SQL Server - Creating a table with a row of strings

Question

I have a list of names I want to put into a temp/variable table but can not think of how to do it.

Here is a dummy list....

NAMES
McLovin
Deckard
Mufasa
DeLarge

This is a string I'll get sent to me which I need to wash against actual tables so I wanted to place this in a temp field but I just can not think of how to do it.

CREATE TABLE [#test] (
NAMES varchar (20));

INSERT INTO [#test] ([NAMES])
VALUES ('McLovin');

INSERT INTO [#test] ([NAMES])
VALUES ('Deckard');

The above is the only way I can think of doing it. I am new to SQL so am still learning the logic. Any guidance would be much appreciated.

Cheers.

Also, You could have searched for multiple insert. no need to post duplicate dummy questions. — Tedo G., Aug 03 '16 at 12:16
Possible duplicate of [Inserting multiple rows in a single SQL query?](http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query) — Andrey Korneyev, Aug 03 '16 at 12:17
If you are going to do it with straight sql instead of through an ETL tool, this is the way to go. I believe in SQL server you can do multiple values in your insert `INSERT INTO #test VALUES ('McLovin'), ('Deckard'), ('Mufasa');` Generally when I have a small list of values, I just drop them in excel and use string concatenation to build the inserts `="INSERT INTO [#test] VALUES ('" & A1 & "');"` then copy it down the rows. — JNevill, Aug 03 '16 at 12:17

score 2 · Answer 1 · edited May 23 '17 at 11:44

2

See This: Inserting multiple rows in a single SQL query?

INSERT INTO #test ([NAMES])
VALUES ('McLovin'), ('Deckard'), ('Mufasa'), ('DeLarge')

edited May 23 '17 at 11:44

Community

1
1

answered Aug 03 '16 at 12:15

Tedo G.

1,556
3
17
29

score 2 · Answer 2 · answered Aug 03 '16 at 12:17

I want to add to Tedo's answer that you might not need a temporary table at all. You can include these directly in a query:

select . . .
from (VALUES ('McLovin'), ('Deckard'), ('Mufasa'), ('DeLarge')) v(names)

Or use a CTE:

with names(name) as (
      select *
      from (VALUES ('McLovin'), ('Deckard'), ('Mufasa'), ('DeLarge'))
     )
select . . .

score 0 · Answer 3 · answered Aug 03 '16 at 12:18

0

Here is the fastest on the fly way:

SELECT CONVERT(VARCHAR(20), 'McLovin') NAMES
INTO #temp
UNION
SELECT 'Deckard'
UNION
SELECT 'Mufasa'
UNION
SELECT 'DeLarge'

No need to write create statements too.

answered Aug 03 '16 at 12:18

Chaos Legion

2,730
1
15
14

SQL Server - Creating a table with a row of strings

3 Answers3