CODEWITHSUNDEEP
sqlsql-server
rickyProgrammer
rickyProgrammer

Reputation: 1167

Temporary Table creation Issue in SQL Server 2005

When I create and insert values into a temporary table, I got this error in a SQL Server 2005 database.

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ','

But when I run the exact code in SQL Server 2008 and higher versions, I can successfully create the rows.

Is there a limitation in OLDER VERSION? or just some setting issues?

These are my codes as example

create table #CustCodesMapping 
(
     NewCustCode varchar(30), 
     OldCustCode varchar(30) collate database_default
)

insert into #CustCodesMapping
values ('11111', 'aaaaa'), ('22222', 'bbbbb'), ('33333', 'ccccc')

but when I only add one value, it works

create table #CustCodesMapping 
(
     NewCustCode varchar(30), 
     OldCustCode varchar(30) collate database_default
)

insert into #CustCodesMapping
values ('11111', 'aaaaa')

Upvotes: 0

Views: 37

Answers (1)

Thom A
Thom A

Reputation: 95544

To reiterate my comment:

Why are you still using SQL Server 2005, it's been completely unsupported for 5~ years now; long past time to upgrade.

As for the problem, again, it's because you're using such an outdated version of SQL Server. VALUES allowing multiple rows was introduced in SQL Server 2008 (which is also, completely unsupported). You have to use the really old method in 2005:

CREATE TABLE #CustCodesMapping (NewCustCode varchar(30),
                                OldCustCode varchar(30) COLLATE DATABASE_DEFAULT);
INSERT INTO #CustCodesMapping (NewCustCode,OldCustCode)
SELECT '11111',
       'aaaaa'
UNION ALL
SELECT '22222',
       'bbbbb'
UNION ALL
SELECT '33333',
       'ccccc';

But the real solution is upgrade to a supported version. Unfortuantely, as you're using 2005, that means 2 upgrades. 2005 -> 2012 -> supported version.

Upvotes: 2

Related Questions