Inserting multiple records in a stored procedure

[levinll]

I am wondering if there is a way to copy multiple records from one table to another using a stored procedure. Here's an example of what I'd like to do:

select * from Table1
insert into Table2 (using records retrieved from above select statement)

Table2 has many of the same fields as Table1, but there are a few that I'll need to update once the records from Table1 are inserted into Table2.

I'm no SQL guru so any help would be greatly appreciated.

 

[entaroadun]

Certainly. Anything that can be done with a regular SQL statement can be recorded and "played back" with a stored procedure. Elaborate on your problem and the majority of us can help.

 

[levinll]

That's great. I appreciate the help.

here's the scenario. I have a table that I want to copy records out of and put in another table.

let's see table 1 has the following layout.

field name | field type |
recId int
dayofweek char(4)
hours int
comment char(50)

and table 2 has the following layout.
field name | field type |
historyId int
recId int
dayofweek char(4)
hours int
comment char(50)
editby char(20)
editdate datetime

I want to copy specific records (say recID=15) from table 1 into table 2.

 

[osjohnm]

Hi

Do something like this:

CREATE PROCEDURE INSERT_PROCEDURE
@rec int
AS
INSERT INTO TABLE2(recid, dayofweek, hours, comment)
SELECT recid, dayofweek, hours, comment
from table1
where recid = @rec
GO

If you want you can add the editby, editdate in the insert as well.eg:
CREATE PROCEDURE INSERT_PROCEDURE
@rec int
AS
INSERT INTO TABLE2(recid, dayofweek, hours, comment, editby, editdate)
SELECT recid, dayofweek, hours, comment, 'system user', getdate()
from table1
where recid = @rec
GO

then execute the sp with the value youw ant to pass it

exec insert_procedure 15

that should do it for you.

John

 

[levinll]

You da man !