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

Question

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.

What have you tried so far? Where are you stuck?

Nico Haase
– Nico Haase

2020-02-02 21:07:01 +00:00
Commented Feb 2, 2020 at 21:07 — Nico Haase
– Nico Haase, Commented Feb 2, 2020 at 21:07

Dale K · Accepted Answer · 2020-01-30 06:05:11Z

2

This can be achieved with a Table Value Constructor

Example:

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

edited Jan 30, 2020 at 6:05

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Jan 30, 2020 at 5:44

Santhosh kumar

463 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gotqn · Accepted Answer · 2020-01-30 05:57:31Z

1

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)

answered Jan 30, 2020 at 5:57

gotqn

44k47 gold badges167 silver badges257 bronze badges

Comments

Jay Rindani · Accepted Answer · 2020-02-03 08:13:42Z

-2

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

edited Feb 3, 2020 at 8:13

answered Jan 30, 2020 at 5:55

Jay Rindani

403 bronze badges

1 Comment

Peyman Mohamadpour Over a year ago

Please add a short description into your answer to make it more useful

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related