SQL Compact - Error executing multiple insert statements

Question

SQL Compact - Error executing multiple insert statements

I'm using management studio to connect to my sql mobile/compact database.

I'm trying to insert some dummy data into some tables, for example:

INSERT INTO FlooringTypes (FlooringType) VALUES ('Carpet')  
INSERT INTO FlooringTypes (FlooringType) VALUES ('Smooth')

However it returns the error:

Major Error 0x80040E14, Minor Error 25501

If I run them seperately it works fine.

sql

sql-server

sql-server-ce

asked on Stack Overflow Jan 29, 2010 by

Peter C • edited Jan 29, 2010 by

OMG Ponies

4 Answers

Put GO between them. I think SQL CE doesn't handle batches.

answered on Stack Overflow Jan 29, 2010 by

Rob Farley

The first will work by adding a semi colon after each line (excluding the last line).

INSERT INTO FlooringTypes (FlooringType) VALUES ('Carpet');   
INSERT INTO FlooringTypes (FlooringType) VALUES ('Smooth')

answered on Stack Overflow Mar 22, 2012 by

John Doherty • edited Mar 22, 2012 by

DaveShaw

You could also consider using one single statement, and seperating the individual values with commas. This works in regular SQL Server. I'm not sure if it also works on Compact, as I don't have that installed, but I see no reason why it shouldn't:

INSERT INTO FlooringTypes 
   (FlooringType)
VALUES
   ('Carpet')
   , ('Smooth')

answered on Stack Overflow Oct 27, 2012 by

SchmitzIT

USE COMMA THAT IS A SOLUTION FOR ABOVE ERROR

INSERT INTO FlooringTypes (FlooringType) VALUES ('Carpet');
INSERT INTO FlooringTypes (FlooringType) VALUES ('Smooth')

answered on Stack Overflow Apr 6, 2015 by

Rajesh ShanmugaSundaram

User contributions licensed under CC BY-SA 3.0