Using SQl Server CE; Possible to Insert Only If Not Exists?

Question

I'm trying to verify a simple 1 field table to determine if a record exists before inserting a duplicate.

if not exists (select * from url where url = ...)
insert into url...

Can someone Help?

Answer 1

Just reverse it and add the condition as a where clause predicate

 Insert Into Table .... 
  Where Not Exists 
 (Select * From table where ...)

... But your basic problem sounds like it might be better solved by putting a alternate key (unique) constraint on the insert table, referencing the url column (I assume Sql CE does Referential Integrity (RI) constraints?)

Answer 2

Your code example will run in the full version of SQL, or you could rearrange to the following:

insert into url
select 'myvalue'
where not exists (select * from url where url = 'myvalue')

Answer 3

You are on the right path with IF NOT EXISTS. It is better to use IF NOT EXISTS() or IF EXISTS() than a Sub Query because SQL Server will stop scanning rows in the table when it finds the first instance that matches the EXISTS() condition your looking for. With a Sub Query written in the examples above it will scan the whole table.

A Classic example is the Insert or Update aka the SAVE.

IF EXISTS(SELECT * FROM Table_A WHERE Column_1 = @Parameter)
BEGIN
    --Update Statement here.
END
ELSE
BEGIN
    --Insert Statement here.
END

Answer 4

What about something like this:

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

Source

Answer 5

Use an Outer Join

Insert into X(...) select blah, blah, blah from table t left outer join X on t.id=x.id where x.id is null

Answer 6

You might want to read this thread. performing-insert-or-update-upsert-on-sql-server-compact-edition

In a nutshell a sqlce specific solution (using SqlCeResultSet) will provide the maximum performance.