question

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!

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

That is exactly what I was looking for

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