Splitting 2 delimited string values into one table

Question

Post reply

Splitting 2 delimited string values into one table

SQLCurious

SSCrazy

Points: 2339
More actions
May 10, 2016 at 11:13 am

#326919

I have 2 separate strings that are pipe delimited - Ex: '1|2|3|4|5' and 'AAA|BBB|CCC|DDD|EEE'
I need to get these two strings parsed out and combine them into a table (into two separate columns), I need to insert/update/delete these into/from another table based on the value in @Operation.
Can someone suggest the best possible way to accomplish this please? Thanks in advance!
SQLCurious
DECLARE @IDList NVARCHAR (1000) = '1|2|3|4|5'
DECLARE @NameList NVARCHAR (1000) = 'AAA|BBB|CCC|DDD|EEE'
DECLARE @Delimiter VARCHAR (1) = '|'
DECLARE @Operation VARCHAR (10) = 'INSERT' -- This is an enumerated value that can have 'INSERT', 'UPDATE', or 'DELETE'
DECLARE @SourceTable (ID INT, Name VARCHAR (100))
-- Hacking this table with an insert to show the values that I want to get into it
INSERT @SourceTable (ID, Name) VALUES (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD'), (5, 'EEE')
If @Operation = 'INSERT'
INSERT @DestinationTable (ID, Name) SELECT ID, Name FROM @SourceTable
If @Operation = 'UPDATE'
UPDATE @DestinationTable
SET ID = s.ID, Name = s.Name
FROM @DestinationTable d
INNER JOIN @SourceTable s ON d.ID = s.ID
If @Operation = 'DELETE'
DELETE @DestinationTable
WHERE ID IN (SELECT ID FROM @SourceTable)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

You could use the DelimitedSplit shared in this article: http://www.sqlservercentral.com/articles/Tally+Table/72993/

DECLARE @IDList NVARCHAR (1000) = '1|2|3|4|5'

DECLARE @NameList NVARCHAR (1000) = 'AAA|BBB|CCC|DDD|EEE'

DECLARE @Delimiter VARCHAR (1) = '|'

DECLARE @Operation VARCHAR (10) = 'INSERT' -- This is an enumerated value that can have 'INSERT', 'UPDATE', or 'DELETE'

DECLARE @DestinationTable TABLE (ID INT, Name VARCHAR (100))

If @Operation = 'INSERT'

INSERT @DestinationTable (ID, Name)

SELECT i.Item, n.Item

FROM dbo.DelimitedSplitN4K(@IDList, @Delimiter) i

JOIN dbo.DelimitedSplitN4K(@NameList, @Delimiter) n ON i.ItemNumber = n.ItemNumber

--SELECT * FROM @DestinationTable;

--SET @Operation = 'UPDATE'

--SET @NameList = 'MMMM|NNNN|OOOO|PPPP|QQQQ'

If @Operation = 'UPDATE'

UPDATE @DestinationTable

SET ID = i.Item, Name = n.Item

FROM @DestinationTable d

JOIN dbo.DelimitedSplitN4K(@IDList, @Delimiter) i ON d.ID = i.Item

JOIN dbo.DelimitedSplitN4K(@NameList, @Delimiter) n ON i.ItemNumber = n.ItemNumber

--SELECT * FROM @DestinationTable;

--SET @Operation = 'DELETE';

If @Operation = 'DELETE'

DELETE @DestinationTable

WHERE ID IN (SELECT i.Item FROM dbo.DelimitedSplitN4K(@IDList, @Delimiter) i)

--SELECT * FROM @DestinationTable;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLCurious SSCrazy Points: 2339 More actions · Answer 2

Thanks for your suggestion, Luis Cazares. It worked for me perfectly. I appreciate your valuable time on this.

SQLCurious