Buld Data load import

HI

I have a table XYZ that needs to contain a million records as operational data.

XYZ has a column named SLOT_VALUE which has values 1,2,3....100,000

What is the easiset way to bulk load this information in the shortest possible time...

Insert scripts/ Batch program like if..while loop takes heck lot of time....

Hello,

If you mean that you just want to populate that table with incrementing values from 1 to 1MIL, then the easiest would be to execute something like

select top 1000000 identity(int,1,1) as Num
into TempNumTable
from syscolumns c1
cross join syscolumns c2
cross join syscolumns c3
cross join syscolumns c4
... etc

You can't just directly insert into your already created table as the identity() function need a select into clause. You can just insert from the newly created table into your XYZ operational table.

If this is not what you mean, and you have a flat file that you want to bulk load into the table, have a look at the BULK INSERT command (use TABLOCK and consider dropping any existing indexes on the table prior to the load).

Cheers,

Rob

|||

Guess the easiest is to have t-sql script using a while loop...I thought of bcp but then I need to create a file...that would be redundant...

Anyhow thnx for the post.

|||

No worries. Just remember that performing this operation within a while loop will entail 1 million individual inserts! The set based solution described above would be much faster and much more efficient. If this is a once-off operation, then it's probably not an issue.

Cheers,
Rob