x

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?
more ▼

asked Sep 18, 2012 at 11:51 PM in Default

denkyira gravatar image

denkyira
30 2 4 5

Thank you very much
Sep 19, 2012 at 02:30 PM denkyira
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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!
more ▼

answered Sep 19, 2012 at 01:29 AM

JohnM gravatar image

JohnM
6.8k 1 3 7

(comments are locked)
10|1200 characters needed characters left

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. 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
more ▼

answered Sep 19, 2012 at 10:42 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

(comments are locked)
10|1200 characters needed characters left
That is exactly what I was looking for
more ▼

answered Sep 19, 2012 at 03:09 PM

denkyira gravatar image

denkyira
30 2 4 5

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.
Sep 19, 2012 at 03:11 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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()
more ▼

answered Sep 20, 2012 at 12:32 AM

denkyira gravatar image

denkyira
30 2 4 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x726
x106
x3

asked: Sep 18, 2012 at 11:51 PM

Seen: 2579 times

Last Updated: Sep 20, 2012 at 12:32 AM