Click here to Skip to main content
15,920,217 members
Please Sign up or sign in to vote.
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
Updated 28-Oct-11 4:23am
v2

Use insert into ... select
SQL
insert into table1 (id1, table1id) select id as [id1], column as [table1id] from table2
 
Share this answer
 
Comments
ShacharK 28-Oct-11 10:38am    
Thanks :)
If using T-SQL:

SQL
INSERT INTO relational_table (ID1,table1ID)
SELECT 1, PKEY_ID_COLUMN FROM table1
 
Share this answer
 
Comments
ShacharK 28-Oct-11 10:38am    
Thanks. :)
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..
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900