selecting multiple rows and insert same

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)

selecting multiple rows and insert same

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2004-03-09 : 18:05:18

I have this tableA:
userid groupnum
joe 1
lee 1
sam 1
ben 2
dan 3
kim 3
len 4

What I want to do is do a select on this table where groupnum=1 and insert the all rows returned into tableB (shown below) using one stored procedure.

After the stored proc has executed tableB should look like this:

tableB
comp cycledate userid groupnum file
111 1/12/2003 joe 1 file1.xml
111 1/12/2003 lee 1 file1.xml
111 1/12/2003 sam 1 file1.xml

All the other field values, except userid and groupnum, will be passed on to the stored proc when called by my vb.net application.

I hope this makes sense. Thanks for your help.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-09 : 18:24:49

create procedure tableBInsert

@comp varchar(5),
@cycledate datetime,
@file varchar(55)

as

insert tableB(
comp,
cycledate,
userid,
groupnum,
file)

select
@comp,
@cycledate,
ta.userid,
ta.groupnum,
@file
from
tableA ta
where
ta.groupnum = 1

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources