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

For those unfamiliar, this is valid syntax in SQL Server 2008. But for previous versions of SQL Server, you have to use a different approach. — DOK
– DOK, Commented Mar 9, 2011 at 17:50
any reason you are passing varchars for isActive and isDeleted? — Abe Miessler
– Abe Miessler, Commented Mar 9, 2011 at 17:51
What if you generally try ('Mat Studio','Mat Studio', true, false) (no single quotes around your BIT values) - does that change anything?? — marc_s
– marc_s, Commented Mar 9, 2011 at 18:03
Make sure the tool used to run this is a 2008 tool. I've run into behavior like this when developing against SSMS 2008 but having the resulting script run with sqlcmd for 2005. — Enull
– Enull, Commented Mar 9, 2011 at 20:39

Joe Stefanelli · Accepted Answer · 2011-03-09 17:45:56Z

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

While this may well address the problem in the OP. I would be more concerned with why MSSQL 2008 is not acting like MSSQL 2008.

SQLMenace · Accepted Answer · 2011-03-09 17:53:37Z

5

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()

edited Mar 9, 2011 at 17:53

answered Mar 9, 2011 at 17:46

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Comments

Rich Andrews · Accepted Answer · 2011-03-09 17:49:44Z

0

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.

answered Mar 9, 2011 at 17:49

Rich Andrews

4,1883 gold badges38 silver badges48 bronze badges

2 Comments

Snow_Mac Over a year ago

Do you have an example of a bulk insert?

Rich Andrews Over a year ago

link to MSDN added - it depends on the source of your data as to whether it's best to BULK INSERT or SSIS but basically BULK INSERT will insert the contents of file into a table. SSIS can stick anything, anywhere :)

Ken White · Accepted Answer · 2011-03-11 00:18:00Z

0

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

edited Mar 11, 2011 at 0:18

answered Mar 9, 2011 at 17:50

Ken White

126k15 gold badges237 silver badges476 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related