Menu
INSERT - create new rows in a table. Synopsis WITH. The optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted (or updated. INSERT conforms to the SQL standard, except that the RETURNING clause is a PostgreSQL extension. 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. The PostgreSQL PRIMARY KEY is a column in a table which must contain a unique value which can be used to identify each and every row of a table uniquely. So it can be said that the PRIMARY KEY of a table is a combination of NOT NULL and UNIQUE constraint.
By: Maria Zakourdaev | Updated: 2019-08-15 | Comments | Related: More >T-SQL
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.
Solution
Disclaimers
- The fastest data generation solution is as follows:
- Insert the required number of rows into each parent table
- 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.
- insert into 1st parent table + store output into the variable
- insert into 2st parent table + store output into the variable
- insert into a child table
ANSI Solution
![Sql Insert Generate And Return Primary Key Postgresql Sql Insert Generate And Return Primary Key Postgresql](/uploads/1/2/6/3/126386810/392677966.gif)
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
Assetto-corsa-cd-key-generator. 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.
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.
Query execution failed: The target table 'dbo.salesorderheader' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement.
Since we are generating the test data and this is not a production system, we can temporary disable foreign keys as follows:
Sql Insert Generate And Return Primary Key Postgresql 10
We still cannot have two layers of nested INSERTs, because it is not possible to have two OUTPUT INTO clauses in the same statement: Cd key generator battle for middle earth 2.
SQL Error [10717] [S0001]: The OUTPUT INTO clause is not allowed when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE 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. Auto generated primary key mysql.
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. Please enter feedback in the comments section below.
Next Steps
![Sql Insert Generate And Return Primary Key Postgresql Sql Insert Generate And Return Primary Key Postgresql](/uploads/1/2/6/3/126386810/847585587.png)
Sql Insert Generate And Return Primary Key Postgresql Download
Last Updated: 2019-08-15About the author
Sql Insert Generate And Return Primary Key Postgresql File
Maria Zakourdaev has been working with SQL Server for more than 15 years. She is also managing other database technologies such as MySQL, Postgresql, Redis, RedShift, CouchBase and ElasticSearch.
View all my tips
View all my tips