Integrating SQL Server Data Using Union, Case and Cast in T-SQL

By: Dallas Snider | Updated: 2014-11-04 | Comments (1) | Related: > JOIN Tables

Problem

Sometimes we need to integrate data from multiple tables into a single table. While SQL Server Integration Services (SSIS) can handle this task, depending on the situation this integration task could be handled with T-SQL.

Solution

In this tip, we will examine the integration of table data through the use of the T-SQL UNION operation along with the INTO clause, the CAST function and the CASE expression. The three tables I am creating all have different structures, but similar data which I will convert using CAST to create one unified table.

Create SQL Server Tables

Let's begin by creating three small tables with the following T-SQL Code.

set nocount on  --suppress the count of the number of rows affected
go

--drop the tables for this example if they exist
IF EXISTS (SELECT name FROM sys.tables WHERE name = N'tableA')
   drop table dbo.tableA
IF EXISTS (SELECT name FROM sys.tables WHERE name = N'tableB')
   drop table dbo.tableB
IF EXISTS (SELECT name FROM sys.tables WHERE name = N'tableC')
   drop table dbo.tableC
IF EXISTS (SELECT name FROM sys.tables WHERE name = N'tableD')
   drop table dbo.tableD

create table dbo.tableA
(
   ColumnA1 int,
   ColumnA2 varchar(3),
   ColumnA3 date
)

create table dbo.tableB
(
   ColumnB1 int,
   ColumnB2 varchar(5),
   ColumnB3 datetime
)

create table dbo.tableC
(
   ColumnC1 int,
   ColumnC2 tinyint,
   ColumnC3 datetime
)

Populate the SQL Server Tables

Next, let's populate our sample tables and then select the content of the tables.

insert into dbo.tableA values (1,'yes',getdate())
insert into dbo.tableA values (2,'no',getdate())
insert into dbo.tableA values (3,'yes',getdate())

insert into dbo.tableB values (1,'true',getdate())
insert into dbo.tableB values (2,'false',getdate())
insert into dbo.tableB values (3,'true',getdate())

insert into dbo.tableC values (1,1,getdate())
insert into dbo.tableC values (2,0,getdate())
insert into dbo.tableC values (3,1,getdate())

select * from dbo.tableA
select * from dbo.tableB
select * from dbo.tableC

We can see the contents of our sample tables below.

T-SQL Code to Integrate Data

In the T-SQL code below we will convert the second column of TableA and TableC to varchar(5) through the use of the CAST function. We will also use the CASE expression to convert our values in the second column of TableA and TableC to true and false. Furthermore, the third column of TableB and TableC will be cast as a DATE data type, dropping the time component of the DATETIME datatype. Finally, we add a column named SourceTable to identify the source of our record.

select 
cast('tableA' as varchar(6)) as SourceTable,
ColumnA1 as ColumnD1,
cast(case 
       when ColumnA2='yes' then 'true' 
       else 'false' 
     end as varchar(5)) as ColumnD2,
ColumnA3 as ColumnD3
from dbo.tableA
union ----------------------------
select
cast('tableB' as varchar(6)) as SourceTable,
ColumnB1 as ColumnD1,
ColumnB2 as ColumnD2,
cast(ColumnB3 as date) as ColumnD3
from dbo.tableB
union ----------------------------
select
cast('tableC' as varchar(6)) as SourceTable,
ColumnC1 as ColumnD1,
cast(case 
       when ColumnC2=1 then 'true' 
       else 'false' 
     end as varchar(5)) as ColumnD2,
cast(ColumnC3 as date) as ColumnD3
from dbo.tableC

The code above performed the data integration in the results window and did not store the data to a table. Adding the INTO clause in the second line below will write the results to TableD. Also, the following T-SQL code below will convert the second column of TableA and TableB to the tinyint data type through the use of the CAST function. Furthermore, we will use the CASE expression to convert our values in the second column of TableA and TableB to the tinyint values one and zero. Finally, the third column in TableA will be cast as the DATETIME datatype and the time component will be defaulted to 00:00:00.000.

select D.* 
into dbo.tableD
from
(
   select
   cast('tableA' as varchar(6)) as SourceTable,
   ColumnA1 as ColumnD1,
   cast(case 
          when ColumnA2 ='yes' then 1
          else 0 
        end as tinyint) as ColumnD2,
   cast(ColumnA3 as datetime) as ColumnD3
   from dbo.tableA
   union ----------------------------
   select
   cast('tableB' as varchar(6)) as SourceTable,
   ColumnB1 as ColumnD1,
   cast(case 
          when ColumnB2 ='true' then 1
          else 0 
        end as tinyint) as ColumnD2,
   ColumnB3 as ColumnD3
   from dbo.tableB
   union ----------------------------
   select
   cast('tableC' as varchar(6)) as SourceTable,
   ColumnC1 as ColumnD1,
   ColumnC2 as ColumnD2,
   ColumnC3 as ColumnD3
   from dbo.tableC
) as D

select * from dbo.tableD

We can execute ALTER TABLE statements if we need to add a primary key to our newly created table.

ALTER table dbo.tableD add PrimaryKey int identity(1,1) not null
ALTER table dbo.tableD add constraint PK_tableD_PrimaryKey Primary Key (PrimaryKey)

TableD after adding the primary key column

Summary

In this tip, we have seen ways to integrate our table data using T-SQL code.

Next Steps

Try variations in the data types and CASE expressions. Also, check out these other tips relating to data types.

About the author

Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

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

View all my tips

Article Last Updated: 2014-11-04