Insert Multiple Rows Into Temp Table With SQL Server 2012

Question

These StackOverflow questions here, here, and here all say the same thing, but I can't get it to run in SSMS or SQLFiddle

CREATE TABLE #Names
  ( 
    Name1 VARCHAR(100),
    Name2 VARCHAR(100)
  ) 

INSERT INTO #Names
  (Name1, Name2)
VALUES
  ('Matt', 'Matthew'),
  ('Matt', 'Marshal'),
  ('Matt', 'Mattison')

When I execute this is SSMS, the insert fails with the following message on the first line after VALUES

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

This Fiddle runs without the # sign, and the schema executes successfully when the table name is #Names, but I get the following message when I try to select * from the table

Invalid object name '#NAMES'.: SELECT * FROM #NAMES

Does SQL Server 2012 support multiple inserts?

---

Update: Apparently accessing a 2005 server on SSMS 2012....

SELECT @@VERSION --Returns: Microsoft SQL Server 2005

Answer 1

When using SQLFiddle, make sure that the separator is set to GO. Also the schema build script is executed in a different connection from the run script, so a temp table created in the one is not visible in the other. This fiddle shows that your code is valid and working in SQL 2012:

SQL Fiddle

MS SQL Server 2012 Schema Setup:

Query 1:

CREATE TABLE #Names
  ( 
    Name1 VARCHAR(100),
    Name2 VARCHAR(100)
  ) 

INSERT INTO #Names
  (Name1, Name2)
VALUES
  ('Matt', 'Matthew'),
  ('Matt', 'Marshal'),
  ('Matt', 'Mattison')

SELECT * FROM #NAMES

Results:

| NAME1 |    NAME2 |
--------------------
|  Matt |  Matthew |
|  Matt |  Marshal |
|  Matt | Mattison |

---

Here a SSMS 2012 screenshot: