Hi,
I have a stored procedure that insert data into 2 temp tables. The problem that I have is when I insert a data into a first table, how do I get the @.@.identity value from that table and insert it into the second table?? The following code is what I have:
Create #Temp1
@.StateID Identity,
@.State nvarchar(2),
@.wage money
INSERT INTO #Temp1 (State, wage)
SELECT State, Wage FROM Table1 INNER JOIN Table2 ON Table1.Table1_ID = Table2.Table2_ID
Create #Temp2
@.ID Identity
@.EmployeeID int
@.StateID Int
@.Field1 Money
@.Field2 Money
INSERT INTO #Temp2 (EmployeeID, StateID, Field1, Field2)
SELECT EmployeeID, StateID, Field1, Field2 FROM SomeTable
So, The first part I created a #Temp1 table and insert data into the table. Then after the insert, I want the @.@.Identity value and insert into the #Temp2 table. This is my first time doing stored procedure, so, I am wondering how do I retrieve the @.@.identity value and put into the second select statement. Please help.
ahTan
DECLARE @.NewID INT
SET @.NewID = @.@.IDENTITY
OR
DECLARE @.NewID INT
SELECT @.NewID = @.@.IDENTITY
|||
instead of @.@.IDENTITY use SCOPE_IDENTITY() like
declare @._ID int
Select @._ID = SCOPE_IDENTITY()
to know why to use it, please search for SCOPE_IDENTITY in SQL SERVER books online.
thanks,
satish.
|||SCOPE_IDENTITY() is not necessary here since the table is being created in the same stored proc. There are no triggers that would be run or no side effects to doing an insert into the table, therefore @.@.IDENTITY will work fine.
SCOPE_IDENTITY() is useful when you may have a trigger that inserts into table2 when you insert into table1, in that case getting the value used in the IDENTITY column should be retrieved with SCOPE_IDENTITY() since @.@.IDENTITY will get you value from table2 and not table1. However you don't have to worry about that here.
Regards,
Tim
|||Thank you guys for the answer. Now, the next problem is, how do I insert the @.@.Identity value into the #TempPayroll table?
|||
ahTan:
how do I insert the @.@.Identity value into the #TempPayroll table
just save the @.@.scope_identity from the first insert into another variable and use it as input to the 2nd insert
insert into table1....
select @.Identity1 = @.@.SCOPE_IDENTITY
insert into table2 (...) values(..., @.Identity1,...)
|||
Thank you all of you for the responses. I have managed to get the stored procedure to work when I tested it using the SQL analyzer. I am getting close to what I want. Ok, the stored procedure will be called using a sqldatasource. The following code call the stored procedure:
1For Each rowAs GridViewRowIn GridView1.Rows2 SqlDataSource2.InsertCommand ="_payroll"3 SqlDataSource2.InsertCommandType = SqlDataSourceCommandType.StoredProcedure4 SqlDataSource2.InsertParameters.Add("pniID", 410)5 SqlDataSource2.Insert()6 SqlDataSource2.InsertParameters.Clear()7Next8 GridView1.Visible =False9 SqlDataSource2.SelectCommand ="_payroll"10 SqlDataSource2.SelectCommandType = SqlDataSourceCommandType.StoredProcedure11''SqlDataSource2.SelectParameters.Add("pniID", CInt(row.Cells(0).Text.Trim))12 GridView2.DataSourceID ="SqlDataSource2"13 GridView2.DataBind()After the for each loop, I want to display all the data in a gridview. The following code is the sql code in the stored procedure:
|||1CREATE PROCEDURE [dbo].[_payroll]2(3@.pniIDint4)56AS78SET NOCOUNT ON910CREATE TABLE #TempStates11(12StateIDInt IDENTITY,13Statenvarchar(2),14Wagemoney15)1617Create Table #TempPayrolls18(19TempPayrollIDint IDENTITY,20PNI_IDint,21EmployeeIDint,22StateIDint,23Deptnvarchar(50),24PerDiemmoney,25Mileagemoney,26Phonemoney,27Computermoney,28Cameramoney29)3031INSERT INTO #TempStates (State, Wage)32SELECT PaySheets.StateOfProject,33 PNI_Payroll.Amount * PNI_Payroll.QuantityAS Amount34FROM PNI_Payroll35INNERJOIN PNION PNI_Payroll.PNI_ID = PNI.PNI_ID36INNERJOIN TimeSheetsON PNI.TimeSheetID = TimeSheets.TimeSheetID37INNERJOIN PaySheetsON TimeSheets.PaysheetID = PaySheets.PaysheetID38WHERE (PNI.PNI_ID = @.pniID)AND (PNI_Payroll.DescriptionLIKE'%Salary%')3940DECLARE @.NewStateIDint41SELECT @.NewStateID =@.@.IDENTITY4243--SELECT * FROM #TempStates4445INSERT INTO #TempPayrolls (PNI_ID, EmployeeID, StateID)46SELECT PNI.PNI_ID, InspectorID, StateID = @.NewStateID47FROM PNI48WHERE PNI.PNI_ID = @.pniID4950INSERT INTO #TempPayrolls (PNI_ID, EmployeeID, Dept, PerDiem, Mileage, Phone, Computer, Camera)51SELECT DISTINCT PNI.PNI_ID,52 PNI.InspectorID,53 StateOfProject +' Inspector'AS Dept,54 (SELECT (Quantity * Amount)As PerDiemAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Per Diem (YES OR NO)')AS PerDiem,55 (SELECT (Quantity * Amount)As MileageAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Mileage')AS Mileage,56 (SELECT (Quantity * Amount)As PhoneAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Cell Phone (YES OR NO)')AS Phone,57 (SELECT (Quantity * Amount)As ComputerAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Computer')AS Computer,58 (SELECT (Quantity * Amount)As DigitalCamAmtFROM PNI_PayrollWHERE PNI_ID = @.pniIDANDDescription ='Camera')AS Camera59FROM PNI_PayRoll60INNERJOIN PNION PNI_PayRoll.PNI_ID = PNI.PNI_ID61INNERJOIN TimeSheetsON PNI.TimeSheetID = TimeSheets.TimeSheetID62INNERJOIN PaySheetsON TimeSheets.PaysheetID = PaySheets.PaysheetID63WHERE PNI.PNI_ID = @.pniID6465--Display data from the temp tables66SELECT TempPayrollID, PNI_ID, EmployeeID,67(SELECT StateFROM #TempStatesWHERE #TempStates.StateID = #TempPayrolls.StateID)AS State,68(SELECT WageFROM #TempStatesWHERE #TempStates.StateID = #TempPayrolls.StateID)AS Wage,69PerDiem, Mileage, Phone, Computer, Camera70FROM #TempPayrolls717273-- Turn NOCOUNT back OFF7475SET NOCOUNT OFF76GO77So, I am wondering how am I going to display all the data in the temp tables in the gridview2. Please help
Satish is correct, use SCOPE_IDENTITY(). Although fevir's explaination is correct on why @.@.IDENTITY might work, it's misusing the command, and the wrong command to use.
Just because there are no triggers TODAY, doesn't mean that there won't ever be.
|||Motley, what about the whole YAGNI thought? Today, as it sit, the temp table it being created directly above and so no triggers exits. If you eventually did put a trigger on a temp table (not sure why you'd do this) but you'd adjust at that time.
Ultimately you're correct in that either can be used, but I'm curious about your thoughts in regard the YAGNI argument.
Regards,
Tim
|||YAGNI would only apply if there was an easy way and more difficult approach. Since we are talking about using:
SELECT @.NewStateID =@.@.IDENTITY
or
SELECT @.NewStateID =SCOPE_IDENTITY()
I see no point in using a function that really should be reserved for someone who is very well versed in T-SQL. It's rare that @.@.IDENTITY is the correct function to use when you need an identity value. Again, in this case, we want the identity value that was just inserted by the insert just above us. The function for that is SCOPE_IDENTITY. If we wanted to know the last identity value that the system generated in the scope of our connection, that would be @.@.IDENTITY. I have yet to EVER need @.@.IDENTITY in any project I've ever done. And while using @.@.IDENTITY can cause maintenaince problems, and unexpected results, the same is not true of SCOPE_IDENTITY. It will always return the result that we are expecting whether we have a trigger on the table today, tomorrow, or never.
I say this, because I can't count the number of times that people have gotten bitten because they had to add auditting tables to track changes to a table (or replication), only to find that the application is now creating bogus data because someone decided to use @.@.IDENTITY when they should have used SCOPE_IDENTITY().
