CODEWITHSUNDEEP
sql-servert-sql
pmh
pmh

Reputation: 312

Insert data into a table from comma separated string (T-SQL)

I have table with a integer column and the data as below:

4,89, 8, 1

How can I insert above integers in to a column.

Upvotes: 1

Views: 467

Answers (3)

Jay Rindani
Jay Rindani

Reputation: 40

Select *
From (Values (1),(2),(3),(4)) table (ColumnA)

Upvotes: -2

Santhosh kumar
Santhosh kumar

Reputation: 46

This can be achieved with a Table Value Constructor

Example:

Select *
From (Values (1),(2),(3),(4)) tabA (ColA)

Upvotes: 2

gotqn
gotqn

Reputation: 43666

Automatically:

DECLARE @DataSource TABLE
(
    [ColumnValue] INT
);

DECLARE @Input VARCHAR(MAX) = '4,89, 8, 1';

DECLARE @InputXML XML = CAST(N'<r><![CDATA[' + REPLACE(@Input, ',', ']]></r><r><![CDATA[') + ']]></r>' AS XML);

INSERT INTO @DataSource
SELECT RTRIM(LTRIM(Tbl.Col.value('.', 'INT'))) AS Code 
FROM @InputXML.nodes('//r') Tbl(Col)

Upvotes: 1

Related Questions