Inserting multiple rows of data in single temp table
-
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
- VegasL - Friday, August 3, 2018 8:37 AM
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 -
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) -
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','','') -
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.
--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) - Jeff Moden - Friday, August 3, 2018 9:48 AM
But you had additional information about the global temp tables that I had missed.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply