A stored proc to copy table data including any default value or binding data via a SQL script running within SQL Server

Question

I would like to be able to copy a table and it's data and also still have any default value or binding (as it is labelled within SQL Server Management console) constraints copied over.

The script below is a testing script to demonstrate the idea. The last line I assume needs to be replaced with a call to a custom stored proc?

Note: The source table (aSourceTbl) schema varies and can change over time.

--TEST SETUP 
--Delete the prev tables so test script can be replayed
IF OBJECT_ID('aSourceTbl', 'U') IS NOT NULL 
        DROP TABLE aSourceTbl;

IF OBJECT_ID('aSourceCopyTbl', 'U') IS NOT NULL 
        DROP TABLE aSourceCopyTbl;

--Simple table to demonstrate table copying does not carry over the table constraits
CREATE TABLE [dbo].[aSourceTbl](
    [aValue] [int] NOT NULL,
    [DELETED] [int] NOT NULL
) ON [PRIMARY]

--Add some dummy data
INSERT INTO aSourceTbl (aValue, DELETED) VALUES (1,2);
INSERT INTO aSourceTbl (aValue, DELETED) VALUES (3,4);

--Add constraints of default values of 0 in this case 
ALTER TABLE [dbo].[aSourceTbl] ADD  CONSTRAINT [DF_aSourceTbl_aValue]  DEFAULT ((0)) FOR [aValue]
ALTER TABLE [dbo].[aSourceTbl] ADD  CONSTRAINT [DF_aSourceTbl_DELETED]  DEFAULT ((0)) FOR [DELETED]

--Actual Required SQL script from here down 
--The line below works nicely but does not copy the 2 constraints from the lines above into the new table. 
--TODO QUESTION: Replace line below with the same functionaility + the constraints are also copied into new table
Select * INTO aSourceCopyTbl FROM aSourceTbl

Could you please help me by suggesting a suitable stored proc that can replace the last line in above SQL snippet? Any help greatly appreciated :)

References:

Similar SO Question however focuses on PK constraints. I am only interested in default value constraints in this case.

Is this a one-time script, or is it something you plan to incorporate within the application? — James Johnson
– James Johnson, Commented Sep 29, 2011 at 13:33
An ad-hoc script. Used to (i) take copies of tables, (ii) then trial update/algos on the copied table data, (iii) then depending on results possibly swap the copied table back in to replace the original table. Hence maintaining constraints is required. Please note: most of the script above is just test related. The last line just needs to be enhanced so as to also copy table constraints (+data etc). — ryan
– ryan, Commented Sep 29, 2011 at 23:25
Is this a script that you'll be using in an application, or is it just for maintenance purposes? — James Johnson
– James Johnson, Commented Sep 29, 2011 at 23:28

Michal Barcik · Accepted Answer · 2011-09-29 22:04:20Z

4

You can execute this code after the last row which will replicate the defauld constraints to the new table (replace the variables with your table names).

declare @table_name sysname, @new_table sysname, @cmd varchar(max)
select @table_name = 'SOURCE_TABLE', @cmd = '', @new_table = 'TEST_TABLE'


select @cmd = @cmd+'ALTER TABLE '+@new_table+' ADD CONSTRAINT [DF_' +@new_table+'_'+a.name+'] DEFAULT '+b.definition+' FOR['+a.name+']; 
' 
from sys.columns a 
join sys.default_constraints b on a.object_id = b.parent_object_id and a.column_id = b.parent_column_id
where a.object_id = object_id(@table_name)  

print @cmd
exec (@cmd)

answered Sep 29, 2011 at 22:04

Michal Barcik

6624 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JoeFletch Over a year ago

Nice answer. I added a cursor to loop through all of the constraints and added them to my new table. Thanks!

Collectives™ on Stack Overflow

A stored proc to copy table data including any default value or binding data via a SQL script running within SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related