May 20, 2010 at 1:04 am
All,
I have a scenario.
Step1 : User will pass delimited values.
Step2 : I used 'Tally' table to split the delimited values
Step3 : Splittled values will be stored in a table as rows.
say for example,
'A123,B123,C123,D123,E123,F123' -These are all AssetID
CREATE TABLE #t
(
AssetID VARCHAR(10)
)
DECLARE @Parameter VARCHAR(max)
SET @Parameter = ',' + 'A123,B123,C123,D123,E123,F123' + ','
INSERT INTO #t
SELECT LTRIM(RTRIM(SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)))
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ','
WHILE loop is being used to split the delimited values. I have to replace it with TALLY code.
Step4 : There is a table which contain all the AssetID's.
CREATE TABLE ReportData
(
AssetID VARCHAR(10),
ModifiedDate DATETIME,
UserID VARCHAR(25)
)
INSERT INTO ReportData
SELECT 'A123',getdate(),'karthik'
UNION
SELECT 'B123',getdate(),'karthik'
UNION
SELECT 'C123',getdate(),'karthik'
UNION
SELECT 'D123',getdate(),'karthik'
Step 5: My requirement is to check whether the above ID's (splitted one) are available in the table or not. If exists just i have to update it with the new values otherwise (if it is not available in the original table. i.e it is a new AssetID) those ID's needs to be inserted into the table.
Step 6: Currently this process is running with 'RBAR' techniques. i.e CURSOR is being used to do this.
Step 7: I want to avoid CURSOR and want to apply SET BASED technique for this.
Step 8: I thought like add one more column in #t1 table say for example ALTER TABLE #t1 ADD Exist_Flag CHAR(1) NULL, update the flag value like
UPDATE #t1
SET Exists_Flag = 'Y'
FROM ReportDate, #t1
WHERE ReportDate.AssetID = #t1.AssetID
after that if i execute the below query
UPDATE ReportData
SET ModifiedDate = @Date,
UserID = @user-id
FROM ReportData, #t1
WHERE ReportData.AssetID = #t1.AssetID
AND Exists_Flag ='Y'
INSERT INTO ReportData
SELECT #t1.AssetID, (SELECT @Date), (SELECT@UserID)
FROM #t1,ReportData
WHERE ReportData.AssetID = #t1.AssetID
AND Exists_Flag IS NULL
I have planned to do like as i said above. I hope i can eliminate the usage of CURSOR. But i just want to know is there any other way to do the same. I am sure we can pick an egg from the basket with different way. i.e by hand or by mouth,like that i just want to see some other way.
Inputs are welcome!
karthik
May 20, 2010 at 4:09 am
Try fnSplit. Its a Table valued function. This function will return the character separated values in a table.
By using this function, there will be no need of creating temporary table. Also, you can apply the Update and Insert queries directly. Like;
CREATE TABLE ReportData
(
AssetID VARCHAR(10),
ModifiedDate DATETIME,
UserID VARCHAR(25)
)
DECLARE @Parameter VARCHAR(max)
SET @Parameter = 'A123,B123,C123,D123,E123,F123'
--Select [value] from dbo.fnSplit(@Parameter,',')
UPDATE ReportData
SET ModifiedDate = getdate(),
UserID = 'Atif'
FROM ReportData a
Inner Join dbo.fnSplit(@Parameter,',') b on b.[Value] = a.AssetID
Insert into ReportData
Select [value],getdate(),'Atif'
from dbo.fnSplit(@Parameter,',') a
Left Outer Join ReportData b on b.AssetID = a.[value]
Where b.AssetID is Null
Select * from ReportData
May 20, 2010 at 5:19 am
I can suggest cursor-free and UDF-free string split functionality :
declare @pInput varchar(max)
set @pInput = 'A123,B123,C123,D123,E123,F123'
;with split
as
(
select CASE WHEN LEN(@pInput)= 0 THEN ''
WHEN CHARINDEX(',',@pInput) = 0 THEN @pInput
ELSE LEFT(@pInput, CHARINDEX(',',@pInput)-1) END as id
,CASE WHEN LEN(@pInput)=0 THEN ''
WHEN CHARINDEX(',',@pInput) = 0 THEN ''
ELSE SUBSTRING(@pInput, CHARINDEX(',',@pInput) +1,8000) END as ids
union all
select LEFT(ids, CHARINDEX(',',ids)-1) as id
,SUBSTRING(ids, CHARINDEX(',',ids) +1,8000) as ids
from split where CHARINDEX(',',ids) > 0
)
select id from split WHERE id != ''
May 20, 2010 at 5:26 am
Not Returning all values...
May 20, 2010 at 5:27 am
elutin (5/20/2010)
I can suggest cursor-free and UDF-free string split functionality :
declare @pInput varchar(max)
set @pInput = 'A123,B123,C123,D123,E123,F123'
;with split
as
(
select CASE WHEN LEN(@pInput)= 0 THEN ''
WHEN CHARINDEX(',',@pInput) = 0 THEN @pInput
ELSE LEFT(@pInput, CHARINDEX(',',@pInput)-1) END as id
,CASE WHEN LEN(@pInput)=0 THEN ''
WHEN CHARINDEX(',',@pInput) = 0 THEN ''
ELSE SUBSTRING(@pInput, CHARINDEX(',',@pInput) +1,8000) END as ids
union all
select LEFT(ids, CHARINDEX(',',ids)-1) as id
,SUBSTRING(ids, CHARINDEX(',',ids) +1,8000) as ids
from split where CHARINDEX(',',ids) > 0
)
select id from split WHERE id != ''
Not returning all values... Can you please check again?
May 20, 2010 at 5:53 am
try this:
declare @pInput varchar(max)
set @pInput = 'A123,B123,C123,D123,E123,F123'
;with split
as
(
select CASE WHEN LEN(@pInput)= 0 THEN ''
WHEN CHARINDEX(',',@pInput) = 0 THEN @pInput
ELSE LEFT(@pInput, CHARINDEX(',',@pInput)-1) END as id
,CASE WHEN LEN(@pInput)=0 THEN ''
WHEN CHARINDEX(',',@pInput) = 0 THEN ''
ELSE SUBSTRING(@pInput, CHARINDEX(',',@pInput) +1,8000) END as ids
union all
select CASE WHEN CHARINDEX(',',ids) = 0 THEN ids
ELSE LEFT(ids, CHARINDEX(',',ids)-1)
END as id
,CASE WHEN CHARINDEX(',',ids) = 0 THEN ''
ELSE SUBSTRING(ids, CHARINDEX(',',ids) + 1,8000) END as ids
from split where len(ids) > 0
)
select id from split WHERE id != ''
however if you have performance and scalability concerns you better use tally table way (again non-cursor and non-udf):
declare @pInput varchar(max)
declare @n int
set @pInput = 'A123,B123,C123,D123,E123,F123'
set @pInput = ',' + 'A123,B123,C123,D123,E123,F123' + ',' -- you will need to add separators
select @n =LEN(@pInput)
set rowcount @n
select IDENTITY( int,1,1) as id into #tally
from sys.columns
set rowcount 0
select SUBSTRING(@pInput,id+1,CHARINDEX(',',@pInput,id+1)-id-1)
from #tally
where SUBSTRING(@pInput,id,1) = ',' and id < LEN(@pInput)
drop table #tally
May 20, 2010 at 6:00 am
I wanted to write an articale about using Tally table for cursor-less string splitting but it was already done by Jeff Moden: http://www.sqlservercentral.com/articles/T-SQL/62867/
You can find there that you could create permanent tally table in your database and index it for the best performance... Must Read article.
Don't split you string in the cursor/loop-based UDF - it is slow.
You may consider using CLR function for it, using CLR may be faster for not very large data volumes.
May 21, 2010 at 4:05 am
any other way ?
karthik
May 21, 2010 at 4:31 am
karthikeyan-444867 (5/20/2010)
Step1 : User will pass delimited values.Step2 : I used 'Tally' table to split the delimited values
Step3 : Splittled values will be stored in a table as rows.
say for example,
'A123,B123,C123,D123,E123,F123' -These are all AssetID
CREATE TABLE #t(
AssetID VARCHAR(10)
)
DECLARE @Parameter VARCHAR(max)
SET @Parameter = ',' + 'A123,B123,C123,D123,E123,F123' + ','
INSERT INTO #t
SELECT LTRIM(RTRIM(SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)))
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ','
WHILE loop is being used to split the delimited values. I have to replace it with TALLY code.
Nope, this isn't a WHILE loop, I'm fairly confident it's set-based. It works, too, and even returns the asset id's as a set so they could be used as a table-source in an update query.
So, what's the problem?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 21, 2010 at 6:35 am
Nope, this isn't a WHILE loop, I'm fairly confident it's set-based.
No...No...Curently the procedure is using LOOP to split the delimited values. I have to replace it with the above said code. Thats what i mentioned. But it may be unclear due to my poor writing skill.
karthik
May 25, 2010 at 4:43 am
any other way to pick the egg?
karthik
May 25, 2010 at 5:20 am
DECLARE @Parameter VARCHAR(MAX);
SET @Parameter = ',' + 'A123,B123,C123,D123,E123,F123' + ','
BEGIN TRANSACTION
-- Update existing items
UPDATE ReportData
SET -- New modification date
ModifiedDate = CURRENT_TIMESTAMP,
-- Remove the updated assetid from the string
@Parameter = REPLACE(@Parameter, ',' + AssetID + ',', ',')
WHERE CHARINDEX(',' + AssetID + ',', @Parameter) > 0;
-- Add new items
WITH Items
AS (
-- Use your favourite string-splitter here
SELECT item
FROM dbo.SplitString_Multi(@Parameter, N',')
)
INSERT ReportData (AssetID, ModifiedDate, UserID)
SELECT item, CURRENT_TIMESTAMP, 'Paul'
FROM Items
WHERE NOT EXISTS
(
SELECT *
FROM ReportData RD WITH (UPDLOCK, HOLDLOCK)
WHERE RD.AssetID = Items.item
);
-- Would use MERGE in 2008
COMMIT TRANSACTION;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply