SQL Command to execute multiple times?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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)

Answer 4

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

Answer 5

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

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