Batch Inserts - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Batch Inserts

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2007-03-04 : 12:11:13

Hi folks!

If I have to do the following:

For x = 1 To 3
For y = 1 To 365
Insert Into MyTable (Year,Day) Values (x,y)
Next y
Next x

Is there a way to have this done in a single statement instead of 3x365 statements?

Thanks!

spejbl
Starting Member

28 Posts

Posted - 2007-03-04 : 12:33:45

[code]DECLARE @x INT,@y INT
SET @x=1
WHILE @x<=3
BEGIN
SET @y=1
WHILE @x<=365
BEGIN
INSERT dbo.MyTable(Year,Day)VALUES(@x,@y)
SET @y=@y+1
END
SET @x=@x+1
END[/code]

kb
http://kbupdate.info/

WindChaser
Posting Yak Master

225 Posts

Posted - 2007-03-04 : 14:04:02

Thanks spejbl, but you're issuing the Insert statement 3x365 times. I'm asking if there's a way to do it in one single statement being issued once.

Thanks!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-04 : 18:32:14

This will do it.


insert into dbo.MyTable
	(Year,Day)
select
	Year	= (a.NUMBER/365)+1,
	Day	= (a.NUMBER%365)+1
from
	-- Function available at:
	-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
	F_TABLE_NUMBER_RANGE(0,1094) a
order by
	a.NUMBER

CODO ERGO SUM

WindChaser
Posting Yak Master

225 Posts

Posted - 2007-03-07 : 22:36:32

Thanks Michael. Powerful stuff.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources