sqlite INSERT INTO ... WHERE NOT EXISTS and get the id?

Question

is it possible to get the id even if I use INSERT INTO ... WHERE NOT EXISTS? how to modify it so it will bring me the id of the inserted record or the id of the already existed record? if possible at all!

INSERT INTO Timeline (name, ts)
SELECT @name, @ts
WHERE NOT EXISTS (SELECT 1 FROM Timeline WHERE name=@name AND ts = @ts);

i have a column called "id" with auto increment integer how I can get the value of the ID?

cheers

Solution

You are guaranteeing that (name,ts) is unique, so this should do the trick.

INSERT INTO Timeline (name, ts)
SELECT @name, @ts
WHERE NOT EXISTS (SELECT 1 FROM Timeline WHERE name=@name AND ts = @ts);

SELECT id, name, ts from TimeLine
WHERE name=@name AND ts = @ts

Comments

[Lamak]

With that statement, it will insert the new record if it doesn't already exists on your table. Its a perfectly valid statement

[Data-Base]

I know, my question on how to make it bring me the id or the value of the id column?

[Ben]

You want to know if a row was inserted or if a row was already present? Won't rowcount tell you?

[Data-Base]

can you explain the "output inserted.name " part! note sure if I understand it

[Data-Base]

it seems i was not clearer in explaining the issue, sorry for that, now I edited the question, hope it is clear now!

[Diego]

it will work the same way, I used name as an example. Try changing it to ID and you will see the new Ids inserted

[Diego]

sorry I don't know. I did not realize the sqlite tag. Was testing on sql server 2005. I apologize