问题:

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.

回答1:

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');

回答2:

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.