inserting multiple rows

arun_pk

how to insert multiple rows in sql using a single sql command thanks in advance arun

_Damian S_

It depends where the data is coming from... Can you be a little more specific?

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 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...!!

_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!!

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_

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!!

_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!!

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