SQL CE Insert If Data Doesn't Exist
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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')
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')
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.Publish
throw exception;
}
catch (Exception exception)
{
ExceptionPublisher.Publish
throw exception;
}
return CurrentRecordExists;
}
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.Publish
throw exception;
}
catch (Exception exception)
{
ExceptionPublisher.Publish
throw exception;
}
return CurrentRecordExists;
}
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.
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.
ASKER
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.