SQL-Multiple Insert into identity table

Question

I need to to do a insert from a table with the following structure:

Table A
Col1    Col2    Col3    Col4
intID1  intID2  intID3  intID4

I need to select the rows from the above table that are null for col1,col2,col3 and insert those rows into a table that will generate an identity row that I need to use to insert into another table.I am not sure of the sql statement or the general method used to select those rows and insert them multiple times and retrieve the identity id one by one to insert into the next table. Any help is greatly appreciated!

Sample process:

Table A
Col1 Col2 Col3 Col4
1     3    7    null
null null null  45   
null null null  67

1)Retrieve rows 2 and 3

2)Insert 2 and 3 into another table to retrieve identity id for both rows

3)Insert identities from step 2 into another table

so I need to get those rows that are all null for col 1,2 and 3 — user1212140, Mar 06 '12 at 20:53

score 1 · Answer 1 · answered Mar 06 '12 at 20:35

1

INSERT INTO TABLEB(Col1,Col2,Col3,Col4)
SELECT * FROM TABLEA WHERE Col1 is NULL AND Col2 is NULL AND Col3 is NULL;

answered Mar 06 '12 at 20:35

Teja

13,214
36
93
155

I need to get the identity row for each insert as well "SCOPE_IDENTITY() " If I do it this way will I be able to get each identity value and do another insert from there? – user1212140 Mar 06 '12 at 20:44
can u post some sample data n ur desired o/p ? – Teja Mar 06 '12 at 20:45
so ur basically looking for only those rows which have NULLS in Col1,Col2,Col3 – Teja Mar 06 '12 at 21:03
Identity is the value that database generates when you insert into a identity column.I'm looking to get each identity number tat is generated from the table and insert that into another column of another table – user1212140 Mar 06 '12 at 21:08
@user1212140 I'm slightly confused. Is Col4 your identity column? – Phil Mar 07 '12 at 07:21

score 1 · Accepted Answer · edited May 23 '17 at 10:34

1

Venk covered step 1 and 2 I think. For 3 can use the OUPUT clause to retrieve the identity value from set operation.

Get Identity of multiple insertion in sql server 2008

edited May 23 '17 at 10:34

Community

1
1

answered Mar 06 '12 at 21:22

paparazzo

44,497
23
105
176

score 1 · Answer 3 · answered Mar 07 '12 at 03:01

Sounds like you need the output operator:

declare @TableA table(Col1 int, Col2 int, Col3 int, Col4 int);
declare @TableB table(id int identity(1,1), Col1 int, Col2 int, Col3 int, Col4 int);
declare @Audit table(id int);

insert into @TableA 
select 1,3,7,null union all
select null, null, null, 45 union all
select null, null, null, 67;

-- copy null columns from @TableA to @TableB
-- and output id's to @Audit
insert into @TableB
output inserted.id
into @Audit
select * 
from @TableA
where Col1 is null
and Col2 is null
and Col3 is null;

-- Copied @TableB values and @Audit values
select * from @TableB;
select * from @Audit;

SQL-Multiple Insert into identity table

3 Answers3