How to insert multiple rows in a table with single row?

Question

I Have a table where I am trying to insert multiple rows into a single column... Any easy way of doing this ?

insert into tableA
(name) values(
'A',
'B',
'C',
.,
.,

I have about 400 rows I am getting the names from a excel sheet I tried using import from excel but that didn't work as it was throwing an error saying the format is wrong.

Any kind of ideas appreciated Thanks.

You will need to make it all one big string – Alex Hall Jan 11 '21 at 21:17 — Alex Hall, Jan 11 '21 at 21:17

score 2 · Accepted Answer · answered Jan 11 '21 at 21:11

2

Yes (assuming you are on SQL Server 2008 or newer and don't have more than 1000 columns per row).

You can insert rows with the same schema by separating the value blocks with commas.

INSERT INTO tableA (name)
VALUES ('A'), ('B'), ('C')...

The way you're currently doing it is attempting to add each row as a column.

answered Jan 11 '21 at 21:11

D M

5,769
4
12
27

Yes... How do I do it for only rows ? – Alice Jan 11 '21 at 21:13
1

Wait, you want a new row for each value or you want all values in individual columns in a single row? – D M Jan 11 '21 at 21:16
Hi.. I figured it out. I edited the top 200 rows in the db and copy pasted from excel It worked :) – Alice Jan 11 '21 at 21:27
I wanted to add about 400 names in multiple rows but in a single column but of course not manually. – Alice Jan 11 '21 at 21:28
Glad you figured it out! – D M Jan 11 '21 at 21:29
1

@Alice If you have the data in an application, there are far faster ways to do it, either bulk insert or table parameter. But if you are copying text into SQL like you say, then this is right. – Charlieface Jan 11 '21 at 22:21
Thanks! This worked for me. Adding single quotes instead of double quotes worked for me. – Sanushi Salgado Apr 27 '21 at 10:38

How to insert multiple rows in a table with single row?

1 Answers1