Inserting multiple rows of data in single temp table

Question

Inserting multiple rows of data in single temp table

VegasL

Hall of Fame

Points: 3006
More actions
August 3, 2018 at 8:37 am

#414563

Hello,
I have this basic script. I get the following error when trying to insert values:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'values'.
I want to insert multiple rows of data into table columns.
create table ##Test ([Primary KW] varchar (100), [Secondary KW] varchar (100), [Teritary KW] varchar (100), [Quaternary KW] varchar (100))
insert into ##Test ([Primary KW],[Secondary KW], [Teritary KW], [Quaternary KW])
values ('red', '1','year old','car'), values ('','2','','truck'), values ('','3','','')
Not sure where the error is. Thx

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76996 More actions · Answer 1

VegasL - Friday, August 3, 2018 8:37 AM
Hello,
I have this basic script. I get the following error when trying to insert values:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'values'.
I want to insert multiple rows of data into table columns.
create table ##Test ([Primary KW] varchar (100), [Secondary KW] varchar (100), [Teritary KW] varchar (100), [Quaternary KW] varchar (100))
insert into ##Test ([Primary KW],[Secondary KW], [Teritary KW], [Quaternary KW])
values ('red', '1','year old','car'), values ('','2','','truck'), values ('','3','','')
Not sure where the error is. Thx

You only specify the VALUES keyword once.

create table ##Test ([Primary KW] varchar (100), [Secondary KW] varchar (100), [Teritary KW] varchar (100), [Quaternary KW] varchar (100)) insert into ##Test ([Primary KW],[Secondary KW], [Teritary KW], [Quaternary KW]) values ('red', '1','year old','car'), ('','2','','truck'), ('','3','','')

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 2

The error message was telling you exactly what is wrong... you weren't using the VALUES Table Valued Constructor correctly. 😉

create table ##Test ([Primary KW] varchar (100), [Secondary KW] varchar (100), [Teritary KW] varchar (100), [Quaternary KW] varchar (100))

insert into ##Test ([Primary KW],[Secondary KW], [Teritary KW], [Quaternary KW])

values ('red', '1','year old','car'),
('','2','','truck'),
('','3','','')

Do a Google search for "VALUES in sql server" to learn more.

You should also do a search on "Global Temporary Tables" and learn how they can destroy concurrency of code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VegasL Hall of Fame Points: 3006 More actions · Answer 3

Thanks for pointing me in right direction guys - Appreciate it.

I see the correct way to do it as:

create table ##Test ([Primary KW] varchar (100), [Secondary KW] varchar (100), [Teritary KW] varchar (100), [Quaternary KW] varchar (100))

insert into ##Test ([Primary KW],[Secondary KW], [Teritary KW], [Quaternary KW])

values ('red', '1','year old','car'), ('','2','','truck'), ('','3','','')

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 4

Heh... I didn't see Drew's post because I had opened this thread and let it sit for a while. So sorry for the double post with the same answer.