•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 374,443 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,909 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser:
Views: 42486 | Replies: 5
 |
•
•
Join Date: Oct 2005
Posts: 4
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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?
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?
•
•
Join Date: Oct 2005
Location: Manchester, UK
Posts: 481
Reputation:
Rep Power: 3
Solved Threads: 30
Posting Pro in Training
•
•
•
•
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
/*******************************************************************************
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 •
•
Join Date: Nov 2005
Posts: 1
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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 ;-)
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 ;-)
•
•
Join Date: Oct 2005
Location: Manchester, UK
Posts: 481
Reputation:
Rep Power: 3
Solved Threads: 30
Posting Pro in Training
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
a) the values were in another table
b) and they werent in a comma delimited list as per the original question
•
•
Join Date: Jan 2006
Posts: 14
Reputation:
Rep Power: 3
Solved Threads: 0
Newbie Poster
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
"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
•
•
Join Date: Jun 2007
Posts: 1
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
Just replying for anyone looking for an answer. Using MS SQL, you can do the following:
This will insert 5 rows into the table. Testing this on SQL Server 2000, the following works:
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
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
Linear Mode
- How to store multiple selection? (VB.NET)
- updating multiple rows with one form (PHP)
- Insert Dataset into SQL Database. (VB.NET)
Other Threads in the MS SQL Forum
- Previous Thread: Seemingly Simple Issue
- Next Thread: Complex Query Help
