INSERT INTO for SQL Server, Oracle and PostgreSQL

By:   |   Comments   |   Related: > SQL Server vs Oracle vs PostgreSQL Comparison


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
query resuilts

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;
query resuilts

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";
query resuilts

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 $$;
query resuilts

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
query resuilts

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;
query resuilts

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";
query resuilts

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
query resuilts

And just to check what we have in this new table:

select *
from Discount_Table
query resuilts

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;
query resuilts

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;
query resuilts
query resuilts

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
query resuilts

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
query resuilts

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" || ')'
query resuilts

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" || ')'
query resuilts

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.

query resuilts
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Andrea Gnemmi 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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article

















get free sql tips
agree to terms