Problem
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.
Solution
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.
SQL Server
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.
PostgreSQL
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";
Oracle
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.
SQL Server
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
Oracle
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.
PostgreSQL
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.
SQL Server
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
Oracle
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;
PostgreSQL
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.
SQL Server
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 <span style="color: #808080">*</span>
from Discount_Table
Oracle
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;
PostgreSQL
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.
PostgreSQL
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.
Next Steps
- 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:
Andrea Gnemmi is a Database and Data Warehouse professional with almost 20 years of experience, having started his career in Database Administration with SQL Server 2000.
His expertise ranges from SQL Server to Oracle and Postgres for database administration and from SAP BO to SSIS, SSRS and Power BI regarding BI and ETL.
He has worked both in SQL Server and Oracle mostly in multinational environments with very high transaction volumes and large datasets. Performance tuning, indexing techniques and in general Database administration are his hot topics. Ensuring a 24/7 uptime of the various databases in shop is his main responsibility, as well as the best possible performances of all queries run on the databases. He has started working with PostgreSQL two years ago loving many of the features of this RDBMS.
- MSSQLTips Awards: Trendsetter (25+ tips) – 2024 | Rookie of the Year – 2021