insert multiple rows in database
 

How do i insert multiple rows in database.
for example: I have 3 list:
list1: a1,a2,a3,a4
list2: b1,b2,b3,b4
list3: c1,c2,c3,c4

I want to insert a1,b1,c1 in 1st row. a2,b2,c2 in 2nd row,...
How do I write one sql insert statement to do that?

Re: insert multiple rows in database
 

i'd imagine making a stored procedure to deal with the inserts would be the easiest way.

you can modify this sp to scroll through the list and put the whole lot of your list into variables then just insert 'em.

hope this helps

/*******************************************************************************

Description             Delimit a string and return specified segment

Author:                     Peter Yates

Example usage:        exec delimiter 'te~st~in~g1~23', '~', 5

Modifications:

*******************************************************************************/



if exists  (select * 

            from dbo.sysobjects 

            where id = object_id(N'[dbo].[delimiter]') 

            and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[delimiter]

go



create procedure delimiter(

 @str nvarchar (4000), --delimited string

 @del nvarchar (10), --delimiter

 @sect int --section of string wanted

 )

as

begin

 declare @nextstr nvarchar(4000)

 declare @pos int

 declare @nextpos int

 create table #valuetable (id int identity, value varchar(500))

 set @nextstr = ''

 set @str = @str + @del

 set @pos = charindex(@del,@str)

 set @nextpos = 1

 while (@pos <>  0)  

 begin

  set @nextstr = substring(@str,1,@pos - 1)

     insert into #valuetable 

      ([value]) 

      values 

      (@nextstr)

  set @str = substring(@str,@pos +1,len(@str))

  set @nextpos = @pos

  set @pos  = charindex(@del,@str)

 end

   select value 

   from #valuetable

   where id = @sect

 return

end

Re: insert multiple rows in database
 

Why use a stored procedure when we can do it in one sql??

This is an example of how to to it ....

INSERT INTO SEND_RULE(MRT_CODE, ISSUING_COMPANY_CODE, CARD_BRAND_CODE, SEND_ISSUING_COMPANY_CODE)
(SELECT MRT_CODE, ISSUING_COMPANY_CODE, CARD_BRAND_CODE, ISSUING_COMPANY_CODE SEND_ISSUING_COMPANY_CODE
FROM CARD_MRT_CONTRACT)
ORDER BY MRT_CODE, ISSUING_COMPANY_CODE, CARD_BRAND_CODE;

:mrgreen:

Rohit Kumar Singh

P.S> Im the best ;-)

Re: insert multiple rows in database
 

that would work if

a) the values were in another table

b) and they werent in a comma delimited list as per the original question :)

Re: insert multiple rows in database
 

I tried to use your solution but while creating sp for inserting rows, I ran into a problem, My table has more columns so when I say
"Insert into table1 (col1,col2,col3) values (@Nextstr)"
the sp compiler gives me error that column list has more items than value list. So just to trick that I created a columnstr and passed @Columnstr into Insert statement so now it looks like
"Insert into table1 (@Columnstr) values (@Nextstr)"
now it compiles ok, but runtime it gives error 'table1 object not found'

any idea ? any help is deeply appreciated.

thanks in advance.
TJ

Re: insert multiple rows in database
 

Just replying for anyone looking for an answer. Using MS SQL, you can do the following:

INSERT INTO test_table

SELECT 62

UNION

SELECT 91

UNION

SELECT 95

UNION 

SELECT 98

UNION 

SELECT 99

This will insert 5 rows into the table. Testing this on SQL Server 2000, the following works:

CREATE TABLE test

(

        val1 VARCHAR(10), 

        val2 VARCHAR(10), 

        val3 VARCHAR(10), 

        val4 VARCHAR(10)

)

GO



INSERT INTO test

(val1, val2, val3, val4)

(SELECT 'a1', 'a2', 'a3', 'a4')

UNION

(SELECT 'b1', 'b2', 'b3', 'b4')

UNION

(SELECT 'c1', 'c2', 'c3', 'c4')

GO