Best way to make double insert

Question

What's the best way of inserting information in table A and using the index from table A to relate to table B.

The "solution" I tried is inserting the info in table A (which has a automatically generated ID), then, select the last index and insert it in table B. This may not be very useful, as the last index may change between the inserts because another user could generate a new index in table A

I have had this problem with various DBMS postgreSQL, Informix, MySQL and MSSQL (thanks to lomaxx for the answer)

Answer 1

if you're using MSSQL you could use SCOPE_IDENTITY to return the last id inserted in your current session. You can then use that to insert into table B.

This article from MSDN gives a decent example on how to do it.

Answer 2

This is the sequence solution (for postgres), you'd have to do it in a stored procedure or on your application code, of course.

postgres=# create table foo(id serial primary key, text varchar);
NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE

postgres=# create table bar(id int references foo, text varchar);
CREATE TABLE
postgres=# select nextval('foo_id_seq');
 nextval
---------
       1
(1 row)

postgres=# insert into foo values (1,'a'); insert into bar values(1,'b');
INSERT 0 1
INSERT 0 1

For MySQL, the transaction is important not to trip on your own feet in case you're using the same connection for more than one insert.

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed.

mysql> create table foo(id int primary key auto_increment, text varchar(10)) Engine=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> create table bar(id int references foo, text varchar(10)) Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo(text) values ('x');
Query OK, 1 row affected (0.00 sec)

mysql> insert into bar values (last_insert_id(),'y');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.04 sec)

Answer 3

The other option is to create a sequence and before insert into table get the sequence value in a variable and use this to insert into both tables.

Answer 4

With IBM Informix Dynamic Server (IDS), it depends on the language that you are using to implement the double-insert. If it is the server (SPL - stored procedure language), and if you are using a SERIAL column, then you use DBINFO('sqlca.sqlerrd2') to represent the serial value added to Table A when inserting to Table B. If you are working in a client (ESQL/C, I4GL, JDBC, ODBC), you collect the serial via the approved interface (sqlca.sqlerrd[1] in ESQL/C, sqlca.sqlerrd[2] in I4GL) and then transfer it back again.

IDS also supports sequences, so you can use that technique instead.

IDS 11.50 supports SERIAL8 and BIGSERIAL as well as SERIAL (a 4-byte integer); the detailed interfaces are slightly different for each of these, but the basic principle is the same.

Answer 5

If your tables are UUID-keyed, generate UUID and use it in both inserts.

Answer 6

The Access 2000+ (Jet 4.0) answer is described in the Microsoft Knowledge Base. Basically, you can use SELECT @@Identity to retrieve the value of the auto-increment field that is generated on your connection.

Answer 7

Another Access 2000+ (Jet 4.0) answer is to create a Jet 4.0 VIEW (in Access terms: a SELECT query saved as a Query object) with an INNER JOIN on the IDENTITY (Autonumber) column; the joining columns must be exposed in the SELECT clause and the referenced table . Then INSERT INTO the VIEW supplying values for all the NOT NULL columns that have no DEFAULT.

The IDENTITY column value may either be omitted, in which case the engine will auto-generating the value as usual, or an explicit value supplied and honoured; if the value of the joining column in the other table (the one without the IDENTITY column) is additionally supplied then it must be the same as the IDENTITY value otherwise an error will occur; if the IDENTITY value is omitted then any value supplied for the joining column will be ignored. Note that a FOREIGN KEY would normally be expected between such tables but is not a prerequisite for this process to work.

Quick example (ANSI-92 Query Mode Jet 4.0 syntax):

CREATE TABLE Table1 
(
   key_col INTEGER IDENTITY NOT NULL PRIMARY KEY, 
   data_col_1 INTEGER NOT NULL
)
;
CREATE TABLE Table2
(
   key_col INTEGER NOT NULL, 
   data_col_2 INTEGER NOT NULL, 
   PRIMARY KEY (key_col, data_col_2)
)
;
CREATE VIEW View1
AS 
SELECT T1.key_col AS key_col_1, T2.key_col AS key_col_2, 
       T1.data_col_1, T2.data_col_2
  FROM Table2 AS T2
       INNER JOIN Table1 AS T1
          ON T1.key_col = T2.key_col
;
INSERT INTO View1 (data_col_1, data_col_2) 
VALUES (1, 2)
;

Answer 8

if you're using sql server 2005+ you can also use the OUTPUT clause which outputs the data that has been update, inserted or deleted. It's pretty cool and exactly for the type of stuff you need it for. http://msdn.microsoft.com/en-us/library/ms177564.aspx

Answer 9

In SQL Server you use the @@IDENTITY field, and also wrap the INSERT's in a transaction.

DEFINE ... etc etc 

BEGIN TRANSACTION

INSERT INTO table1 ( value1 ) VALUES ( @p_value1 )
SET @pk_table1 = @@IDENTITY

INSERT INTO table2 ( pk_table1, value2 ) VALUES ( @pk_table1, @p_value2 )

COMMIT

It's best practice in TSQL to store the @@IDENTITY value in a variable immediatly after the INSERT to avoid the value being corrupted by future maintenance code.

It's also best practice to use stored procedures.

Answer 10

In ORACLE, use sequences to keep PK values, and use the RETURNING clause

INSERT INTO table1 ( pk_table1, value1 ) 
   VALUES ( table1_seq.NEXTVAL, p_value1 ) RETURNING pk_table1 INTO l_table1_id;

INSERT INTO table2 ( pk_table2, pk_table1, value2 ) 
  VALUES ( table2_seq.NEXTVAL, l_table1_id, p_value2 );

It's best practice to use PACKAGES in Oracle to store all the SQL / Data manipulation layer of the appilcation.

Answer 11

If its in Informix and JSP, there is a function that returns the Serial field of a table after the insert.

import com.informix.jdbc.*;

cmd = "insert into serialTable(i) values (100)";
stmt.executeUpdate(cmd);
System.out.println(cmd+"...okay");
int serialValue = ((IfmxStatement)stmt).getSerial();
System.out.println("serial value: " + serialValue);

Here's the Link

(For some reason in my work computer it describes everything in spanish, maybe because in in Mexico)

Answer 12

Use a transaction to avoid this problem: "This may not be very useful, as the last index may change between the inserts because another user could generate a new index in table A."

And, in PostgreSQL, you can use 'nextval' and 'currval' to accomplish what you want to do:

BEGIN;

INSERT INTO products (prod_id, prod_name, description) VALUES (
    nextval('products_prod_id_seq')
    , 'a product'
    , 'a product description'
);

INSERT INTO prices (price_id, prod_id, price) VALUES (
    nextval('prices_price_id_seq')
    , currval('products_prod_id_seq')
    , 0.99
);

COMMIT;

Let me know if you need a DDL snippet as well.