SQL CE Insert If Data Doesn't Exist

angus_young_acdc
angus_young_acdc used Ask the Experts™
on
Hello Experts,

I'm writing a C# application and using SQL Server Compact Edition as a local database, as I'm unable to write stored procedures for it (I'm assuming that's correct!) I need to somehow write a query that will insert data, but only if it doesn't currently exist in the table.

I have a query that goes:
string insert = "INSERT INTO myTable (Column1, Column2, Column3) VALUES (Data1, Data2, Data3)

I tried putting WHERE NOT EXISTS(SELECT * FROM myTable WHERE Column1 = Data1), but I get the following error:
There was an error parsing the query. [ Token line number = 1,Token line offset = 399,Token in error = WHERE ]

What is the correct way to only insert if the data doesn't currently exist?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011
Commented:
instead of the insert into (...) values(...)

format

 use

INSERT INTO myTable (Column1, Column2, Column3)
  select @var1,@var2,@var3
WHERE NOT EXISTS (SELECT * FROM myTable WHERE Column1 = @var1)

you don't need a from clause...
string SQL = @"
IF SELECT Column1 FROM myTable WHERE Column1 = Data1 IS NULL
BEGIN
INSERT INTO myTable (Column1, Column2, Column3) VALUES (Data1, Data2, Data3)
END";
Top Expert 2011
Commented:
see this for stored procedures in SQL Server CE

According to a number of people on the web, SPs aren't supported in ce, but they are incorrect - stored procedures can be used in sql server ce. As Jim Wilson explains in this blog http://blog.jwhedgehog.com/200402archive001.asp, the CommandType of StoredProcedure isn't supported in RDA so this won't work (hence why most think it doesn't work at all):
SqlCommand cmd = new SqlCommand();
cmd.CommandText = “MyStoredProc '02/23/2004' ”;
cmd.CommandType = CommandType.StoredProcedure;
However, the solution is to use the exec command like this: SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(); rda.Pull("authors", "exec MyStoredProc '02/23/2004' ", ...);

from According to a number of people on the web, SPs aren't supported in ce, but they are incorrect - stored procedures can be used in sql server ce. As Jim Wilson explains in this blog http://blog.jwhedgehog.com/200402archive001.asp, the CommandType of StoredProcedure isn't supported in RDA so this won't work (hence why most think it doesn't work at all):
SqlCommand cmd = new SqlCommand();
cmd.CommandText = “MyStoredProc '02/23/2004' ”;
cmd.CommandType = CommandType.StoredProcedure;
However, the solution is to use the exec command like this: SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(); rda.Pull("authors", "exec MyStoredProc '02/23/2004' ", ...);

from http://social.msdn.microsoft.com/forums/en-US/vssmartdevicesvbcs/thread/6bd28e2a-2a5a-4187-b253-dceab163ecc6/
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

AndyAinscowFreelance programmer / Consultant
Commented:
You could do it in two steps
1) SELECT * FROM  myTable WHERE Column1 = Data1  --> find out if there is any records returned
2) if none returned then use your insert to do the work.
Unfortunately I haven't been able to get any of these working, must be missing something (this is why I prefer stored procedures :-p).  I shall keep trying and get back to you guys

Andy, I could use that but I'm already opening several connections within this method (I have to read an access DB, then connect to SQL) so I would perfer to reduce the amount of DB polling code.
Top Expert 2011
Commented:
can you post what you ran for this format?

together with any error messages it raised , otherwise we are in the dark

INSERT INTO myTable (Column1, Column2, Column3)
  select @var1,@var2,@var3
WHERE NOT EXISTS (SELECT * FROM myTable WHERE Column1 = @var1)
AndyAinscowFreelance programmer / Consultant
Commented:
If you did want a two step proc then you should also be able to do it with only opening the connection once.
Get records back with the SELECT, if none returned then use an Addnew/Update on the returned recordset (instead of the INSERT using another connection).
I think you have written query like below:
INSERT INTO myTable (Column1, Column2, Column3) VALUES (Data1, Data2, Data3)
 WHERE NOT EXISTS(SELECT * FROM myTable WHERE Column1 = Data1)
The error here is, we can write WHERE clause for INSERT query. It is used in SELECT query only. So to check whether data exists or not, use IF condition to check whether data exists for Data1 and if not then insert data. Following is sample query:
IF NOT EXISTS(SELECT * FROM myTable WHERE Column1 = Data1)
BEGIN
    INSERT INTO myTable (Column1, Column2, Column3) VALUES (Data1, Data2, Data3)
END
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
I think you will have more joy with lowfatspreads approach... Just tested on a CE database and it is very picky....

karthitron is quite right about you current syntax, however would still do :

insert mytable (col1,col2,col3)
select 'this','that','the other things'
where not exists (select 1 from mytable where col1 = 'this')
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
The other way to handle it is to write your own "record exists" type function, we had these for each table and used for updates, inserts, deletes as a check before going anywhere near an add, change, delete etc...

            public static bool RecordExists(
                  object MY_ID
                  )
            {
                  bool CurrentRecordExists = false;
                  try
                  {
                        //Open a DataReader and check the record exists:
                        SqlCeDataReader dr = LoadReader(
                              MY_ID
                              );
                        if(dr.Read())
                        {
                              CurrentRecordExists = true;
                        }
                        dr.Close();
                  }
                  catch (SqlCeException exception)
                  {
                        ExceptionPublisher.PublishEx(exception, true);
                        throw exception;
                  }
                  catch (Exception exception)
                  {
                        ExceptionPublisher.PublishEx(exception, true);
                        throw exception;
                  }
                  return CurrentRecordExists;
            }
            
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
That's interesting :)

In SSMS in a query window connected to a SQL Compact database, just tried the queries as a quick test.

karthitron's query didnt work because of the IF
and
jeroenemans query didnt work because of the IF
and
AndyAinscow didnt write a query...

Would like to hear if you actually got the IF working in CE and the version.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial