Insert Array of values into columns of the table in SQL Server

Question

I have an array

(1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16;)

I want to insert this array of values into the table like

Column1 |  Column2 | Column3 | Column4
-----------------------------------------
    1   |   2      |   3     |    4
    5   |   6      |   7     |    8
    9   |  10      |  11     |   12
   13   |  14      |  15     |   16

Are you asking this from a programming language point of view, or do you just want to conceptually add this data directly into SQL Server? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 8, 2017 at 4:32

Harsh Sharma · Accepted Answer · 2017-06-08 04:43:36Z

6

Try this:

INSERT INTO TableName (Column1,Column2,Column3,Column4) 
VALUES (1,2,3,4),(5,6,7,8),(9,10,11,12),(13,14,15,16)

answered Jun 8, 2017 at 4:43

Harsh Sharma

9301 gold badge7 silver badges21 bronze badges

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

1 Comment

Ben Farmer Over a year ago

Is there a way to do this "transposed"? I.e. provide all the values to go in each column, rather than each row? I ask because I already have large arrays for each column and seems dumb to loop through them all just to restructure the data in terms of rows.

Neo · Accepted Answer · 2017-06-08 07:27:18Z

2

DECLARE @array varchar(max) = '(1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16;)'
SET @array = REPLACE( REPLACE(@array, ';', '), ('), ', ()', '')
DECLARE @SQLQuery VARCHAR(MAX) = 'INSERT INTO TABLENAME (Column1,Column2,Column3,Column4) VALUES ' + @array
EXEC (@SQLQuery)

edited Jun 8, 2017 at 7:27

answered Jun 8, 2017 at 5:10

Neo

1356 bronze badges

8 Comments

Vinod Over a year ago

I want to insert 1 in column1 ,2 in column2, 3 in column3, 4 in column4........... not like this

Neo Over a year ago

it does exactly the same. try it... @Vinod

Vinod Over a year ago

i am geting like(1,2,3,4), (5,6,7,8), (9,10,11,12), (13,14,15,16) but i need 1 in column1 ,2 in column2, 3 in column3, 4 in column4...........

Neo Over a year ago

if youre running the exact code as above, youll get exact output as shown in your question.

Vinod Over a year ago

this is what i executed -Select REPLACE( REPLACE('(1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16;)', ';', '), ('), ', ()', '') and i got (1,2,3,4), (5,6,7,8), (9,10,11,12), (13,14,15,16)

|

Felix Pamittan · Accepted Answer · 2017-06-08 04:42:03Z

1

INSERT INTO TableName
VALUES 
(1,2,3,4),(5,6,7,8),(9,10,11,12),(13,14,15,16)

edited Jun 8, 2017 at 4:42

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

answered Jun 8, 2017 at 4:38

Hila DG

7346 silver badges14 bronze badges

Collectives™ on Stack Overflow

Insert Array of values into columns of the table in SQL Server

3 Answers 3

1 Comment

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related