CODEWITHSUNDEEP
sqlsql-server-2008
Snow_Mac
Snow_Mac

Reputation: 5797

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

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

Table Design:

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');

Upvotes: 2

Views: 6155

Answers (4)

Ken White
Ken White

Reputation: 125748

The easiest way to deal with this is to change the VALUES into SELECTs and UNION them:

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

... etc

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135171

Are you running in sql 2008 compatibility mode?

Does this return 100 or less than 100? If it is less than 100 then you are not running in SQL 2008 compatibility level

SELECT compatibility_level 
FROM sys.databases
WHERE database_id = DB_ID()

Upvotes: 5

Rich Andrews
Rich Andrews

Reputation: 4188

Is there any reason it has to be one statement? I have never seen this kind of use of an INSERT statement before. I would either use multiple statements or a BULK INSERT or SSIS.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135918

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';

Upvotes: 5

Related Questions