SQL命令多次执行？

I have situations that I need to write multiple rows of the same value to setup some tables. Say I have to add 120 rows with two columns populated. I am looking for a shortcut, instead of having the Insert line repeated n times. How to do this?

我有情况需要写多个相同值的行来设置一些表。假设我必须添加120行,其中填充了两列。我正在寻找一个快捷方式,而不是重复插入行n次。这个怎么做?

6 个解决方案

#1

How about

Insert Table( colsnames )
Select Top 120 @value1, @Value2, etc.
From AnyTableWithMoreThan120Rows

Just make sure the types of the values in the @Value list matches the colNames List

只需确保@Value列表中的值类型与colNames列表匹配

#2

what about

insert into  tbl1
 (col1,col2)
(select top 120 @value1,@value2 from tbl2)

if in sql server 2008 . new in sql server 2008 to insert into a table multiple rows in a single query .

如果在sql server 2008中。 sql server 2008中的新增功能可在单个查询中将多行插入表中。

insert into  tbl1
 (col1,col2)
values
(@value1,@value2),(@value1,@value2),.....(@value1,@value2)

#3

In SQL Server Management Studio, you can use the "GO" keyword with a parameter:

在SQL Server Management Studio中,您可以将“GO”关键字与参数一起使用:

INSERT INTO YourTable(col1, col2, ...., colN)
VALUES(1, 'test', ....., 25)
GO 120

But that works only in Mgmt Studio (it's not a proper T-SQL command - it's a Mgmt Studio command word).

但这仅适用于Mgmt Studio(它不是一个合适的T-SQL命令 - 它是一个Mgmt Studio命令字)。

Marc

#4

Put the values in an unused table for safe keeping. From there you can insert from this table to the tables you need to setup.

将值放在未使用的表中以便安全保存。从那里,您可以从此表插入您需要设置的表。

#5

Create an Excel Spreadsheet with your data.

使用您的数据创建Excel电子表格。

Import the speadsheet into Sql Server.

将speadsheet导入Sql Server。

#6

You can even try with something like this(just an example)

你甚至可以试试这样的东西(只是一个例子)

declare @tbl table(col1 varchar(20),col2 varchar(20))
; with generateRows_cte as
(
    select 
        1 as MyRows

       union all
        select 
            MyRows+1

        from    generateRows_cte   
        where   MyRows < 120
)
insert into @tbl(col1,col2)
select 
'col1' + CAST(MyRows as varchar),'col2' + CAST(MyRows as varchar)
from generateRows_cte OPTION (MAXRECURSION 0)
select * from @tbl

Note:- Why not you are trying with Bulk insert into SqlServer from a dataset ? I didnot notice first that u have a front end too(VB)!

注意: - 为什么不尝试从数据集中批量插入SqlServer?我没有注意到你有一个前端(VB)!

#1