CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Control Freak
Control Freak

Reputation: 13233

Choosing when to insert data into Identity column

I am trying to do a bulk insert from a select statement, but sometimes I would like to save the Identity from Table2 to Table1, but sometimes I want the Identity in the Table to be generated automatically when there is no data in Table2, So how would this be accomplished?

 INSERT INTO Table (ID,Name) 
 SELECT 
  CASE WHEN Col1 IS NOT NULL THEN Col1 ELSE @@identity END ID,
  Col2 Name,
 FROM Table2

Is this possible or do I have to do 2 seperate bulk import processes?

Upvotes: 1

Views: 662

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19184

Yes you need two bulk inserts:

SET IDENTITY_INSERT Table ON

INSERT INTO Table (ID,Name) 
SELECT 
Col1
Col2 Name
FROM Table2
WHERE Col1 IS NOT NULL

SET IDENTITY_INSERT Table OFF

INSERT INTO Table (Name) 
SELECT 
Col2 Name
FROM Table2
WHERE Col1 IS NULL

Upvotes: 1

Related Questions