Multiple rows insert query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Multiple rows insert query

Author

Topic

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2005-03-09 : 11:09:57

Hello,

I have 2 tables. tbl_num and tbl_link.
In tbl_num numID is the PK and identity column
In tbl_link Link_ID is the PK and identity column.

tbl_num
numID--Name--Num_type
1---------Abc-----10
2---------Def-----10
3---------Fghj----20
4---------Fghj----30

tbl_link
Link_ID--numID--LinkNum
1---------1-------100
2---------1-------200
3---------1-------400
4---------2-------100
5---------2-------700

Now consider this ...
Declare @temp_num_id int
Insert into tbl_num(Name,Num_type)
Select Name,Num_type=4 from tbl_num where numID = 1
Select @temp_num_id = @@IDENTITY

Insert into tbl_link(numID,LinkNum)
Select numID=@temp_num_id,LinkNum from tbl_lnk where numID = 1

The above segment works because the select in my first insert returns only one row (as numID is my PK) so I can use @@IDENTITY to get the new row entered and then I can use that in my second insert statement.

But the problem comes when I want to copy all the records from tbl_num with a particular num_type. This can return more than one row so I cannot use @@IDENTITY to get all the generated numIDs.
How can I do this if I don’t want to use cursors?

Thanks
maximus_vj

chadmat
The Chadinator

1974 Posts

Posted - 2005-03-09 : 11:26:24

You should be using SCOPE_IDENTITY() rather than @@Identity.

You could use a trigger to accomplish what you want.

-Chad

http://www.phxpoker.com

Phoenix's largest online poker community

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2005-03-09 : 12:15:37

Hello Chad
Thanks for the SCOPE_IDENTITY() tip.

As Triggers will be fired on INSERT,UPDATE or DELETE, but I want to use the above code segment only when we are doing a copy operation and not when we are adding a new record. But in both cases we will have an Insert statement so the INSERT trigger will be fired anyway.
And moreover with INSERT trigger I can use INSERTED to get the new ID inserted but how will I get the numID from which we are copying.

quote:

Declare @temp_num_id int
Insert into tbl_num(Name,Num_type)
Select Name,Num_type=4 from tbl_num where numID = 1
Select @temp_num_id = @@IDENTITY

Insert into tbl_link(numID,LinkNum)
Select numID=@temp_num_id,LinkNum from tbl_lnk where numID = 1

As you can see in my code I am trying to copy records based on a numID and also I am trying to copy the corresponding records in another table for the newly inserted record in tbl_num.

Hope I am clear enough...
Thanks,
maximus_vj

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2005-03-10 : 10:05:59

Hello
Any ideas?

Thanks,
maximus_vj

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 10:08:51

disable the trigger
copy the rows
reenable the trigger

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources