Insert 700000 rows

[bmacbmac]

Hi,

I have about 700,000 rows of data (10 characters each) in Excel.

I am trying to insert them into a temp table so I can run queries against it:

declare @temp table (test varchar(20), test2 int)

insert into @temp values ('D0000708',1)
insert into @temp values ('D0000708',2)
insert into @temp values ('D0000708',3)
insert into @temp values ('D0000709',1)
insert into @temp values ('D0000709',2)
insert into @temp values ('D0000709',3)

Needless to say I have stack space and memory errors when trying to insert all 700,000 rows. I could break it down into several chunks but my query would run much better if all the data is there at once.

Is there a better way I should go about this? Is there something I can add into my lines to make this more efficient? Should I 'GO' between each one?

Thanks!

Brian

 

[bmacbmac]

BTW, this is SQL 2000

 

[gmmastros]

try...

declare @temp table (test varchar(20), test2 int)

insert into @temp (test, test2)
Select 'D0000708',1
Union All Select 'D0000708',2
Union All Select 'D0000708',3
Union All Select 'D0000709',1
Union All Select 'D0000709',2
Union All Select 'D0000709',3

Really though... this is still going to be slow. Instead, you could set up a linked server, or openrowset, or dts, or probably several other ways, most of which would be faster.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Have you tried exporting it from excel to a CSV and import the flat file via DTS?

Worse case is you'll need to create a little script to import the CSV into your table. One line at a time. May take a little time to run, but won't overload your system.

--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[jbenson001]

This should be done using DTS.