ktlady (6/20/2009)
I have a table (tableA) with following format while Column1 is primary key and Column2 is delimited with ":".Column1 Column2
a1 1:3:5:6
a2 2:4:5
I'd like to use stored procedure to transform tableA to tableB and insert into tableB as following:
Column1 Column2
a1 1
a1 3
a1 5
a1 6
a2 2
a2 4
a2 5
In case of tableB, the primary key would become Column1 & Column2.
Could someone help me with the T-SQL stored procedure code in handling this?
Thanks a lot!
You can use some xml to do the trick...
-- first make a temp table to hold the sample data
declare @Tmp table (
ColumnA char(2),
ColumnB varchar(50),
TmpCol XML NULL) --<<<<< NOTE the new column being added
-- put the sample data into the temp table.
-- NOTICE how this makes it so much easier for people to just copy and start testing
insert into @Tmp (ColumnA, ColumnB)
select 'a1', '1:3:5:6' UNION ALL
select 'a2', '2:4:5'
-- update the xml column by replacing the delimiter with XML tags,
-- and putting the appropriate XML tags around the string.
update @Tmp
set TmpCol = '' + replace(ColumnB, ':', '') + ''
-- shred the xml data apart into individual rows
select T.ColumnA,
x.data.value('.','int') AS ColumnB
--INTO TABLE2
from @Tmp T
CROSS APPLY TmpCol.nodes('/rows/row') AS x(data)
results:
ColumnAColumnB
a1 1
a1 3
a1 5
a1 6
a2 2
a2 4
a2 5
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes