Insert Statement Not Affecting Any Rows
Feb 4, 2008
I have the following code:
Code Snippet
INSERT INTO [CICC].[dbo].[972createtest]
SELECT
Always81,
AccountNumber,
CAST(SUBSTRING(DateOfPayment,5,2) + SUBSTRING(DateOfPayment,1,2) + SUBSTRING(DateOfPayment,3,2) as datetime), CAST(TotalPmtAmt AS decimal(9,2))/100,
Collector,
PmtCode,
CAST(AmtPdToAgency AS decimal(9,2))/100,
CAST(AmtPdToClient AS decimal(9,2))/100
FROM [CICC].[dbo].[972create]
I'm trying to insert data into the table called 972createtest. It has the following columns and datatypes:
[Always81] [smallint] NULL,
[AccountNumber] [nvarchar](8) NULL,
[DateOfPayment] [nvarchar](6) NULL,
[TotalPmtAmt] [decimal](11, 2) NULL,
[Collector] [nvarchar](3) NULL,
[PmtCode] [smallint] NULL,
[AmtPdToAgency] [decimal](11, 2) NULL,
[AmtPdToClient] [decimal](11, 2) NULL
And here's a sample of the data stored in those columns:
81 01068713 092107 40.00 NJW 31 4000.00 0.00
81 01068713 111207 2000.00 NJW 31 2000.00 0.00
81 01068784 110806 10000.00 KSD 31 10000.00 0.00
81 01068784 121506 10000.00 KSD 31 10000.00 0.00
81 01068784 030507 10000.00 KSD 31 10000.00 0.00
81 01068784 033007 14100.00 KSD 31 14100.00 0.00
81 01068784 051807 7400.00 KSD 34 7400.00 0.00
Everytime I try to run the code, I get a message telling me "0 row(s) affected". What stupid thing am I doing wrong?
View 8 Replies
ADVERTISEMENT
Aug 16, 2006
Hi,
I would like to know how to add SELECT row to a table variable. It's not for my SELECT syntax(code following is just an ugly example) that I want help it's for the use of table variable.
Your help will greatly appreciate!!!
ex :
DECLARE @MyTestVar table (
idTest int NOT NULL,
anotherColumn int NOT NULL)
SET @MyTestVar = (SELECT idTest, anotherColumn FROM tTest)-- This cause an error :-- Must declare the variable '@MyTestVar'. ???? What?
View 3 Replies
View Related
Jan 14, 2005
Hey,
I am not sure how to really explain this, but I'll give it a try.
I am looking to use a select statement in a way that I can tell it which rows to insert in depending on when only one result is returned. For example, if I run this statement:
SELECT Column1, Column2, Column3
FROM #Temp1
The result set is:
Column1---Column2---Column3
99--------6756756---55555
44--------55---------NULL
Column3 as only the one returned value, so I do not want it associated with any of the other rows, so I need this:
Column1---Column2---Column3
NULL------NULL------55555
99--------6756756---NULL
44--------55---------NULL
Another example:
The returned result now is:
Column1---Column2---Column3---Column4
99---------6756756---55555-----NULL
42---------55---------NULL------12345
So I need:
Column1---Column2----Column3----Column4
NULL-------NULL-------55555------NULL
NULL-------NULL-------NULL-------12345
99---------6756756----NULL-------NULL
44---------55----------NULL-------NULL
Does this make sense, and/or is it even possible?
I know it could be more of a presentation thing, but I would like to know how to do it in the code behind.
Thanks
View 2 Replies
View Related
Feb 12, 2014
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
TableA.item, 'Planning'
TableA.item, 'Design'
TableA.item, 'Program'
TableA.item, 'Production'
How can I do this with tSQL using a single select statement?
View 6 Replies
View Related
Apr 6, 2006
Environment:
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
Problem & Info:
I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls
Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
Desired Help:
How to perform
1) stripping out all undesired rows
2) importing each column into sql table
View 1 Replies
View Related
Dec 17, 2005
I have a table with 23 columns. 8 columns are not always filled by users and so valued to default. Would this affect the overall performance of querying this table? Should I separate those 8 columns and link with a one-to-one relation?
Thanks
View 3 Replies
View Related
Nov 5, 2015
I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies
View Related
Feb 22, 2008
Hi All
I decided to change over from Microsoft Access Database file to the New SQLServerCe Compact edition. Although the reading of data from the database is greatly improved, the inserting of the new rows is extremely slow.
I was getting between 60 to 70 rows per sec using OLEDB and an Access Database but now only getting 14 to 27 rows per sec using SQLServerCe.
I have tried the below code changes and nothing seams to increase the speed, any help as I would prefer to use SQLServerCe as the database is much smaller and I€™m use to SQL Commands.
Details:
VB2008 Pro
.NET Frameworks 2.0
SQL Compact Edition V3.5
Encryption = Engine Default
Database Size = 128Mb (But needs to be changes to 999Mbs)
Where Backup_On_Next_Run, OverWriteQuick, CompressAns are Booleans, all other column sizes are nvarchar and size 10 to 30 expect for Full Folder Address size 260
TRY1
Direct Insert Using Data Adapter.
Me.BackupDatabaseTableAdapter1.Insert(Group_Name1, Full_Folder_Address1, File1, File_Size_KB1, Schedule_To_Run1, Backup_Time1, Last_Run1, Result1, Last_Modfied1, Last_Modfied1, Backup_On_Next_Run1, Total_Backup_Times1, Server_File_Number1, Server_Number1, File_Break_Down1, No_Of_Servers1, Full_File_Address1, OverWriteQuick, CompressAns)
14 to 20 rows per second (Was 60 to 70 when using OLEDB Access)
TRY 2
Using Record Sets
Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)
Dim conn As SqlCeConnection = Nothing
Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"
Try
conn = New SqlCeConnection(strConn)
conn.Open()
Dim cmd As SqlCeCommand = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM BackupDatabase"
cmd.ExecuteNonQuery()
Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
rec.SetString(1, Group_Name1)
rec.SetString(2, Full_Folder_Address1)
rec.SetString(3, File1)
rec.SetSqlString(4, File_Size_KB1)
rec.SetSqlString(5, Schedule_To_Run1)
rec.SetSqlString(6, Backup_Time1)
rec.SetSqlString(7, Last_Run1)
rec.SetSqlString(8, Result1)
rec.SetSqlString(9, Last_Modfied1)
rec.SetSqlString(10, Latest_Modfied1)
rec.SetSqlBoolean(11, Backup_On_Next_Run1)
rec.SetSqlString(12, Total_Backup_Times1)
rec.SetSqlString(13, Server_File_Number1)
rec.SetSqlString(14, Server_Number1)
rec.SetSqlString(15, File_Break_Down1)
rec.SetSqlString(16, No_Of_Servers1)
rec.SetSqlString(17, Full_File_Address1)
rec.SetSqlBoolean(18, OverWriteQuick)
rec.SetSqlBoolean(19, CompressAns)
rs.Insert(rec)
Catch e As Exception
MessageBox.Show(e.Message)
Finally
conn.Close()
End Try
End Sub
€™20 to 24 rows per sec
TRY 3
Using SQL Commands Direct
Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)
Dim conn As SqlCeConnection = Nothing
Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"
Try
conn = New SqlCeConnection(strConn)
conn.Open()
Dim cmd As SqlCeCommand = conn.CreateCommand()
cmd.CommandText = CommandText1
'cmd.CommandText = "INSERT INTO BackupDatabase (€¦"
cmd.ExecuteNonQuery()
Catch e As Exception
MessageBox.Show(e.Message)
Finally
conn.Close()
End Try
End Sub
€˜ 25 to 30 but mostly holds around 27 rows per sec I
Is this the best you can get or is there a better way. Any help would be greatly appericated
Kind Regards
John Galvin
View 3 Replies
View Related
Apr 21, 2008
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
View 7 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Jan 14, 2008
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
View 5 Replies
View Related
May 4, 2004
I have a table "PageInfo" wich has columns "PageID, Title(has "AboutUs" as one of the values), DateModified, Active". My other table is "AboutUs" and has columns "ID, WhatsNew, Welcome, Active".
My goal here is that if table "AboutUs" is affected as an "INSERT" or an "UPDATE" on any row, I want "PageInfo.DateModified" WHERE "PageInfo.Title = AboutUs" to be updated with "getDate()". Don't know if I'm clear enough but thanks in advance.
Gazzou
View 7 Replies
View Related
Oct 20, 2006
I am trying to use SQL Express to support unit testing of functionality which requires database access.
I created an express database (.mdf) which contains a database snapshot which is the database environment that each test expects to see when it starts. Through the execution of the test the database will be modified arbitrarily and I need to reset the database to the original state before the next test executes.
Here's what I do today:
Before Test:
The original database is copied to a unique location in the temp directory
A unique database name is generated (GUID-based)
I connect to the new database file with:
Server=.SQLEXPRESS;AttachDbFilename="PathToTempFile";Database="GuidDBName";Initial Catalog="GuidDBName";Trusted_Connection=Yes;User Instance=true
During the test all database access is routed to the temporary database GuidDBName
After Test:
Close all active database connections and clear the connection pool
Connect to the master database and detach the database:
Server=.SQLEXPRESS;Database=master;Initial Catalog=master;Trusted_Connection=Yes;User Instance=true - sp_detach_db 'GuidDBName'
Delete the temporary file
When I do this, everything seems to work correctly but when I re-open the original database it has changed unexpectedly and includes the modifications that were made by the test as it ran.
Can anyone explain why this is happening and/or provide a solution?
View 1 Replies
View Related
Dec 12, 2014
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0
View 5 Replies
View Related
Jul 23, 2005
Is there a way to copy the structure from one database to anotherwithout affecting the actual data?For example, I added new fields and stored procedures to a db in mydevelopment environment. There are a lot of them.I now want to make the same changes to my production environment db butdon't want to affect the data. Only the fields, stored procs &constraints and stuff pertaining to the structure of the db should bechanged.Does anyone know of a program that can do this or can this be done inMS SQL Enterprise Manager.Any help would be appreciated.7078895
View 2 Replies
View Related
May 26, 2006
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View 10 Replies
View Related
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
View Related
Apr 25, 2007
Hi All,
From what I know, when you execute an
INSERT INTO table1 (field0)
SELECT field0 FROM table 2 ORDER BY field0
the rows are inserted in whatever order the server engine thinks is better at that moment. Is it any way I can have field0 in table1 inserted in the order I need?
My problem is that I can not touch table1, it is used by a legacy application which I am not allowed to modify. I was thinking about creating a clustered index, adding an id field, etc, but I can not know how this will affect the front end application.
table1 and table2 have only a few hundred records.
table1 =
CREATE TABLE [FinalPlasma] ([name] [varchar] (2000) NULL )
table2 =
CREATE TABLE [Test_FinalPlasma] ([nameID] [int] NOT NULL ,
[name] [varchar] (2000) NULL
)
The update that is not giving the "good" order =
DELETE FROM FinalPlasma
INSERT INTO FinalPlasma SELECT name from dbo.Test_FinalPlasma
Unfortunately I can not order the [name] field after the update, because it looks something like
Donald McQ. Shaver
Mark Sheilds
R.J. Shirley
W.E. Sills
Kenneth A. Smee
A. Britton Smith
LCol Edward W. Smith
Harry V. Smith
M. E. Southern
Timothy A. Sparling
Spectrum Investment Management Limited
Thank you,
View 12 Replies
View Related
Sep 1, 2015
We are approaching the "go live" date of a big application upgrade, so while the application is being upgraded i was asked to lock out all users and backup all databases and make sure the data isnt changed during a period of time
It turns out, as one of my databases is being replicated to another server, i cant put that database in read_only mode, or restricted_mode, without removing the replication.
Removing the replication means i have to set it up again and that means another 3 hours added to an already cram-packed weekend without sleep!
How I can lock out the users to make sure there are no changes to a database other then read_only/restricted_mode/single_user ?
I really don't want to touch that replication...
View 0 Replies
View Related
Aug 30, 2007
Hi,
I need to estimate the effort required in writing some SSIS packages.
Could anyone provide some pointers to the various factors (E.g. number of tables, source, etc.) which influence the effort estimate for SSIS packages?
Thanks in advance.
Regards,
B@ns
View 4 Replies
View Related
Jun 10, 2014
I've a variable in my SP that I'd like to use to change the format of my output. Try this!
select case when 1=2 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Then this:
select case when 1=1 then cast(1 as decimal(10,2)) else cast(2 as decimal(10,0)) end
Why in either case, the output is returned as n.nn ? Whilst single select expressions (like these):
select cast(1 as decimal(10,2)) -- = 1.00
select cast(2 as decimal(10,0)) -- = 2
Work fine.
View 8 Replies
View Related
Sep 2, 2015
In my SELECT query I have: MIN(a.orderdue) AS 'Oldest order date'
This works in that it brings through the oldest order date, however it brings through a date format like: 2015-06-11 11:30.000
So I amended the SELECT query to:
MIN (CONVERT(varchar(17),a.orderdue,103)) AS 'Oldest order date'
This brings the date through as 11/06/2015, which is preferable.
But I have noticed that doing this has affected the output: the MIN function no longer returns the first (oldest) date, but a completely different value.
Obviously my changing the formatting for the date has affected the MIN output. Is there any way I can amend the formatting of the date without this happening?
View 3 Replies
View Related
Apr 17, 2007
Hi all,
Got a good question to ask. At the moment, I do not have any control over the reporting server webserver. This is due to the company's policy. The webhosting is controled by antoher department.
Anyways, the question is the following:- Is there a way to customize the config file for the htmlviewer (css style sheet, rendering and others) for a specific client without affecting the rest ?
There are about 20 clients on a server, and I am responsible for one of them. I had customized my config file to render or allow only two formats for this particular client. Since I don't have any control over the websever, I just can't replace the config file without affecting the others. How do i do this ? I just want th config file to ONLY affect a certain client's HTMLViewer and not the others. I tried to do some research on this, but came up empty handed.
Thanks !
Bernard
View 4 Replies
View Related
Feb 26, 2015
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
[Code] .....
View 1 Replies
View Related
Jul 20, 2005
I'm quite stuck with this:I have an import table called ReferenceMatchingImport which containsdata that has been sucked from a data submission. The contents ofthis table have to be imported into another table ExternalReferencewhich has various foreign keys.This is simple but one of these keys says that the value inExternalReference.CompanyRef must be in the CompanyReference table.Of course if this is an initial import then it will not be so as partof my script I must insert a new row into CompanyReference andpopulate ExternalReference.CompanyRef with the identity column of thistable.I thought a good idea would be to use an SP which inserts a new rowand returns @@Identity as the value to insert. However this doesn'twork as far as I can tell. Is there a approved way to perform thissort of opperation? My code is below.Thanks.ALTER PROCEDURE SP00ReferenceMatchingImportAS/*Just some integrity checking going on here*/INSERT ExternalReference(ExternalSourceRef,AssetGroupRef,CompanyUnitRef,EntityTypeCode,CompanyRef, --this is the unknown ref which is returned by the spExternalReferenceTypeCode,ExternalReferenceCompanyReferenceMapTypeCode,StartDate,EndDate,LastUpdateBy,LastUpdateDate)SELECT rmi.ExternalDataSourcePropertyRef,rmi.AssetGroup,rmi.CompanyUnit,rmi.EntityType,SP01InsertIPDReference rmi.EntityType, --here I'm trying to run thesp so that I can use the return value as the insert value1,1,GETDATE(),GETDATE(),'RefMatch',GETDATE()FROM ReferenceMatchingImport rmiWHERE rmi.ExternalDataSourcePropertyRef NOT IN (SELECT ExternalSourceRefFROM ExternalReference)
View 3 Replies
View Related
Oct 31, 2001
I have a table (tblAction) that contains customer account numbers (Account)
and actions taken on the account in a given day (AcctCode). So each account
can have multiple actions taken on it (one row for each action) in a day.
I have a request to present a result set that displays all action 52, 53,54.
But if a given account number has at least one action 28, then they want me to exclude all the rows for that account number from the result set. Can someone help with this?
View 1 Replies
View Related
Jul 5, 2007
Here's my basic syntax:
Code:
select
dma.dma_market_area,
count(fc.contactid) as Number_Of_QCs,
(case when fc.ctca_currenttier = 1 then count(fc.contactid) end) as P1,
(case when fc.ctca_currenttier = 2 then count(fc.contactid) end) as P2,
(case when fc.ctca_currenttier = 3 then count(fc.contactid) end) as P3,
(case when fc.ctca_currenttier = 4 then count(fc.contactid) end) as c1,
(case when fc.ctca_currenttier = 5 then count(fc.contactid) end) as c2,
(case when fc.ctca_currenttier = 6 then count(fc.contactid) end) as c3
from ...
And then I get output like this:
dma_market_areaNumber_Of_QCsP1P2P3c1c2c3
ALBANY-SCHENECTADY-TROY66NULLNULLNULLNULLNULL
ALBANY-SCHENECTADY-TROY1NULL1NULLNULLNULLNULL
ALBANY-SCHENECTADY-TROY1NULLNULLNULL1NULLNULL
ALBANY-SCHENECTADY-TROY1NULLNULLNULLNULLNULL1
How can I get there all to group to one row, based on the first column?
View 1 Replies
View Related
Mar 30, 2008
I need some help with a query . I have two tables "config" and "item".
I have table config as follows:
ConfigID
ItemID
With Values
ConfigID ItemID
14583 2776
14583 2798
14583 3112
And table item as follows:
ItemID
ItemTypeID
ItemValue
With Values
ItemID ItermTypeID ItemValue
2776 1 123
2798 2 ABC
3112 3 789
So the query:
SELECT ConfigID,
(SELECT ItemValue WHERE ItemTypeID = '1') AS Model,
(SELECT ItemValue WHERE ItemTypeID = '3') AS Minor,
(SELECT ItemValue WHERE ItemTypeID = '2') AS Customer
FROM config c, item i
WHERE ConfigID = '14583'
AND c.ItemID = i.ItemID
Produces the result:
ConfigID Model Minor Customer
14583 123 NULL NULL
14583 NULL NULL ABC
14583 NULL 789 NULL
How do I change the above query to get one row:
ConfigID Model Minor Customer
14583 123 789 ABC
Thanks for your help
View 6 Replies
View Related
Oct 12, 2007
Hi,
I am writting a bit of SQL that takes data from one table then inserts it into another one. There is a field that can be any value (and is usually null), but when I insert the value in the new table then I want to execute:
IF table.field>0 then tabl2.field='400'. In other words for every row in the selection that has a field that is greater than 0 then '400' will be put into the new table.
I am not sure if the IF stamement can loop through a number of rows and execute depending on the value of a field in that row??
Thanks
View 7 Replies
View Related
Sep 10, 2007
Hi,
I am new at SQL hopefully this would be a rather easy question for you guys to help me out with.
I have a table called PRODUCT with the following fields:
a. Product Name
b. Product Dept.
c. Purchase Date.
I would like to run a query to obtain all rows tha has more than one purchases on any particular day.
Any replies would be great.
View 1 Replies
View Related
Sep 26, 2007
Hey guys, i need to insert 10,000 rows into the database and I am wondering what the best way to do that is. Should I create a StringBuilder and build the 10,000 insert statements and execute it in 1 connection. Or should I be looking into creating a dataset and use an adapter to insert? Any ideas? Please only answer from experience and provide a sample in c# Thanks.
View 4 Replies
View Related
Jul 20, 2005
i have a table with about 40 columns in access 97( terrible i knw, butits not mine and im not allowed normalize or change it) . i have addeda column , which is a number , called year. it will contain 1997,1998, etc. there are about 1600 rows in the table. is there anythinglike a correlated sub query that will allow me to insert this into thedate column of each row??
View 2 Replies
View Related
Sep 7, 2006
Hi
I'm using the SQL Server 2005 Express edition.
I've manually created some rows in a table "Books".
If I right-click on "dbo.Books" and choose "Script Table As --> Create To --> New Query Editor Window", I get the SQL for creating that table.
But how do I get the INSERT-SQL for the rows already inserted?
Kind Regards
View 3 Replies
View Related
