question

denkyira avatar image
denkyira asked

Insert into data into sql server table based on Identity column of another Table

How do I insert employees information into a table based on the system generated id from another table in the same database. So I have inserted data into table A, which has an identity column. Now I need to insert data into Table B based on the identity column of Table A. Any help or example?
sqlinsertinto
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you very much
0 Likes 0 ·
JohnM avatar image
JohnM answered
If I understand your question correctly and assuming that Table_B has an identity column on it as well, you can do something like the following: SET IDENTITY_INSERT Table_B ON: INSERT INTO Table_B( col1, col2, col3, col4 ) SELECT col1 ,col2 ,col3 ,col4 from Table_A SET IDENTITY_INSERT Table_B OFF; GO Where Col1 is the identity column. If that's not what your looking for, let me know. Hope this helps!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
Another way is to use the OUTPUT clause. This allows you to capture multiple ID values as they get created and then reuse them elsewhere in the query. This link is to the [MSDN version of the Books Online][1]. Here's an example I whipped up: DECLARE @IdOutput TABLE ( AgentID INT, AgentName VARCHAR(250)) INSERT INTO dbo.Agent (AgentName, AgentDetails) OUTPUT INSERTED.AgentId, INSERTED.AgentName INTO @IdOutput VALUES (N'Dude', -- AgentName - nvarchar(250) N'Lots of Details' -- AgentDetails - nvarchar(max) ) INSERT INTO dbo.Resource (AgentId, FirstName, MiddleName, LastName, Gender, OtherDetails ) SELECT io.AgentID, 'somevalue', 'someothervalue', 'another', 0, 'otherdetails' FROM @IdOutput AS io [1]: http://msdn.microsoft.com/en-us/library/ms177564.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

denkyira avatar image
denkyira answered
That is exactly what I was looking for
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you're trying to respond to one of the answers, instead of typing into the "Your Answer" box, click on the "add new comment" button just below the answer you're interested in.
1 Like 1 ·
denkyira avatar image
denkyira answered
Because I only need the IDENTITY column from the the parent table, I cannot select so many as stated above. I tried to do as follows and getting "Procedure or function CreateSuggestionID has too many arguments specified". Here is my code: Create PROCEDURE [dbo].[CreateSuggestionID] @DeptID int, @EmpID char(9), @EmpHrlySalCode char(1), @JobTitle nvarchar(50), @empInitials nvarchar(50), @MailCode nvarchar(50), @wkPhone nvarchar(50), @EmpGroup nvarchar(50), @EmpShift nvarchar(50), @FirstSuggestion bit, @OutputSuggID int output AS BEGIN SET NOCOUNT ON; INSERT INTO tblEmpTeam (DeptID ,EmpID,EmpHrlySalCode,JobTitle,empInitials,MailCode,wkPhone,EmpGroup,EmpShift,FirstSuggestion) VALUES (@DeptID,@EmpID, @EmpHrlySalCode,@JobTitle, @empInitials,@MailCode,@wkPhone,@EmpGroup,@EmpShift,@FirstSuggestion) SET @OutputSuggID = SCOPE_IDENTITY() END vb.net code *********************** cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "CreateSuggestionID" cmd.Parameters.Add("@DeptID", Data.SqlDbType.Int).Value = CType(btSubmits.FindControl("emp_dept"), TextBox).Text cmd.Parameters.Add("@EmpID", Data.SqlDbType.Char, 9).Value = CType(btSubmits.FindControl("emp_gmin1"), TextBox).Text cmd.Parameters.Add("@EmpHrlySalCode", Data.SqlDbType.Char, 9).Value = "H" cmd.Parameters.Add("@JobTitle", Data.SqlDbType.NVarChar, 50).Value = CType(btSubmits.FindControl("emp_title"), TextBox).Text cmd.Parameters.Add("@empInitials", Data.SqlDbType.NVarChar, 50).Value = CType(btSubmits.FindControl("emp_Initials"), TextBox).Text cmd.Parameters.Add("@MailCode", Data.SqlDbType.NVarChar, 50).Value = CType(btSubmits.FindControl("emp_mail"), TextBox).Text cmd.Parameters.Add("@wkPhone", Data.SqlDbType.NVarChar, 50).Value = CType(btSubmits.FindControl("emp_phone"), TextBox).Text cmd.Parameters.Add("@EmpGroup", Data.SqlDbType.NVarChar, 50).Value = CType(btSubmits.FindControl("emp_group"), TextBox).Text cmd.Parameters.Add("@EmpShift", Data.SqlDbType.Decimal).Value = CType(btSubmits.FindControl("emp_shift"), TextBox).Text cmd.Parameters.Add("@FirstSuggestion", Data.SqlDbType.Decimal).Value = CType(btSubmits.FindControl("emp_firstsugg"), CheckBox).Checked cmd.Parameters.Add("@OutputSuggID", SqlDbType.Int).Direction = ParameterDirection.Output Dim OutputSuggID As Integer 'Dim outputSuggID As String = cmd.Parameters("@OutputSuggID").Value.ToString() OutputSuggID = cmd.ExecuteScalar() 'cmd.ExecuteScalar() trans.Commit()
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.