What's the best way to extract data from a database with an update if needed?

Question

I need to insert a data into a DB using query like this one:

INSERT INTO Table1 
  (aID) 
 VALUES 
  (select aID from Table2 where aID="aID"))

The problem is that if Table2 doesn’t have the aID I need to update it with it and extract the newly created ID. Is there a SQL command that automatically does it?

Answer 1

Your question is kinda all over the map... The problem is that if Table2 doesn’t have the aID I need to update it with it - what do mean by this?

If it doesn't exist you got to insert it ...rght?

Having said that, you need to write a stored-procedure to achieve your objective...and also set "aID"...to be an auto-incremental,seeded Identifier column type

Let me know if you need elaboration

Answer 2

Presumably, you have more columns to add in the real query, as it doesn't make any sense at the moment, you just have one column in the whole thing.

Presuming that you want to insert some other columns from table 2 into table 1 and then update into table 2 the new id that comes from the table 1, there's no one command that will do this for you.

Something like (in SQL Server) DECLARE @newId int insert into table1 (.. columns..) values select (..columns..) from Table2 where aID="aID") select @newId=scope_identity() update table2 set foreign_key_column_to_table_1=@newId where aID="aID"