1nval1d.netlify.app

By: Maria Zakourdaev Updated: 2019-08-15 Comments Related: More >T-SQL

The contactid column has a default values provided by the uuidgeneratev4 function, therefore, whenever you insert new row without specifying the value for the contactid column, PostgreSQL will call the uuidgeneratev4 function to generate the value for it. Second, insert.

Problem

A few days ago, one of the developers asked me if that was possible to generate test data by performing multiple nested INSERT statements, each of them involving inserting new rows into several parent tables and in the same statement reusing the autogenerated primary keys for the foreign key columns in the child table. The developer was working with PostgreSql and so I tried to find a solution for both PostgreSql and SQL Server to learn the differences in the ANSI features implementation in these different database systems.

• PostgreSQL SERIAL example. It is important to note that the SERIAL does not implicitly create an index on the column or make the column as the primary key column. However, this can be done easily by specifying the PRIMARY KEY constraint for the SERIAL column.
• SERIAL data type allows you to automatically generate unique integer numbers (IDs, identity, auto-increment, sequence) for a column. Quick Example: - Define a table with SERIAL column (id starts at 1) CREATE TABLE teams ( id SERIAL UNIQUE, name VARCHAR(90) ); - Insert a row, ID will be automatically generated INSERT INTO teams (name) VALUES ('Tottenham Hotspur'); - Retrieve generated ID.

Solution

Disclaimers

1. The fastest data generation solution is as follows:
   1. Insert the required number of rows into each parent table
   2. Get the ids according to the data generation logic and use them to add rows to the child table

In this tip I will not be using the technique above, but try to do this all with just one statement.

On my laptop, I generated 100,000 rows using the below technique. In SQL Server it took 86 seconds compared to the 3 statements logic (like below) which took approximately 5 minutes.

1. insert into 1^st parent table + store output into the variable
2. insert into 2^st parent table + store output into the variable
3. insert into a child table

ANSI Solution

There is a great feature in the ANSI standards that can be used exactly for this challenge, using a common table expression or CTE. A CTE is a temporary named result set, created from a simple query defined within an execution scope of SELECT, INSERT, UPDATE or DELETE statement.

In PostgreSql, CTE implementation includes data modification query scope. But in SQL Server, the CTEs query definition must meet a view’s requirements which means we cannot modify data inside a CTE.

In this tip I will show you a single statement solution I came up with in PostgreSql and SQL Server and I would love to hear your comments on how you would solve this challenge.

PostgreSql Approach to Load Data into Parent and Child Tables at the Same Time

Before we get started, here is the syntax for creating the three tables.

In PostgreSql, the solution to this challenge is quite simple, we will update two tables in the CTE and use the generated ids as foreign key ids in the third table.

SQL Server Approach to Load Data into Parent and Child Tables at the Same Time

Before we get started, here is the syntax for creating the three tables.

As I mentioned earlier, in SQL Server a CTEs query definition must meet a view’s requirements which means we cannot modify data inside the CTE. Generations in the workplace key characteristics.

We can use INSERT..OUTPUT construction, but another limitation in SQL Server for capturing the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE or MERGE statement, a target table cannot participate on either side of a FOREIGN KEY constraint.

Since we are generating the test data and this is not a production system, we can temporary disable foreign keys as follows:

We still cannot have two layers of nested INSERTs, because it is not possible to have two OUTPUT INTO clauses in the same statement:

To overcome the issue, I have used INSERT EXEC construction, but in order to use both autogenerated keys in the OUTPUT statement, I need to have both of them in the inserted table. I have added a new column to the table salesperson for storing theid generated during product creation.

And here is my final statement:

We have succeeded to insert two separate rows into two tables, generate 2 ids and used them in the third insert all in one statement. Take into consideration that this solution required disabling referential integrity keys which is not suggested for production environments.

If you come up with another way to implement the above query, without disabling the keys and without an additional column – I would love to see it. The sims 4 serial & key generator. Please enter feedback in the comments section below.

Next Steps

About the author

Postgresql Multiple Primary Key