By: Dallas Snider | 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)
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.
- Comparing SQL Server and Oracle Datatypes
- Concatenation of Different SQL Server Data Types
- Comparing SQL Server Datatypes, Size and Performance for Storing Numbers
- SQL Server SELECT Tutorial
- Using the CASE expression instead of dynamic SQL in SQL Server
- Joining data using UNION and UNION ALL in SQL Server
- SQL Server Integration Services Tips
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips