Insert Multiple Rows Into Temp Table With SQL Server 2012 [duplicate]

10

This question already has an answer here:

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
KyleMit

출처 sql sql-server-2012 bulkinsert
  • Yes, SQL Server 2012 supports multiple inserts - makes me wonder if you have Mgmt Studio 2012, but you're really connected to a 2005 instance - what version do you get from SELECT @@VERSION ?? - marc_s
  • That's exactly it. I'm connected to SQL Server 2005 - KyleMit

2 답변

8

Yes, SQL Server 2012 supports multiple inserts - that feature was introduced in SQL Server 2008.

That makes me wonder if you have Management Studio 2012, but you're really connected to a SQL Server 2005 instance ...

What version of the SQL Server engine do you get from SELECT @@VERSION ??

8

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: enter image description here

  • Now I wonder why it isn't working in SSMS. I only put in on fiddle so other people could more easily replicate the issue. - KyleMit
  • See my update. The only thing I can think of is that you are connected to a 2005 instance. - Sebastian Meine

Linked

Related

Latest