Incorrect syntax near ','

Question

INSERT INTO [Temp].[dbo].[Student]
    ([Fname], [Lname], [Gender])
    VALUES 
    (N'Aname', N'Alname', N'Male')
    GO

This codes workes fine but when I try to add multiple values it gives me an error

Error: Incorrect syntax near ','.

USE TEMP
GO

INSERT INTO [Temp].[dbo].[Student]
([Fname], [Lname], [Gender])
VALUES 
(N'Aname', N'Alname', N'Male'),
(N'Bname', N'Blname', N'Male')
GO

Solution 1

In order to use the multi-row VALUES(),() syntax, you need to be running SQL Server 2008 (or newer).

Since you are running SQL Server 2005, you need to run separate insert statements, use UNION/UNION ALL, or upgrade the instance (which is separate from Management Studio, which is just a client tool you use to connect to instances running any number of versions of SQL Server).

Solution 2

You can do it this way:

insert into [Temp].[dbo].[Student]
select 'Aname', 'Alname', 'AMale'
union all
select 'Bname', 'BAlname', 'BMale'

etc etc

Thanks

Paul.