Insert data in relationship table without identity_insert ON

Question

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.

score 1 · Accepted Answer · answered Dec 16 '11 at 15:23

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

Insert data in relationship table without identity_insert ON

What I did

1 Answers1