Default values in Insert Select in SQL

Question

How to pass default values in the Insert Select construction in SQL?

I have a table:

    create table1 (field1 int, field2 int, field3 int default 1337)
    create table2 (field1 int, field2 int)

I want to insert table2 into table1 with a construction similar to this:

    insert into table1 select field1, field2, DEFAULT from table2

Is it possible to use something instead of DEFAULT in my example to get the task done? How are previously selected tables usually inserted with default values?

Answer 1

Try

INSERT INTO table1 (field1, field2)
SELECT field1, field2 FROM table2

I tested this using SQL Server 2005

DECLARE @Table1 TABLE(
     field1 INT,
     field2 INT,
     field3 INT DEFAULT 1337
)

INSERT INTO @Table1 (field1,field2,field3) SELECT 1, 2, 3

DECLARE @Table2 TABLE(
     field1 INT,
     field2 INT
)

INSERT INTO @Table2 (field1,field2) SELECT 15, 16

INSERT INTO @Table1 (field1,field2)
SELECT  field1,
     field2
FROM    @Table2

SELECT * FROM @Table1