Insert into table with an identity columns from an other table

• Johnny H

  Right there with Babe

  Points: 799

  More actions

  December 23, 2013 at 12:25 pm

  #287637

  Hi Everyone

  I just created a new table with over 100 Columns and I need to populated just the first 2 columns.

  The first columns to populate is an identify column that is the primary key. The second column is a foreign_key to an other column and I am trying to populate this columns with all the values from the foreign_key value. This is what I am trying to do.

  column1 = ID

  column2= P_CLIENT_D

  SET IDENTITY_INSERT PIM1 ON

  INSERT INTO PIM1 (P_CLIENT_ID)

  SELECT

  Client.ID

  FROMP_Client

  So I am trying to insert both an identity values and a value from an other table while leaving the other columns blank. How do I go about doing this.

  Thanks.

• Lowell

  SSC Guru

  Points: 323482

  More actions

  December 23, 2013 at 12:32 pm

  #1675670

  you want to take advantage of the OUTPUT clause, which gives you access to the INSERTED and DELETED triggers that are normally seen inside a trigger;

  here's an example, where i insert 15 rows, capture the new identity fields, and use them elsewhere for an update, but it could just as easily been an insert into a FK table:

  DECLARE @MyResults TABLE(

  ID int,

  newcode VARCHAR(30),

  oldcode VARCHAR(30) )

  Create Table adds(adid int identity(1,1) not null primary key, code varchar(30) )

  INSERT INTO adds(code)

  OUTPUT

  INSERTED.adid,

  INSERTED.code,

  NULL

  INTO @MyResults

  SELECT 'aliceblue' UNION ALL SELECT 'antiquewhite' UNION ALL

  SELECT 'aqua*' UNION ALL SELECT 'aqua*' UNION ALL

  SELECT 'aquamarine' UNION ALL SELECT 'azure' UNION ALL

  SELECT 'beige' UNION ALL SELECT 'bisque' UNION ALL

  SELECT 'black*' UNION ALL SELECT 'black*' UNION ALL

  SELECT 'blanchedalmond' UNION ALL SELECT 'blue*' UNION ALL

  SELECT 'blue*' UNION ALL SELECT 'blueviolet' UNION ALL

  SELECT 'brown' UNION ALL SELECT 'burlywood' UNION ALL

  SELECT 'cadetblue'

  Insert into FK Table

  SELECT ID, newCode FROM @MyResults

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!