Multiple insert sproc for many to many

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Multiple insert sproc for many to many

Author

Topic

j4ydh
Starting Member

18 Posts

Posted - 2010-11-04 : 05:40:31

Hi,

I would appreciate some advice for the best approach with the following scenario:

I would like a single sproc to receive parameters from a page that supplies insert data for table1 and looping (for each) information for table2. table2 is the junction box for multiple choices linked through standard many to many relationship.

The expectation is that I insert the data into table1 which creates the new identity to be used in conjunction with the multiple choice values. Using @@identity I hoped to have a second insert statement that would take in the looping choices and insert into the junction table.

I have checked that the loop works as expected. I have managed to grab the @@identity but I am in a situation where the initial insert is being run as many times as the options for the junction table2.

Please advise if I am trying the wrong method and if there is a best practice that I should follow.

Thank you in advance
Jay

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-11-04 : 07:20:16

Why not use OUTPUT instead?


DECLARE @Table TABLE (identityfield INT)

INSERT INTO Table1
SELECT
Field1,Field2,Field3
OUTPUT identityfield into @table
FROM
Table (Or derived table)

Then:


INSERT INTO TABLE2
SELECT
identityfield
field1,
field2
FROM
@Table

There is no need to loop as you can then write the second query to insert all rows required without a loop.

Try to think in a set based way rather than a procedural way..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources