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

avatar image

denkyira
30 3 4 7

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

avatar image

JohnM
12.4k 3 7 14

(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

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(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

avatar image

denkyira
30 3 4 7

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

avatar image

denkyira
30 3 4 7

(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.

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:

x993
x137
x5

asked: Sep 18, 2012 at 11:51 PM

Seen: 3249 times

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

Copyright 2016 Redgate Software. Privacy Policy