INSERT INTO for SQL Server, Oracle and PostgreSQL
As a second part for this tutorial series on the behavior differences of SQL statements in SQL Server, Oracle and PostgreSQL, we will look at the differences of the INSERT statement.
In the first part of this series we look at UPDATE statements and this article we will look at INSERT statements which are used to add a new record or rows of data to the database either in SQL scripts or via stored procedures.
INSERT statements can be pretty straightforward and easy to use, but there are particular cases that are treated differently in each RDBMS. Some of these differences are the syntax, ways to return values of inserted data, using CTEs or creating a new table with an INSERT statement. We will look at several examples of these items.
For test purposes I am using the github freely downloadable database Chinook because it is available in multiple RDBMS formats and can be download from this link. It is a simulation of a digital media store with sample data and all you have to do is download the version you need and run the scripts to create the data structure and all the inserts for the data.
SQL INSERT Basics
Let's start with the basic syntax of the INSERT statement to add a single row in a table which is the same in all the three RDBMS where we specify the column names then numeric, varchar or date values which are explicit values that are comma separated in parentheses as shown here:
insert into Tablename (Col1,Col2...Coln) values (Val1, Val2...Valn)
Another basic example is to insert data where we specify the column names and a SELECT query instead of specifying the values clause with a list of values the data is derived from the source table in the following statement:
insert into Tablename (Col1,Col2,Col3) select Val1, Val2, Val3 from Tablename2 where Val2 IS NOT NULL
INSERT into Temp Tables
There is also the ability to insert data into temporary objects that can be used for additional processing.
In SQL Server you can insert data into a table variable or a temp table. Note, table variables only exist in SQL Server.
-- temp table example create table #table_temp (Title nvarchar(160), ArtistName nvarchar(120)) insert into #table_temp select AlbumId, Name from album inner join artist on album.artistid=artist.ArtistId -- table variable example declare @table_variable as table(Title nvarchar(160), ArtistName nvarchar(120)) insert into @table_variable select AlbumId, Name from album inner join artist on album.artistid=artist.ArtistId -- insert into a new temp table select AlbumId, Name into #temp_table from album inner join artist on album.artistid=artist.ArtistId
Note, you can create the table first and then do the insert, but the problem is that you do not get the advantage of parallel inserts for very large operations, that's to say the ability of inserting into a temp table in parallel and if you are inserting a lot of rows for staging purposes you can have performance problems. That unless you are using the hint TABLELOCK. There is a very nice post that goes deep on this issue.
In PostgreSQL there are not table variables, but there are temp tables. In PostgreSQL you can insert into a temp table like this:
create temp table temp_table (Title character varying(160), ArtistName character varying(120)); insert into temp_table select "AlbumId", "Name" from "Album" inner join "Artist" on "Album"."ArtistId"="Artist"."ArtistId";
There is also a syntax similar to the SELECT...INTO of SQL Server and it uses the CREATE AS:
create temp table temp_table2 as select "AlbumId", "Name" from "Album" inner join "Artist" on "Album"."ArtistId"="Artist"."ArtistId";
Using OUTPUT with INSERT
At this point let's introduce some other features of the basic INSERT statement, first the possibility to return the values inserted once the insert has been completed.
declare @table_output as table(Artistid int, Name nvarchar(120)) insert into Artist output INSERTED.ArtistId, INSERTED.Name INTO @table_output values(276, 'Greta Van Fleet') select * from @table_output
In Oracle we have a similar syntax but with the keyword RETURNING instead of OUTPUT, note that like in SQL Server you have to assign it to variables:
SET SERVEROUTPUT ON declare my_ArtistId chinook.artist.artistid%TYPE; my_name chinook.artist.name%TYPE; begin insert into chinook.Artist values (276, 'Greta Van Fleet') returning ArtistId, Name into my_artistid, my_name; dbms_output.put_line(my_artistid ||' '||my_name); end;
Please note that here we have to do a small trick in order to display the variables directly in the Script Output screen of Oracle Developer, with the instruction dbms_output.put_line we have to use the SET SERVEROUTPUT ON.
The syntax in PostgreSQL is similar again using the keyword RETURNING we can directly display the values inserted:
insert into "Artist" values (276, 'Greta Van Fleet') returning "Artist"."ArtistId", "Artist"."Name";
It is possible also to assign the returning values to variables with this syntax:
do $$ declare My_Artistid int; declare My_Name character varying(120); begin insert into "Artist" values (276, 'Greta Van Fleet') returning "Artist"."ArtistId", "Artist"."Name" into My_Artistid, My_Name; raise notice 'Artistid: %', My_Artistid; raise notice 'Name: %', My_Name; end $$;
INSERT Into New Table
Then what about creating a table directly with an insert statement? This is possible in all three RDBMS, but obviously with different syntax.
We can create the new table as follows with a SELECT statement by specifying the list of columns from an existing table:
select AlbumId, Name into Artist_album from album inner join artist on album.artistid=artist.ArtistId
In Oracle the syntax is different and we use the CREATE TABLE ... AS which includes a SELECT statement:
create table chinook.Artist_album as select AlbumId, Name from chinook.album inner join chinook.artist on album.artistid=artist.ArtistId;
In PostgreSQL the syntax is similar to Oracle using CREATE TABLE ... AS which includes a SELECT statement:
create table Artist_album as select "AlbumId", "Name" from "Album" inner join "Artist" on "Album"."ArtistId"="Artist"."ArtistId";
Using CTE for INSERT
Let's see now something a little bit more "juicy" like using a Common Table Expression (AKA CTE or WITH query) for defining the rows to be inserted.
So we will reuse the CTE from the UPDATE tip and imagining that we need to store in a table the discounts calculated and the discount percentage instead of updating the Invoice table, in SQL Server the syntax is like:
create table Discount_Table(Customerid int, Genretotal numeric(10,2), DiscountPercentage tinyint) ; with discount as (select Invoice.CustomerId, sum(invoiceline.UnitPrice*Quantity) as genretotal, 8 as DiscountPercentage from invoice inner join InvoiceLine on Invoice.InvoiceId=InvoiceLine.InvoiceId inner join Track on Track.TrackId=InvoiceLine.TrackId inner join customer on customer.CustomerId=Invoice.CustomerId where country='Austria' and GenreId in(1,3) group by Invoice.CustomerId having sum(invoiceline.UnitPrice*Quantity)>20) insert into Discount_Table select Customerid, genretotal,DiscountPercentage from discount
And just to check what we have in this new table:
select * from Discount_Table
In Oracle syntax is similar, but a little bit different on the position of the CTE:
create table Chinook.Discount_Table (Customerid int, Genretotal numeric (10,2), DiscountPercentage numeric (2,0)); insert into Chinook.Discount_Table with discount as (select chinook.Invoice.CustomerId, sum(chinook.invoiceline.UnitPrice*Quantity) as genretotal, 8 as DiscountPercentage from chinook.invoice inner join chinook.InvoiceLine on chinook.Invoice.InvoiceId=chinook.InvoiceLine.InvoiceId inner join chinook.Track on chinook.Track.TrackId=chinook.InvoiceLine.TrackId inner join chinook.customer on chinook.customer.CustomerId=chinook.Invoice.CustomerId where country='Austria' and GenreId in (1,3) group by chinook.Invoice.CustomerId having sum(invoiceline.UnitPrice*Quantity)>20) select Customerid, genretotal,DiscountPercentage from discount;
As you can see in Oracle the CTE must be after the INSERT INTO statement, while in SQL Server CTE must always be the first part of the query. Let's check the data in the table, but first don't forget to Commit as in Oracle Autocommit is not active by default as in SQL Server and PostgreSQL:
commit; select * from DISCOUNT_TABLE;
Now it's the turn of PostgreSQL, here we see again a syntax like the one of SQL Server:
create table Discount_Table (Customerid int, Genretotal numeric (10,2), DiscountPercentage numeric(2,0)) ; with discount as (select "Invoice"."CustomerId", sum("InvoiceLine"."UnitPrice"*"Quantity") as genretotal, 8 as DiscountPercentage from "Invoice" inner join "InvoiceLine" on "Invoice"."InvoiceId"="InvoiceLine"."InvoiceId" inner join "Track" on "Track"."TrackId"="InvoiceLine"."TrackId" inner join "Customer" on "Customer"."CustomerId"="Invoice"."CustomerId" where "Country"='Austria' and "GenreId" in (1,3) group by "Invoice"."CustomerId" having sum("InvoiceLine"."UnitPrice"*"Quantity")>20) Insert into Discount_Table select "CustomerId", genretotal,DiscountPercentage from discount
Interesting to note that PostgreSQL is a little bit different with the output of a successful INSERT as it returns not only the number of rows inserted but also the OID, with the format:
INSERT oid count
As per PostgreSQL official documentation: "The count is the number of rows inserted or updated. If count is exactly one, and the target table has OIDs, then oid is the OID assigned to the inserted row."
Again we check with a select statement run against the new of the table we have created:
select * from Discount_Table
INSERT Conflicts in PostgreSQL
One last singularity that I would like to point out in PostgreSQL is the possibility to perform different actions when a constraint is violated by the insert, using the optional clause ON CONFLICT.
Thus it is possible to easily perform an UPSERT with a simpler and cleaner syntax, here is an example:
insert into "Album" ("AlbumId","Title", "ArtistId") values (348,'Safari Song',276) on conflict ("AlbumId") do update SET "Title" = EXCLUDED."Title" || ' (formerly ' || "Album"."Title" || ')'
First we have inserted a new row, no constraint violated so it performs a normal INSERT, now we repeat with a new title:
insert into "Album" ("AlbumId","Title", "ArtistId") values (348,'When the curtain falls',276) on conflict ("AlbumId") do update SET "Title" = EXCLUDED."Title" || ' (formerly ' || "Album"."Title" || ')'
It seems that it has repeated the insert even if it should have violated the PK constraint on column AlbumID, but if we check the data in the table:
select * from "Album" where "AlbumId"=348
We see that it has performed an UPDATE on the Title column as specified in the SET statement. Note that we have made use of the special table Excluded to get values originally proposed for insertion.
- For a complete description on all three RDBMS syntaxes I remind you the official documentation sites:
- Reference to my previous post on the UPDATE statement
- Check out these related tips:
About the author
View all my tips
Article Last Updated: 2021-02-23