[SQL] Multiple Insert using a stored procedure

Question

0.00/5 (No votes)

See more:

What is the most efficient way to execute the following SQL "Pseudo-code" (within a STORED PROCEDURE):

SQL

SELECT PKEY_ID_COLUMN FROM table1 -- SELECT all the ID values from table1... (lets say 1,2,3)

-- Now, insert to another table the value 1 (for example...) along with each and every one of the values from the previous query. INSERT (1,1), (1,2), (1,3)
INSERT INTO relational_table (ID1,table1ID) VALUES(1,PKEY_ID_COLUMN)

Does it require using "SqlDataReader" (like in C#) in the STORED PROCEDURE itself?
Anyway, How can I execute it properly?

Posted 28-Oct-11 4:22am

ShacharK

Updated 28-Oct-11 4:23am

v2

Add a Solution

3 solutions

Solution 3

U can use cursor to do this...use following procedure,

create proc addid_sp(@eid int)--table2 id
as
begin
declare @uid int--table1 id
declare curUid cursor for select * from tblId
open curUid
fetch next from curUid into @uid
while @@FETCH_STATUS=0
begin
insert into tbleid values(@eid,@uid)
fetch next from curUid into @uid
end
close curUid
deallocate curUid
end
go

but the issue here is,cursor can be costly interms of io performance,if your application is small then u can use above procedure..hope it helps u..

Posted 28-Oct-11 5:04am

Arasu Rajendran

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Accepted Answer · 2011-10-28T04:32:00

Solution 1

Use insert into ... select

SQL

insert into table1 (id1, table1id) select id as [id1], column as [table1id] from table2

Posted 28-Oct-11 4:32am

Mehdi Gholam

Comments

ShacharK 28-Oct-11 10:38am

Thanks :)

Corporal Agarn · Accepted Answer · 2011-10-28T04:33:00

Solution 2

If using T-SQL:

SQL

INSERT INTO relational_table (ID1,table1ID)
SELECT 1, PKEY_ID_COLUMN FROM table1

Posted 28-Oct-11 4:33am

Corporal Agarn

Comments

ShacharK 28-Oct-11 10:38am

Thanks. :)