[SQL]使用存储过程多次插入 [英] [SQL] Multiple Insert using a stored procedure

问题描述

执行以下SQL伪代码"(在存储过程中)的最有效方法是:

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

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)

是否需要在STORED PROCEDURE本身中使用"SqlDataReader"(如C#)?
无论如何，如何正确执行它?

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

推荐答案

使用insert into ... select

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

如果使用T-SQL:

If using T-SQL:

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

U可以使用游标执行此操作...使用以下过程，

创建proc addid_sp(@eid int)-table2 id
如
开始
声明@uid int--table1 id
从tblId
声明select *的当前光标 打开当前
从curUid提取下一个到@uid
而@@ FETCH_STATUS = 0
开始
插入tbleid值(@ eid，@ uid)
从curUid提取下一个到@uid
结束
关闭当前
取消分配curUid
结束
去

但是这里的问题是，游标可能会影响io性能，如果您的应用程序很小，那么您可以使用上述过程.希望它对您有帮助.

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..

这篇关于[SQL]使用存储过程多次插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！