Copying data into a table with default constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Copying data into a table with default constraint

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2012-09-04 : 10:37:47

How do I copy data from one table into another, specifying a default constraint value?


CREATE DATABASE Test1
GO
USE [Test1]
GO
CREATE TABLE Test1
(col1 varchar(10), col2 int, col3 bit)
GO
INSERT INTO Test1
SELECT 'Hello',1,0
GO
CREATE DATABASE Test2
GO
USE [Test2]
GO
CREATE TABLE Test1
(col1 varchar(10), col2 int, col3 bit, col4 int default (99), col5 bit)

So I want to select the default to go into col4:

INSERT INTO Test2.dbo.Test1
SELECT col1, col2, col3, default, col5 FROM Test1.dbo.Test1

Hearty head pats

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-09-04 : 10:53:53

specify your column list. just don't insert column 4, and the default will fill itself in.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Bex
Aged Yak Warrior

580 Posts

Posted - 2012-09-04 : 11:07:05

Thank you

Hearty head pats

stonebreaker
Yak Posting Veteran

92 Posts

Posted - 2012-09-04 : 11:09:46

If you're wanting to supply the default value from your select, then use an ISNULL() or COALESCE().

SELECT
col1,
col2,
ISNULL(col3,<default>),
col4,
etc.

Stonebreaker
The greatest obstacle to discovery is not ignorance - it is the illusion of knowledge.
-Daniel Boorstin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources