Ad:

MS SQL Discussion Thread
Unsolved
Views: 136575

Oct 26th, 2005

insert multiple rows in database

Expand Post »

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?

Similar Threads

updating multiple rows with one form in PHP
Insert Dataset into SQL Database. in VB.NET
How to store multiple selection? in VB.NET

sweetyp

Reputation Points: 11

Solved Threads: 0

Newbie Poster

Offline

4 posts
since Oct 2005

Permalink

Oct 31st, 2005

Re: insert multiple rows in database

Quote originally posted by sweetyp ...

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?

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

MS SQL Syntax (Toggle Plain Text)
/*******************************************************************************
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
/*******************************************************************************
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

pty

Reputation Points: 64

Solved Threads: 39

Posting Pro

Offline

530 posts
since Oct 2005

Permalink

Nov 17th, 2005

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 ;-)

rohitk2000

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Nov 2005

Permalink

Nov 18th, 2005

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

pty

Reputation Points: 64

Solved Threads: 39

Posting Pro

Offline

530 posts
since Oct 2005

Permalink

Mar 14th, 2006

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

Texpert

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

14 posts
since Jan 2006

Permalink

Jun 22nd, 2007

Re: insert multiple rows in database

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

MS SQL Syntax (Toggle Plain Text)
INSERT INTO test_table
SELECT 62
UNION
SELECT 91
UNION
SELECT 95
UNION 
SELECT 98
UNION 
SELECT 99
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:

MS SQL Syntax (Toggle Plain Text)
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
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

JacobSteelsmith

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Jun 2007

Permalink

Feb 2nd, 2011

Re: insert multiple rows in database

hey can you help me for making a frame on sql

dandoy

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Feb 2011

Permalink

Dec 1st, 2011

How to Insert multiple rows to table in SQL Server

It would be easier to use XML in SQL Server to insert multiple rows otherwise it becomes very tedious.

View full article with code explanations here [snipped]

Copy the following code into sql server to view a sample.

MS SQL Syntax (Toggle Plain Text)
declare @test nvarchar(max)
 
SET @test = '<topic><dialog id="1" answerId="41">
        <comment>comment 1</comment>
        </dialog>
    <dialog id="2" answerId="42" >
    <comment>comment 2</comment>
        </dialog>
    <dialog id="3" answerId="43" >
    <comment>comment 3</comment>
        </dialog>
    </topic>'
 
declare @testxml xml
SET @testxml = cast(@test AS xml)
declare @answerTemp TABLE(dialogid int, answerid int, comment varchar(1000))
 
INSERT @answerTemp
SELECT  ParamValues.ID.value('@id','int') ,
ParamValues.ID.value('@answerId','int') ,
ParamValues.ID.value('(comment)[1]','VARCHAR(1000)')
FROM @testxml.nodes('topic/dialog') AS ParamValues(ID)
declare @test nvarchar(max)

set @test = '<topic><dialog id="1" answerId="41">
        <comment>comment 1</comment>
        </dialog>
    <dialog id="2" answerId="42" >
    <comment>comment 2</comment>
        </dialog>
    <dialog id="3" answerId="43" >
    <comment>comment 3</comment>
        </dialog>
    </topic>'

declare @testxml xml
set @testxml = cast(@test as xml)
declare @answerTemp Table(dialogid int, answerid int, comment varchar(1000))

insert @answerTemp
SELECT  ParamValues.ID.value('@id','int') ,
ParamValues.ID.value('@answerId','int') ,
ParamValues.ID.value('(comment)[1]','VARCHAR(1000)')
FROM @testxml.nodes('topic/dialog') as ParamValues(ID)

View full article with code explanations here [snipped]

Last edited by Ezzaral; Dec 1st, 2011 at 6:02 pm. Reason: Snipped blog links. Please do not use the forums to promote your blog.

joe_bolla

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Dec 2011

Previous Thread in MS SQL Forum Timeline: SQL Field creation Script for fields that may already exist

Next Thread in MS SQL Forum Timeline: crystal report connection information problem

DaniWeb Message

Cancel Changes

User Name
Password