I have a table as shown below
Is it possible to insert the above table data into a table in separate rows?
I tried using split function on each column and stored each column result on a temp table. I have no clue how to insert into new table combining all these rows and columns as per the id. Any help or suggestion would help.
Here is another method of CTE with help of XML node
There will no need to create any function.
WITH cte AS (
SELECT ID,
split.a.value('.', 'NVARCHAR(MAX)') [name],
ROW_NUMBER() OVER(ORDER BY ( SELECT 1)) RN
FROM
(
SELECT ID,
CAST('<A>'+REPLACE(name, ';', '</A><A>')+'</A>' AS XML) AS [name]
FROM <table_name>
) a
CROSS APPLY name.nodes('/A') AS split(a)),
CTE1 AS (
SELECT ID,
split.a.value('.', 'NVARCHAR(MAX)') [title],
ROW_NUMBER() OVER(ORDER BY ( SELECT 1 )) RN
FROM
(
SELECT ID,
CAST('<A>'+REPLACE(title, ';', '</A><A>')+'</A>' AS XML) AS [title]
FROM <table_name>
) aa
CROSS APPLY title.nodes('/A') AS split(a))
SELECT C.ID, C.name, C1.title FROM CTE C
JOIN CTE1 C1 ON C1.RN = C.RN
WHERE C.name != '' AND C1.title != '';
Result :
ID name title
1 a 12
1 b 13
1 s 45
2 c 67
2 f 56
2 u 34
3 l 90
3 k 70
3 m 60
Try this answer. Hope this helps you.
DECLARE @Table TABLE(ID INT, NAME VARCHAR(10),TITLE VARCHAR(10))
INSERT INTO @Table VALUES (1,';a;b;c',';12;13;14')
DECLARE @ID INT=1
SELECT @ID ID,Items,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN1 INTO #T1 FROM dbo.split((SELECT NAME FROM @Table WHERE id=@ID),';')
SELECT @ID ID,Items,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN2 INTO #T2 FROM dbo.split((SELECT TITLE FROM @Table WHERE id=@ID),';')
SELECT T1.ID,T1.Items NAME,T2.Items TITLE
FROM #T1 T1 INNER JOIN #T2 T2 ON T1.RN1=T2.RN2
DROP TABLE #T1
DROP TABLE #T2
If you want all the values, you just try the looping method like WHILE.
DECLARE @Table TABLE(ID INT, NAME VARCHAR(10),TITLE VARCHAR(10))
INSERT INTO @Table VALUES (1,';a;b;c',';12;13;14'),(2,';c;f;u',';67;56;34'),(3,';l;k;m',';90;70;60')
DECLARE @MinID INT,@MaxID INT
SELECT @MinID=MIN(ID),@MaxID=MAX(ID) FROM @Table
CREATE TABLE #T1(ID INT,Items VARCHAR(10),RN1 INT)
CREATE TABLE #T2(ID INT,Items VARCHAR(10),RN2 INT)
WHILE @MinID<=@MaxID
BEGIN
INSERT INTO #T1
SELECT @MinID ID,Items,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN1
FROM dbo.split((SELECT NAME FROM @Table WHERE id=@MinID),';')
INSERT INTO #T2
SELECT @MinID ID,Items,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN2
FROM dbo.split((SELECT TITLE FROM @Table WHERE id=@MinID),';')
SET @MinID=@MinID+1
END
SELECT T1.ID,T1.Items NAME,T2.Items TITLE
FROM #T1 T1 INNER JOIN #T2 T2 ON T1.ID=T2.ID AND T1.RN1=T2.RN2
DROP TABLE #T1
DROP TABLE #T2
This will produce the result, what you exactly want:
ID NAME TITLE
----------- ---------- ----------
1 a 12
1 b 13
1 c 14
2 c 67
2 f 56
2 u 34
3 l 90
3 k 70
3 m 60
Here is the split function, I used to split the Strings:
CREATE FUNCTION [dbo].[Split]
(@String VARCHAR (max), @Delimiter CHAR (1))
RETURNS
@temptable TABLE (
[items] VARCHAR (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
AS
begin
declare @idx int
declare @slice varchar(max)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
Try below way .. this will save time and memory also!
This T-SQL block has dependency on dbo.SplitString function ..
T1 is my Source table
T2 is my Destination table
DECLARE @c_s AS CURSOR;
DECLARE @id INT;
DECLARE @name VARCHAR(1000);
DECLARE @value VARCHAR(1000);
SET @c_s = CURSOR FOR SELECT * FROM T1;
OPEN @c_s;
FETCH @c_s INTO @id, @name, @value
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO T2
SELECT @id
, a.Value NAME
, b.value value
FROM dbo.SplitString(@name, ';') a
INNER JOIN dbo.SplitString(@value, ';') b
ON a.OrdinalPosition = b.OrdinalPosition
FETCH NEXT FROM @c_s INTO @id, @name, @value
END
here is the dbo.SplitString
CREATE FUNCTION [dbo].[SplitString](@givenString VARCHAR(8000) , @separator VARCHAR(100))
RETURNS TABLE AS
RETURN (
WITH data([start], [end]) AS (
SELECT 0 AS [start]
, CHARINDEX(@separator, @givenString) AS [end]
UNION ALL
SELECT [end] + 1
, CHARINDEX(@separator, @givenString, [end] + 1)
FROM data
WHERE [end] > 0
)
SELECT ROW_NUMBER() OVER (
ORDER BY OrdinalPosition
) OrdinalPosition
, RTRIM(LTRIM(Value)) Value
FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY [start]
) OrdinalPosition
, SUBSTRING(@givenString, [start], COALESCE(NULLIF([end], 0), len(@givenString) + 1) - [start]) Value
FROM data
) r
WHERE RTRIM(Value) <> ''
AND Value IS NOT NULL
)
You can achieve this by writing a table-valued function that will split the strings according to your requirements. Once you have created this object, then you can use the T-SQL in second code snippet to get your final required table.
The definition of this table-valued function is as given below. Just copy and paste this into SSMS and run it against your database.
Split a string function
-- =============================================
-- Author: B Vidhya
-- Create date: Nov 7, 2017
-- Description: Splits a string and returns a table
-- =============================================
CREATE FUNCTION [dbo].[SplitAString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @splitTable TABLE (
ItemNumber INT IDENTITY(1,1),
Item NVARCHAR(1000)
)
AS
BEGIN
DECLARE @startIndex INT,@endIndex INT
SET @startIndex = 1
IF SUBSTRING(@string, LEN(@string) - 1, LEN(@string)) <> @delimiter
BEGIN
SET @string = @string + @delimiter
END
WHILE CHARINDEX(@delimiter, @string) > 0
BEGIN
SET @endIndex = CHARINDEX(@delimiter, @string)
INSERT INTO @splitTable(Item)
SELECT SUBSTRING(@string, @startIndex, @endIndex - 1)
SET @string = SUBSTRING(@string, @endIndex + 1, LEN(@string))
END
RETURN
END
GO
In T-SQL below, I have called the original table StackOverflowTable1 and you can replace this table name with your actual table name. Also, I am inserting final rows into a table variable. If you wanted to insert into your custom table, then you could use perform an INSERT into your table after the END of WHILE loop.
T-SQL to get your final table
DECLARE @myTable TABLE
(Id INT,
Name VARCHAR(5000),
Title VARCHAR(5000)
);
DECLARE @lastId INT= 0, @id INT, @name VARCHAR(5000), @title VARCHAR(5000);
--for each record in table perform splitting and insertion in new table
WHILE EXISTS
(
SELECT 1
FROM StackOverFlowTable1 soft
WHERE Id > @lastId
)
BEGIN
SELECT TOP (1) @id = Id,
@name = Name,
@title = Title
FROM StackOverFlowTable1 soft
WHERE Id > @lastId
ORDER BY Id;
SET @lastId = @id;
INSERT INTO @myTable
(Id,
Name,
Title
)
SELECT @id,
ss1.Item,
ss2.Item
FROM dbo.SplitString(@name, ';') ss1
INNER JOIN dbo.SplitString(@title, ';') ss2 ON ss1.ItemNumber = ss2.ItemNumber
WHERE ss1.Item <> ''
AND ss2.Item <> '';
END;
SELECT * FROM @myTable;
I do not agree with Dinesh script because it is based on RBAR.
I have very similar Split function with also return row_number along with item.
so test my script along with other sample data.
DECLARE @Table TABLE(ID INT, NAME VARCHAR(10),TITLE VARCHAR(10))
INSERT INTO @Table VALUES (1,',a,b,c',',12,13,14')
SELECT id
,t.RowVal
,a.RowVal
FROM (
SELECT t.id
,a.RowNum
,a.RowVal
,t.TITLE
FROM @Table t
CROSS APPLY (
SELECT *
FROM dbo.FN_SPLIT_VALUE(t.NAME)
) a
) t
CROSS APPLY (
SELECT *
FROM dbo.FN_SPLIT_VALUE(t.TITLE)
WHERE t.RowNum = RowNum
) a
WHERE t.RowVal <> ''
