inserting multiple rows

[arun_pk]

thanks for the response i wanted to add around 500000 rows of data in a table as atemplate for my project one field can be the primary key which will be auto incremented all others are repeated values... so is it possible for me to add multiple rows at one shot

[dan sh]

If you have all of that data in a table, you can use:

Insert into DataBaseTable
select * from SourceTable

You can also use Bulk Insert[^] for doing this.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

[arun_pk]

i dont have a alternate table ____ let me give u a clear picture lets assume my table is details with coloumns ID(primary key)(Auto incriment), Name so my insert statement is INSERT INTO DETAILS VALUES("abcd"); i want the same data 500000 times do i need to run it in a loop or is there any other method of adding multiple rows

[_Damian S_]

The easiest way is to use another table to create a cartesian product (ie: selecting from two or more tables with nothing linking them causes a cartesian product - one row for every possible combination). The following select statement will return over a million rows on my test SQL Server:

select 'a'
from dbo.sysobjects as so1, dbo.sysobjects as so2

Extending this into an insert statement, you would do this:

insert into TABLENAME(FIELD1, FIELD2)
select 'a', 'b'
from dbo.sysobjects as so1, dbo.sysobjects as so2

which will basically insert the values a and b into your table a million or so times... If you have a table that autonumbers, then you will have a million consecutive numbers generated with which to test with. Hope this helps.

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

[_Damian S_]

See my cartesian product answer below.

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

[_Damian S_]

I figured he had no data, but wanted 500K records to test with...

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

[arun_pk]

thanks a lot bro its working for meee

[_Damian S_]

No worries... glad I could help!

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

[dan sh]

:doh: My bad. I thought he needs to insert data from somewhere to his DB.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

[dan sh]

Wow! this is a real good way. :thumbsup:

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

[Niladri_Biswas]

If u are using

SQL SERVER 2008

, you can use

Table Valued Parameter

. Form your front end application(assuming .net), u need to send via a datatable to ur TVP. :)

Niladri Biswas

[arun_pk]

pls explain i m sorry i didnt get wat u said .. pls