multiple insert - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

multiple insert

Author

Topic

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-01-06 : 06:28:35

hi i need help in this Query for mutliple data insertion

i am trying to create a stored procedure like this, and i dint know how to proceed further, so looking forward for help

create procedure Test
@Application_ID varchar(16),
@Subcategory Varchar(1000)
AS
begin
Insert into application_subcategory (application_id, subcategory)
select @application_id, @Subcategory
end

@application_id variable will just have one value
but @subcategory will have more than one value concatenated

EG- @Application Id will have single value something like 'CMT'
but @subcategory will have value 'Test,subcategory1, subcategory2,sbucategory3'
There is no limit for the values present in the subcategory variable.
I need to insert one record in application_subcategory table for each subcategory value
Table should get inserted with data like this
like
Application_ID SubCategory
CMT Test
CMT subcategory1
CMT subcategory2
CMT subcategory3

Can anyone help me on these. Thanks Before Hand

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-06 : 06:44:04

try this

create procedure Test
(
@Application_ID varchar(16),
@Subcategory Varchar(1000)
)
AS
set nocount on
begin

DECLARE @Details TABLE( subcategoryId varchar(max))
;WITH raky (i,j)
AS
(
SELECT i = 1, j= charindex(',', @Subcategory+ ',')
UNION ALL

SELECT i = j+1 , j = charindex(',', @Subcategory+ ',', j+1)
FROM raky WHERE charindex(',', @Subcategory+ ',', j+1)<>0
)
INSERT INTO @Details
SELECT SUBSTRING(@Subcategory, i, j-i) FROM raky

Insert into application_subcategory (application_id, subcategory)
SELECT
@Application_ID, subcategoryId
FROM
@Details
WHERE
PATINDEX('%,' + CAST(subcategoryid AS VARCHAR(20))+',%', ',' + @Subcategory + ',') > 0
end
set nocount off

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-06 : 06:49:15

Use this inside your procedure.

Insert into application_subcategory (application_id, subcategory)
select @application_id,val from parsevalues(@Subcategory)

Compile this function before you run it.

CREATE FUNCTION ParseValues  
(@String varchar(8000)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val varchar(100) 
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

swathigardas
Posting Yak Master

149 Posts

Posted - 2009-01-06 : 09:41:06

Thanks a Lot For the Reply.. They Work Perfectly..

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-06 : 10:26:16

Welcome...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources