1

This is probably something really trivial I'm missing, but I can't seem to figure out why it's not working:

Basically, this works:

DECLARE @names TABLE (name NVARCHAR(100));

INSERT INTO @names
VALUES ('John');

but this does not:

DECLARE @names TABLE (name NVARCHAR(100));

INSERT INTO @names
VALUES ('John'), ('Jane');

I'm getting this error:

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

Why wouldn't this work? I've done this thousands of times with SSMS 2008.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Joseph Nields
  • 5,527
  • 2
  • 32
  • 48
  • what version of sql server are you on ? – M.Ali Sep 30 '15 at 20:51
  • It works for me. Nevermind the version of SSMS, what's the compatibility mode of your database? – Lamak Sep 30 '15 at 20:52
  • 2
    SSMS maybe 2014 but what is the sql server version , execute `SELECT @@VERSION` see what it returns – M.Ali Sep 30 '15 at 20:52
  • @M.Ali ahhhh OK this is 2005. I'm guessing that's the problem – Joseph Nields Sep 30 '15 at 20:54
  • 1
    Well this will not work in sql server 2005 , the insert construct you are trying to use was introduces in Sql server 2008 . anything older you will need to use `INSERT INTO TableName VALUES ('Value')` for each line you are inserting – M.Ali Sep 30 '15 at 20:55
  • Read this. http://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part It has an entry for 2005 as well – Amir Pelled Oct 01 '15 at 12:55

2 Answers2

3

SQL Server Table Value Constructor (Transact-SQL) was introduces in SQL Server 2008.

SQL Server 2008 and Later

DECLARE @names TABLE (name NVARCHAR(100));
INSERT INTO @names
VALUES
    ('John'),
    ('Jane');

SQL Server 2000 and Later

Any older version you will need to use single row insert at a time 

DECLARE @names TABLE (name NVARCHAR(100));
INSERT INTO @names VALUES('John');
INSERT INTO @names VALUES('Jane');
M.Ali
  • 67,945
  • 13
  • 101
  • 127
0

It's not a matter of what SSMS supports -- SSMS is just sending the query you've entered to SQL Server and letting SQL Server decide whether the syntax is valid. The issue is that not every version (e.g. SQL Server 2005) of SQL Server supports this comma-delimited syntax for insert statements.

Steven Green
  • 3,387
  • 14
  • 17