CODEWITHSUNDEEP
sqlt-sqlsql-server-ce
Zion
Zion

Reputation:

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

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?

Upvotes: 2

Views: 14059

Answers (7)

Troy Tudor
Troy Tudor

Reputation: 1

Granted, this is way past the posting date, but since I've not seen this answered elsewhere in my quick Google search, I thought I'd share how I solved this with SQL CE so others searching might find an answer.

-- Update existing record's value
UPDATE myTable
SET myValue = 'Hello World'
WHERE keyField = 'MyKey';

-- Insert new record if existing record doesn't exist`

INSERT INTO myTable (keyField, myValue)
SELECT I.keyField, I.myValue
FROM (
SELECT 'Hello World' AS myValue, 'MyKey' AS keyField
) I
LEFT JOIN myTable T ON I.keyField = T.keyField
WHERE T.keyField IS NULL;

Upvotes: 0

ChulioMartinez
ChulioMartinez

Reputation: 100

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.

Upvotes: 1

Brian Spencer
Brian Spencer

Reputation: 204

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

Upvotes: 0

Jared
Jared

Reputation: 7233

What about something like this:

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

Source

Upvotes: -2

DBAndrew
DBAndrew

Reputation: 6958

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

Upvotes: -2

Charles Bretana
Charles Bretana

Reputation: 146559

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

Upvotes: 1

cjk
cjk

Reputation: 46465

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

Upvotes: 4

Related Questions