CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Alex
Alex

Reputation: 2111

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?

Upvotes: 3

Views: 4369

Answers (6)

priyanka.sarkar
priyanka.sarkar

Reputation: 26498

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

Upvotes: 0

marc_s
marc_s

Reputation: 754508

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

Upvotes: 3

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

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

Upvotes: 0

anishMarokey
anishMarokey

Reputation: 11397

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)

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146499

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

Upvotes: 1

JeffO
JeffO

Reputation: 8043

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.

Upvotes: 0

Related Questions