SQL Server Insert Into Select Part 2

 in SQL Server  tagged sql / insert / table / join / into / select / another by

This post shows you how to insert data into an existing empty table from two other tables using have a left join. For more information on Insert Into Select, have a look at the article called SQL INSERT INTO SELECT Statement at the website w3schools.com.

Another example could be using is geographic data.

In accounting, the balance sheet has three types of accounts: assets, liabilities and owner’s equity. Within these three types therre are sub-types, however in this example we will not be looking at the sub-types. Here is our table called AccountType.

Here is our table called AccountChart. It is intentionally small. In reality this could have thousands of entries.

Here is the empty table we need to populate. For a few entries like we have here we might consider entering these in manually but we will be using a query that does the work for us.

Inner Join

We need to join two tables into one to get the data we need to populate the Accounts table.

Here is the results of a join.

Here is the code that generated the above results. We joined on AccountTypeAbbreviation. 

SELECT TOP (1000) AccountName
      ,t.AccountTypeId
      ,c.AccountTypeAbbreviation
	  ,t.AccountTypeName
  FROM [dbo].[AccountChart] c
  join [dbo].[AccountType] t on
  c.[AccountTypeAbbreviation] = t.[AccountTypeAbbreviation]

Inserting

We can use a variation of the above join to insert the information into out table. 

begin tran
insert into Accounts (AccountName, AccountTypeId)
select AccountName, t.AccountTypeId
  from [dbo].[AccountChart] c
  join [dbo].[AccountType] t on
  c.[AccountTypeAbbreviation] = t.[AccountTypeAbbreviation]
select * from Accounts
rollback tran

Below are the results of running the above transaction twice. The first column is an identity column. When you rollback a transaction, the identity column remembers. We show this just for illustration.

Left Join

What if we used a left join and there were some invalid entries in our list of accounts? That invalid entry would be included in the results. If we use the equi join as we did above, those invalid entries would not be included.