SQL SERVER 2008: Trying to insert multiple rows using 1 SQL Statement

Question

I need to insert 50+ rows into an SQL server 2008 and am getting a weird error. Please help!

Table Design:

• Name: mod_Facilities
• Columns
  • faclityID, Primary Key/Index
  • facilityName, nvarchar(4000)
  • facilityDescription, nvarchar(4000)
  • statusComment, nvarchar(4000)
  • isPublic, bit
  • isActive, bit
  • isDeleted, bit

Error:

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

Here's my SQL statement

INSERT INTO mod_Facilites (facilityName,facilityDescription,isActive,isDeleted)
VALUES
('Conference Room Lower','Conference Room Lower – 25, (AV ready for meetings and info sessions)','true','false'),
('Conference Room Upper','Conference Room Upper – 21, (AV ready for meetings and info sessions)','true','false'),
('Meeting Room A','Meeting Room A – (upper theatre set up capacity  40) ','true','false'),
('Meeting Room B','Meeting Room B – (AV ready classroom set up capacity  25) ','true','false'),
('Meeting Rooms A & B','Meeting Rooms A & B – (AV ready capacity 80)','true','false'),
('OP Resource Room','OP Resource Room','true','false'),
('Climbing Wall','Climbing Wall','true','false'),
('Bouldering Wall','Bouldering Wall','true','false'),
('Entire Climbing Area','Entire Climbing Area','true','false'),
('CPR/First Aid classroom','CPR/First Aid classroom','true','false'),
('Lobby Area','Lobby Area','true','false'),
('Studio 1','Studio 1 ','true','false'),
('Studio 2','Studio 2','true','false'),
('Studio 3','Studio 3','true','false'),
('Studio 4','Studio 4','true','false'),
('Mat Studio','Mat Studio','true','false');

Answer 1

You tagged this question as SQL Server 2008, but this is exactly the error you'd see if you tried this syntax on 2005 or earlier.

As an alternative, try:

INSERT INTO mod_Facilites 
    (facilityName,facilityDescription,isActive,isDeleted)
    SELECT 'Conference Room Lower','Conference Room Lower – 25, (AV ready for meetings and info sessions)','true','false' UNION ALL
    SELECT 'Conference Room Upper','Conference Room Upper – 21, (AV ready for meetings and info sessions)','true','false' UNION ALL
    SELECT 'Meeting Room A','Meeting Room A – (upper theatre set up capacity  40) ','true','false' UNION ALL
    SELECT 'Meeting Room B','Meeting Room B – (AV ready classroom set up capacity  25) ','true','false' UNION ALL
    SELECT 'Meeting Rooms A & B','Meeting Rooms A & B – (AV ready capacity 80)','true','false' UNION ALL
    SELECT 'OP Resource Room','OP Resource Room','true','false' UNION ALL
    SELECT 'Climbing Wall','Climbing Wall','true','false' UNION ALL
    SELECT 'Bouldering Wall','Bouldering Wall','true','false' UNION ALL
    SELECT 'Entire Climbing Area','Entire Climbing Area','true','false' UNION ALL
    SELECT 'CPR/First Aid classroom','CPR/First Aid classroom','true','false' UNION ALL
    SELECT 'Lobby Area','Lobby Area','true','false' UNION ALL
    SELECT 'Studio 1','Studio 1 ','true','false' UNION ALL
    SELECT 'Studio 2','Studio 2','true','false' UNION ALL
    SELECT 'Studio 3','Studio 3','true','false' UNION ALL
    SELECT 'Studio 4','Studio 4','true','false' UNION ALL
    SELECT 'Mat Studio','Mat Studio','true','false';