Insert Multiple rows in Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Insert Multiple rows in Stored Procedure

Author

Topic

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-06 : 02:51:17

I am not sure if it is possible to insert multiple rows into the database using stored procedures. I have a comma delimited value such as "1,2,3" and need to insert 3 rows in this case. Anyone have any idea on how to help me? Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-06 : 03:19:02

You need to split them using Split function
Refer SeventhNight's function here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

Write query

Insert into yourTable(col) Select data from dbo.split('1,2,3',',')

Madhivanan

Failing to plan is Planning to fail

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-06 : 03:26:44

You need to run a loop to parse the values in your csv string and do an insert into your db.

Paste your script here, the solution is quiet simple

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-06 : 03:27:24

I tried adding the split function recommended by you into th stored procedure which contains "insert" sql statement. But met with some errors. Before i can edit anything after pasting the split function, it has errors already. So it has nothing to do with my insert stored procedure.

Any ideas on how to get rid of the errors? And how do i use the split function in my stored procedure?

[Code]
CREATE PROCEDURE [ADDPROMOTION]
(
@PROMOTIONNAME VARCHAR (100),
@PROMOSTARTDATE DATETIME,
@PROMOENDDATE DATETIME,
@DISCOUNTRATE INT,
@PROMODESC VARCHAR(100)

)

As

Declare @PID INT

-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@PROMOTIONNAME, @DISCOUNTRATE, @PROMOSTARTDATE, @PROMOENDDATE, @PROMODESC)
-- Now return the InventoryID of the newly inserted record
SET @PID = SCOPE_IDENTITY()

SELECT @PID

CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

GO

[/Code]

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-06 : 03:46:56

Use the split function like this :
INSERT INTO <TableName>
select data from dbo.split(@csvstring,',')

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-06 : 03:57:13

The split function below is giving me an error, indicating there is an error near the word "FUNCTION", is anything wrong?

CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-06 : 04:05:13

I Dont think there is any error in this.
Tell me which version of SQL server you are using?

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-06 : 04:19:27

I am using SQL Server 2000. I dont think there should be any error in the split function but i cant seem to add in into the stored procedure that also contains my insert statement.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-06 : 04:24:30

Hey are you trying to add this create function statement inside ur create procedure.
You need to compile the function separately and use it inside ur SP.

First Create the function

CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-06 : 04:48:46

I am not allowed to create a function without creating a stored procedure or something like that.
Error msg link:
[url]http://img127.imagevenue.com/img.php?loc=loc205&image=caf10_untitled.JPG[/url]

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-06 : 04:58:58

why are you using SQLDMO for creating a SP or a Function?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-06 : 05:14:56

Create the function in Query Analyser and test it

Madhivanan

Failing to plan is Planning to fail

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-06 : 05:15:02

I do not know what "SQLMO" is. But i am creating stored procedure or function using the Enterprise Manager. And it works so far for my except for creating functions, that i do not know why.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-01-06 : 05:22:31

Got it now..
Try it using QA
and if using EM then
right click on User defined functions tab in enterprise manager
select new user defined functions
create new UDF

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-06 : 12:06:42

Yeah, your right. I created the function in the user defined functions. Sorry, newbie to SQL so not so sure about all this. The line that is highlighted in Red, there is an error. Not sure if i have called the split function correctly. I have also included the split function for reference.

I wish to use the @PID and add @ITEMIDS which contains values such as "1,2,3,4" and split it before adding each itemid to @PID in the ITEMPROM table.


CREATE PROCEDURE [ADDPROMOTION] 
(
@PROMOTIONNAME VARCHAR (100),
@PROMOSTARTDATE DATETIME,
@PROMOENDDATE DATETIME,
@DISCOUNTRATE INT,
@PROMODESC VARCHAR(100),
@ITEMIDS VARCHAR(200)
)

As

Declare @PID INT, @SALEITEMID VARCHAR(10)

-- INSERT the new record
INSERT INTO 
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@PROMOTIONNAME, @DISCOUNTRATE, @PROMOSTARTDATE, @PROMOENDDATE, @PROMODESC)
-- Now return the InventoryID of the newly inserted record
SET @PID = SCOPE_IDENTITY()

SELECT @PID

SET @SALEITEMID = 'SELECT DATA FROM DBO.SPLIT(@ITEMIDS, ",")'

INSERT INTO ITEMPROM
(PROMOTIONID, ITEMID)
VALUES
(@PID, @SALEITEMID)

GO



CREATE FUNCTION SPLIT
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-06 : 13:09:23

Btw, i could not add the items to the ITEMPROM table. But i can add row in MSTRPROM. Anyone knows why? Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-06 : 21:00:40

If you have a fairly short list of comma delimited integers, less than 250 items, this code will load the list into a table with no loops or functions. It uses a simple hack to turn the list into a SELECT UNION ALL statement.


create table #temp ( List int not null )
declare @string varchar(100)
declare @sql varchar(8000)

-- Load comma delimited list of values to be loaded
set @string = '1,2,16,27,38,49,100,345,9975'

-- Turn the delimited list into a union statement
-- by replacing the comma delimiters in @string
set @sql = 'insert into #temp select '+
		replace(@string,',',' union all select ')

print 'Load the values into table #temp'
exec(@sql)

print 'Show values in table #temp'
select * from #temp
go 
drop table #temp


-------------------------------------------------------
--------- Output Results ------------------------------
-------------------------------------------------------

Load the values into table #temp

(9 row(s) affected)

Show values in table #temp
List        
----------- 
1
2
16
27
38
49
100
345
9975

(9 row(s) affected)

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-01-06 : 21:24:45

I think you need to change:


SET @SALEITEMID = 'SELECT DATA FROM DBO.SPLIT(@ITEMIDS, ",")'

INSERT INTO ITEMPROM
(PROMOTIONID, ITEMID)
VALUES
(@PID, @SALEITEMID)


INSERT INTO ITEMPROM
(PROMOTIONID, ITEMID)
SELECT @PID, [Data]
FROM DBO.SPLIT(@ITEMIDS, ',')

Kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-06 : 22:22:55

It works! Simply fantastic short and sweet. Im just wondering usually when we use the insert statement, we need "VALUES(1,2)" or something like that right. How come you were able to use "SELECT @PID, [Data]
FROM DBO.SPLIT(@ITEMIDS, ',')" to replace that? Is there any simple explaination for that?

quote:

Originally posted by Kristen

I think you need to change:


SET @SALEITEMID = 'SELECT DATA FROM DBO.SPLIT(@ITEMIDS, ",")'

INSERT INTO ITEMPROM
(PROMOTIONID, ITEMID)
VALUES
(@PID, @SALEITEMID)


INSERT INTO ITEMPROM
(PROMOTIONID, ITEMID)
SELECT @PID, [Data]
FROM DBO.SPLIT(@ITEMIDS, ',')

Kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-06 : 22:25:57

I dont quite understand this line of code: "replace(@string,',',' union all select ')". Can you provide a simple example for this. Sorry, im still very new in SQL. I know you execute the @sql at the end.

Do not mind me asking but creating a temp table and dropping it, will it make the SQL server slower in terms of performance? Thanks

quote:

Originally posted by Michael Valentine Jones

If you have a fairly short list of comma delimited integers, less than 250 items, this code will load the list into a table with no loops or functions. It uses a simple hack to turn the list into a SELECT UNION ALL statement.


create table #temp ( List int not null )
declare @string varchar(100)
declare @sql varchar(8000)

-- Load comma delimited list of values to be loaded
set @string = '1,2,16,27,38,49,100,345,9975'

-- Turn the delimited list into a union statement
-- by replacing the comma delimiters in @string
set @sql = 'insert into #temp select '+
		replace(@string,',',' union all select ')

print 'Load the values into table #temp'
exec(@sql)

print 'Show values in table #temp'
select * from #temp
go 
drop table #temp


-------------------------------------------------------
--------- Output Results ------------------------------
-------------------------------------------------------

Load the values into table #temp

(9 row(s) affected)

Show values in table #temp
List        
----------- 
1
2
16
27
38
49
100
345
9975

(9 row(s) affected)

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-01-07 : 06:15:52

"How come you were able to use "SELECT @PID, [Data]
FROM DBO.SPLIT(@ITEMIDS, ',')" to replace that? Is there any simple explaination for that?"

My Simple Explanation is a bit terse I'm afraid - you need to read some books, or a tutorial, on SQL!

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources