SQL 如何插入多行 - 需要一个循环？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9764621/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert multiple rows - a loop needed?
提问by Michael A
I have the following statement:
我有以下声明：
insert into forecast_entry.user_role_xref
( user_master_id ,
role_id ,
created_date ,
created_by
)
values
( 276 , -- user_master_id - int
101 , -- role_id - int
getdate() , -- created_date - datetime
'MICHAELSK' -- created_by - varchar(20)
)
I need to generate a row for role_id 101-355 (so the same statement above, except repeated with the role_id incrementing). What would be the best way to do this? To get the job done I'm intending on writing a quick C# application that will have a loop but I'm sure this isn't the best way and hope to learn something here to avoid having to do that in future (as I'm sure this kind of scenario is common).
我需要为 role_id 101-355 生成一行（所以上面的语句相同，除了随着 role_id 递增而重复）。什么是最好的方法来做到这一点？为了完成工作，我打算编写一个具有循环的快速 C# 应用程序，但我确信这不是最好的方法，并希望在这里学习一些东西以避免将来不得不这样做（因为我我确定这种情况很常见）。
回答by MichaelS
Here is what I use, just modify as needed. Here, I add a bunch of sequence numbers to a table using a loop variable:
这是我使用的，只需根据需要进行修改。在这里，我使用循环变量将一堆序列号添加到表中：
USE MyDB
GO
DECLARE @MyCounter as INT
SET @MyCounter = 1 -- to use this multiple times you can just
-- change the starting number and run again
-- if you do not want duplicate numbers
WHILE @MyCounter < 1000 -- any value you want
BEGIN
INSERT INTO [MyDB].[dbo].[MyTable]
([NumberField])
VALUES
(@MyCounter) -- insert counter value into table
set @MyCounter = @MyCounter + 1; -- increment counter
END
回答by Mikael Eriksson
You should make use of numbers tableand if you don't have one you can use master..spt_valueslike this:
你应该使用数字表，如果你没有，你可以这样使用master..spt_values：
insert into forecast_entry.user_role_xref
( user_master_id ,
role_id ,
created_date ,
created_by
)
select 276, -- user_master_id - int
number, -- role_id - int
getdate() , -- created_date - datetime
'MICHAELSK' -- created_by - varchar(20)
from master..spt_values
where type = 'P' and
number between 101 and 355
回答by Rinat
In my opinion, the best way is to create stored procedure. In stored procedure you should make a loop, which would insert data into table. From your C# application you open a connection to DB, call once a stored procedure and close a connection. On SQL you get best perfomance working with big amount of data.
在我看来，最好的方法是创建存储过程。在存储过程中，您应该创建一个循环，将数据插入表中。从您的 C# 应用程序打开到数据库的连接，调用一次存储过程并关闭连接。在 SQL 上，您可以在处理大量数据时获得最佳性能。
回答by Nithesh Narayanan
Instead of looping query Create a DataTableand create a stored procedurewith User Defined Table Type
相反，循环的查询创建DataTable并创建一个stored procedure与User Defined Table Type
CREATE TYPE dtl AS TABLE
(
user_master_id INT ,
role_id INT,
created_date DATETIME,
created_by varchar(20)
)
And Stored procedure
和存储过程
CREATE PROCEDURE SPNAME
@dtl dtl READONLY
AS
INSERT INTO forecast_entry.user_role_xref
( user_master_id ,
role_id ,
created_date ,
created_by
)
SELECT
user_master_id ,
role_id ,
created_date ,
created_by
FROM @dtl
Pass DatatTable for @dtl parameter of stored procedure which contains the proper data between 101-255
为存储过程的@dtl 参数传递 DatatTable，其中包含 101-255 之间的正确数据
回答by Amritpal Singh
if you create a loop in c# it will send same query again and again to database which is not a good idea.you rather create sp and loop there. as suggested by sham
如果你在 c# 中创建一个循环，它会一次又一次地向数据库发送相同的查询，这不是一个好主意。你宁愿在那里创建 sp 和循环。正如sham所建议的那样
