CODEWITHSUNDEEP
sql-serveridentity-insert
Mahesh
Mahesh

Reputation: 1784

Insert data in relationship table without identity_insert ON

I am having a scenario where I have to insert some master data into the table with script, so that we can this script on production server while deployment.

My tables are 

Category
 --Id (PK, Identity)
 --Name

CategoryType
  --Id (PK, Identity)
  --Name
  --CategoryID (FK)

Now I have some row data in excel for category and categorytype. I have to generate the data script (bunch of insert statement).

What I did

Start with  identity_insert ON 
insert statement for Category table
Insert statement for CategoryType table with respect of category PK
end  identity_insert Off

I just want to know Is there any way I can avoid identity_insert ON/OFF thing.

Upvotes: 0

Views: 652

Answers (1)

IordanTanev
IordanTanev

Reputation: 6240

may be this is what you are looking for:

DECLARE @inserted table ( id int ) --assuming ID is int

INSERT INTO Category ( Name )
OUTPUT INSERTED.Id INTO @inserted
VALUES( 'MyCategory Name' )

INSERT INTO CategoryType( Name, CategoryID )
SELECT id, 'MyCategoryTypeName'
FROM @inserted

Hope it helps
Best Regards,
Iordan

Upvotes: 1

Related Questions