The best way to do double insertion

Question

What is the best way to insert information into table A and use the index from table A to link to table B.

The “solution” I tried was to insert the information into table A (which has an automatically generated identifier), then select the last index and paste it into table B. This may not be very useful since the last index may change between inserts, since another user can create a new index in table A

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

Answer 1

If you use MSSQL, you can use SCOPE_IDENTITY to return the last identifier inserted in the current session. You can then use this to insert into table B.

This MSDN article provides a decent example of how to do this.

Answer 2

This is a sequence solution (for postgres), you have to do this in a stored procedure or in 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, a transaction is not important in order to travel on your own feet if you use the same connection for more than one insert.

For LAST_INSERT_ID (), the most recently generated identifier is stored in the server for each connection. It does not change by another client. It does not even change if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL, not 0). Using LAST_INSERT_ID () and AUTO_INCREMENT columns from multiple columns at the same time, clients are perfectly faithful. each client will receive the last inserted ID for the last statement that the client is running.

 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

Another option is to create a sequence and before inserting into a table, get the value of the sequence in a variable and use it to insert into both tables.

Answer 4

In ORACLE, use sequences to store 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 is best to use PACKAGES in Oracle to store the entire layer of SQL / Data manipulation by an application.

Answer 5

With IBM Informix Dynamic Server (IDS), this depends on the language you use to implement double insertion. If it is a server (SPL is the language of stored procedures), and if you use the SERIAL column, you use DBINFO ('sqlca.sqlerrd2') to represent the serial value added to table A when pasting into table B. If you are working on the client (ESQL / C, I4GL, JDBC, ODBC), you collect the serial number through the approved interface (sqlca.sqlerrd [1] in ESQL / C, sqlca.sqlerrd [2] in I4GL), and then transfer it back.

IDS also supports sequences, so you can use this technique.

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

Answer 6

If your tables have a UUID key, generate a UUID and use it in both inserts.

Answer 7

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

Answer 8

Another Access 2000+ answer (Jet 4.0) is to create a Jet 4.0 VIEW (in terms of Access: a SELECT query stored as a query object) with an INNER JOIN in the IDENTITY (Autonumber) column; join columns must be open in the SELECT clause and in the referenced table. Then the INSERT INTO value is VIEW for all NOT NULL columns that do not have DEFAULT .

The value of the IDENTITY column may be omitted, in which case the engine will automatically generate the value as usual or an explicit value provided and executed; if the value of the join column in another table (that is, without the IDENTITY column) is additionally provided, then it must be the same as the IDENTITY value, otherwise an error will occur; if the IDENTITY value is omitted, then any value specified for the connection column will be ignored. Please note that a FOREIGN KEY usually expected between such tables, but is not a prerequisite for this process to work.

Quick example (Query Mode ANSI-92 Jet 4.0 query mode 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 9

If you are using SQL Server 2005+, you can also use the OUTPUT clause, which displays data that has been updated, inserted, or deleted. This is pretty cool and accurate for the type you need. http://msdn.microsoft.com/en-us/library/ms177564.aspx

Answer 10

In SQL Server, you use the @@ IDENTITY field, and also carry INSERT into the 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 is best to use TSQL to store the @@IDENTITY value in a variable immediately after INSERT to avoid damaging the code for future code.

Using stored procedures is also recommended.

Answer 11

If Informix and JSP have a function that returns the Serial field of the table after insertion.

 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 is the link

(For some reason, on my work computer, he describes everything in Spanish, maybe because in Mexico)

Answer 12

Use a transaction to avoid this problem: "This may not be very useful since the last index may change between inserts because another user can create 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.