SQL Command to execute multiple times?

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?

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

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

Marc

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

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 .

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

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.

1. Create an Excel Spreadsheet with your data.
2. Import the speadsheet into Sql Server.

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)!