CODEWITHSUNDEEP
t-sqlsql-server-2014
Koo SengSeng
Koo SengSeng

Reputation: 953

Loop N table columns and insert concatenated string into same row

I have a string list with concatenated value separated by delimiter like this:

DECLARE @stringlist NVARCHAR(MAX) = 'company_no,emp_no,emp_name,emp_type,salary_type....'

And I have a temp table with dynamic number of nameless columns with following data:

------------------------------
001   A01   John  P     A
001   A05   Mary  P     A

I want to split the string and insert each split string into each column in one row, such as below:

-----------------------------------------------
company_no emp_no emp_name emp_type salary_type
001        A01    John     P        A
001        A05    Mary     P        A

I can split the string into multiple rows. But how do I split and loop each column in temp table to insert these value?

Upvotes: 1

Views: 93

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

Assuming by your sample data and desired result that all columns in the destination table are some string type, I would suggest not to split the string at all.
Instead, create a dynamic sql insert statement from it:

DECLARE @Sql nvarchar(max);
SET @Sql = 'INSERT INTO <TempTableNameHere> 
            VALUES ('''+ REPLACE(@stringList, ',', ''',''') + ''');';

EXEC(@Sql);

Upvotes: 5

Related Questions