Insert query to create 6000 rows increment of 1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert query to create 6000 rows increment of 1

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-08-17 : 21:46:10

I want to create 6000 new contract rows, here are three insert queries, all i want to increment by 1 is ctrid and ctrno, is there a way an automated query process to create upto 600 new additional contract record just incrementing by 1, the last two fields.

The first two fields prgid and prjid are same for all 600 rows.

insert into tab_contracts(prgid,prjid,ctrid,ctrno)
values(1,2,17,contract17)

insert into tab_contracts(prgid,prjid,ctrid,ctrno)
values(1,2,18,contract18)

insert into tab_contracts(prgid,prjid,ctrid,ctrno)
values(1,2,19,contract19)

Thanks a lot for the helpful info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 23:08:00

[code]
;with NumTable(Num)
(
SELECT 17
UNION ALL
SELECT Num + 1
FROM NumTable
WHERE Num + 1 < = 6016
)

insert into tab_contracts
select 1,2,Num,'contract' + CAST(Num AS varchar(5))
FROM NumTable
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-18 : 08:00:02

[code];with NumTable(Num) AS
(
SELECT 17
UNION ALL
SELECT Num + 1
FROM NumTable
WHERE Num + 1 < = 6016
)

insert into tab_contracts
select 1,2,Num,'contract' + CAST(Num AS varchar(5))
FROM NumTable
OPTION (MAXRECURSION 0)[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 11:09:46

quote:

Originally posted by sunitabeck

;with NumTable(Num) AS
(
SELECT 17
UNION ALL
SELECT Num + 1
FROM NumTable
WHERE Num + 1 < = 6016
)

insert into tab_contracts
select 1,2,Num,'contract' + CAST(Num AS varchar(5))
FROM NumTable
OPTION (MAXRECURSION 0)

thanks for the catch

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-18 : 12:39:42

I must confess that I take great pleasure in finding something to correct in the queries you post. But unfortunately for me, such occasions are far and few in between, so when I found one, I couldn't resist!! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 12:47:00

quote:
Originally posted by sunitabeck

I must confess that I take great pleasure in finding something to correct in the queries you post. But unfortunately for me, such occasions are far and few in between, so when I found one, I couldn't resist!! :)

cool

so working on a weekend?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-18 : 12:52:01

quote:
so working on a weekend?

Yes, and it is GORGEOUS outside!! :(

There has to be a law against making people work on a sunny Saturday!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 12:54:55

quote:
Originally posted by sunitabeck

quote:
so working on a weekend?

Yes, and it is GORGEOUS outside!! :(

There has to be a law against making people work on a sunny Saturday!

Oh...thats bad
I was just catching up with my mails

I HATE working on weekends
Sunny weekends are not an unusual thing in this part so no special excitement in that though things were different when I was in northeastern part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2012-08-21 : 15:05:45

Thanks a lot Visakh & Sunita for the helpful query.

Really an amazing members are here on this forum, who help many.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 15:13:41

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources