A common pattern in SQL Server development is the use of “staging tables”. These are tables, usually temporary, that contain rows that are manipulated in several ways before being inserted into the “real” table, usually at the end of the entire process and in a transaction.
A very common way to create a staging table is to use SELECT INTO to create a temp table that is an exact replica of the target table, like so:
select top 0 *
into #TargetTable
from SourceTable
From there, you can insert new rows into the staging table, manipulate them, and eventually move them into the target table at one time.
However….
If the SourceTable used above happens to have non-nullable columns in it (and corresponding default constraints), the non-nullable attribute of the column will be replicated into the staging table, but the defaults won’t.
This can end up forcing your code to explicitly specify default values for those non-nullable columns. Not a particularly difficult task, but tedious, and error prone. Additionally, it introduces duplicate logic in your SQL (you’ve already set those default values in the definition of SourceTable, so replicating them in your stored proc code is not the greatest idea).
What you need is a way to copy the default constraints to the new staging table after you’ve created it.
And that’s exactly what CopyDefaults is for:
/****************************************************************
Used to copy defaults from a specified source table to a target
table, usually a temp table created to replicate a particular
table, as in:
select top 0 *
into #NewTempTable
from SourceTable
If the source table contains non-nullable columns that have defaults
the above will replicate the null-nullable attribute of the columns
but won't replicate the defaults, so you won't be able to insert
records into the new temp table without specifying values for all the
non-nullable columns.
Usage:
exec CopyDefault 'SourceTable', 'TargetTable'
You may specify a full name for either source or target
exec CopyDefaults 'db.dbo.SourceTable', 'otherdb.dbo.TargetTable'
****************************************************************/
ALTER PROCEDURE CopyDefaults
@SourceTable varchar(256),
@TargetTable varchar(256)
AS
BEGIN
SET NOCOUNT ON
-- Parse the Database names and provide reasonable
-- defaults
declare @SourceDB varchar(128)
declare @TargetDB varchar(128)
select @SourceDB = parsename(@SourceTable, 3)
select @TargetDB = parsename(@TargetTable, 3)
if @SourceDB is null and left(@SourceTable, 1) = '#' begin
select @SourceDB = 'tempdb'
select @SourceTable = 'tempdb..' + @SourceTable
end
if @TargetDB is null and left(@TargetTable, 1) = '#' select @TargetDB = 'tempdb'
if @SourceDB is null select @SourceDB = db_name()
if @TargetDB is null select @TargetDB = db_name()
declare @sql varchar(max)
select @sql=''
-- use a double indirected dynamic sql snippet to
-- create the defaults for the target table, based on those
-- from the Source table.
-- NOTE: the target should NOT already have any of the defaults
-- established!
select @sql =
'
declare @sql varchar(max)
select @sql=''''
select @sql=@sql +
''IF EXISTS (SELECT * FROM ' + @TargetDB + '.dbo.sysobjects WHERE name = '''''' + dc.name + ''xx'''' AND type = ''''D'''')
ALTER TABLE ' + @TargetTable + ' DROP CONSTRAINT '' + dc.name + ''xx
ALTER TABLE ' + @TargetTable +
' ADD CONSTRAINT '' + dc.name + ''xx DEFAULT '' +
replace(replace(definition, ''(('', ''(''), ''))'','')'')
+ '' FOR '' + c.name + char(13) + char(10)
FROM ' + @SourceDB + '.sys.default_constraints dc
INNER JOIN ' + @SourceDB + '.sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
where dc.type = ''D'' and OBJECT_ID(''' + @SourceTable + ''') = dc.parent_object_id
exec (@sql)'
-- and execute the SQL
exec (@sql)
END
GO
Usage is simple. Just create your Staging Table as described above:
select top 0 *
into #TargetTable
from SourceTable
Then execute CopyDefaults:
exec CopyDefaults 'SourceTable', '#TargetTable'
Now your defaults should be in place, so newly inserted rows in #TargetTable will automatically get the appropriate default values.
Finally, here’s a test script to exercise and verify the functionality.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/****************************************************************
Create a test Source Table
****************************************************************/
if OBJECT_ID('tempdb..#SourceTable') is not null drop table #SourceTable
CREATE TABLE #SourceTable(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Bit1] [bit] NOT NULL,
[Bit2] [bit] NOT NULL,
[Address] [varchar](50) NOT NULL, CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED
(ID ASC)
)
ALTER TABLE #SourceTable ADD CONSTRAINT DF_Test1_Bit1 DEFAULT 1 FOR Bit1
ALTER TABLE #SourceTable ADD CONSTRAINT DF_Test1_Bit2 DEFAULT 0 FOR Bit2
ALTER TABLE #SourceTable ADD CONSTRAINT DF_Test1_Address DEFAULT '' FOR Address
/****************************************************************
Insert a sample record
****************************************************************/
insert into #SourceTable (Name) values ('TestName')
/****************************************************************
Select to prove the defaults were filled in
****************************************************************/
select * from #SourceTable
/****************************************************************
Replicate the source table
****************************************************************/
if OBJECT_ID('tempdb..#TargetTable') is not null drop table #TargetTable
select top 0 * into #TargetTable from #SourceTable
/****************************************************************
Try to insert and it will fail
****************************************************************/
BEGIN TRY
insert into #TargetTable (Name) values ('TestName')
print 'Insert into target succeeded (it shouldn''t have)'
END TRY
BEGIN CATCH
print 'Insert into target failed (which is correct)'
END CATCH
/****************************************************************
Copy the default constraints over
****************************************************************/
exec CopyDefaults '#SourceTable', '#TargetTable'
/****************************************************************
And retry to insert and it should succeed now
****************************************************************/
BEGIN TRY
insert into #TargetTable (Name) values ('TestName')
print 'Insert into target succeeded (it should)'
END TRY
BEGIN CATCH
print 'Insert into target failed (which should not have happened)'
END CATCH
select * from #TargetTable
