Inserting default values using a SELECT statement

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 2000 Forums

Transact-SQL (2000)

Inserting default values using a SELECT statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-26 : 08:14:52

Pamela writes "I want to insert records from one table (table1) into another table (table2) using an INSERT INTO... SELECT statement. table2 has a non-NULL field (status)with a default value (1). How can I get the records from table1 to be inserted into table2 with the default value of table2 w/o specifying it as a constant in the SELECT statement?

INSERT INTO table2 (
status,
parcel_number,
site_number)

SELECT
'1',
ParcelNumber,
SiteAddress_number

FROM table1

** Is there a way to not have to specify the constant '1' in the SELECT list? I get an error if I don't specify "status" as one of the columns.

Thanks!"

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-26 : 08:18:48

quote:

INSERT INTO table2 (
status,
parcel_number,
site_number)

SELECT
'1',
ParcelNumber,
SiteAddress_number

FROM table1

** Is there a way to not have to specify the constant '1' in the SELECT list? I get an error if I don't specify "status" as one of the columns.

What error do you get? Can you not just do

INSERT INTO table2 (
parcel_number,
site_number)

(SELECT
ParcelNumber,
SiteAddress_number

FROM table1
)
?

For example -

drop table #moo
drop table #test
create table #moo
(thing varchar (2) not null default '4',
this int null,
more int null)

insert into #moo (this) values (1)

create table #test
(this int null, more int null)

insert into #test (this, more) values (2,3)

select * from #test
select * from #moo

insert into #moo (this,more)
(select * from #test)

select * from #moo

Works, so I can't see why you can't do the same.

-------
Moo.

Edited by - mr_mist on 11/26/2002 08:30:12

Edited by - mr_mist on 11/26/2002 08:41:28

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-26 : 08:20:56

Declare @default As Integer

SELECT TOP 1 @status=status, parcel_number,site_number
FROM table2

INSERT INTO TABLE2(status,parcel_number,site_number)
SELECT @status,ParcelNumber,SiteAddress_number
FROM TABLE1

Caution: table must have a least one entry in order for this method to work.

Henderson
Starting Member

5 Posts

Posted - 2002-11-26 : 18:27:29

If every new insert in the table needs a status of '1', then you can set this default with the CREATE/ALTER TABLE

e.g.

ALTER TABLE [Table1] WITH NOCHECK ADD
CONSTRAINT [DF_Table1_status] DEFAULT (1) FOR [status]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources