3

I keep getting this error for this very simple insert:

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

INSERT INTO dbo.Players (ID_Player, Last_Name, First_Name, Position )
VALUES
(039,   'Astacio '  ,   'Pedro' , ' P ' ),
(040,   'Atchison'  ,   'Scott' , ' P ' ),
(041,   'Ayala'     ,   'Benny' , ' OF' );
GO

What is wrong?

Robert Tritt
  • 31
  • 1
  • 1
  • 3
  • 1
    are you sure that your database model is 11 (SQL 2012)? – Luis LL Sep 22 '13 at 05:09
  • Yes, I am sure. If I just do one line at a time the insert works. When I try multiple lines I receive this error. – Robert Tritt Sep 22 '13 at 05:11
  • What does `select @@version` tell you? – Mikael Eriksson Sep 22 '13 at 05:35
  • Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) Dec 10 2010 10:56:29 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.1 (Build 7601: Service Pack 1) – Robert Tritt Sep 22 '13 at 05:46
  • form your @@version (2005) take a look here : http://stackoverflow.com/a/6354791/1699210, maybe you'll need to retag your question. – bummi Sep 22 '13 at 06:01
  • The table valued constructor was introduced in SQL Server 2008. You are using SQL Server 2005. – Mikael Eriksson Sep 22 '13 at 06:02
  • Thank you everyone for your help! Not sure how I am using 2005 when I purchased 2012 and the box in came in says 2012. – Robert Tritt Sep 22 '13 at 06:12
  • Microsoft SQL Server Management Studio 11.0.2100.60 Microsoft Analysis Services Client Tools 11.0.2218.0 Microsoft Data Access Components (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 9.10.9200.16686 Microsoft .NET Framework 4.0.30319.1008 Operating System 6.1.7601 – Robert Tritt Sep 22 '13 at 06:21
  • When I looked at the versions I have the above running - do know why it says I am running 2005 when I looked at @@versions? – Robert Tritt Sep 22 '13 at 06:22
  • SQL Server Management Studio is the client tool you use to query/administrate your database. – Mikael Eriksson Sep 22 '13 at 07:29
  • 2
    The version of the server instance is a different matter. You can have more than one instance installed on one computer and one might be 2005 and another might be 2012. Both can be administered by the 2012 version of SQL Server Management Studio. – Mikael Eriksson Sep 22 '13 at 07:32
  • If you only specify the server name in the connection you will connect to the "default" instance. If you want to connect to a named instance you connect to something that looks like this `servername/instancename`. – Mikael Eriksson Sep 22 '13 at 07:34
  • How do I run the 2012 instance? – Robert Tritt Sep 22 '13 at 07:38
  • @RobertTritt I don't think SQL Server 2005 support multi record insert like that. You have to write 3 insert statements. – Eric Aug 09 '18 at 16:53

1 Answers1

0

If you want to do it in one statement then use select/union:

INSERT INTO dbo.Players (ID_Player, Last_Name, First_Name, Position)
select 039,   'Astacio ',  'Pedro', ' P ' union
select 040,   'Atchison',  'Scott', ' P ' union
select 041,   'Ayala'   ,  'Benny', ' OF';
GO
Pixel Hunter
  • 92
  • 10
Sean
  • 1,416
  • 19
  • 51