Insert part of one table into another with original default attribute value

Question

I have two tables with the following schema,

• table1 (a int, b int, c int, d int default 123)
• table2 (a int, b int, c int, e int)

I would like to update the (a,b,c) columns of table2 into table1, but keep the default value of d.

For example, with the following record

------------------------
table1 | a | b | c | d |
------------------------
       | 1 | 2 | 3 | 4 |
------------------------

and

------------------------
table2 | a | b | c | e |
------------------------
       | 5 | 6 | 7 | 8 |
------------------------

I would like an output of:

--------------------------
table1 | a | b | c | d   |
--------------------------
       | 1 | 2 | 3 | 4   |
       | 5 | 6 | 7 | 123 |
--------------------------

Here is what I have tried:

INSERT INTO table1(a, b, c, d) 
VALUES ((SELECT a, b, c FROM table2), DEFAULT)

This query has an invalid syntax, because it returns the table in the sub-query and uses it as a value.

I have also tried to avoid this solution below, because it hides the fact, that the default value of d is used. I really want to explicitly make it default to make it clear.

INSERT INTO table1(a, b, c) 
    SELECT a, b, c  
    FROM table2

Is there a way to combine

INSERT .. VALUES(..)

with

INSERT.. SELECT.. FROM table

or just another way to achieve the same goal?

Answer 1

The following code hunts down the default value definition for a specific column in a specific table and cobbles it into an integer. The value can then be used to explicitly supply the default value in other statements, e.g. insert.

-- if object_id( N'Samples', N'U' ) is not NULL
--   drop table Samples;

create table Samples ( Id Int Identity, Foo Int Default 42, Bar Int );

-- Get the default value from the metadata.
declare @FooDefaultValue as NVarChar(32); -- Note:   sys.default_constraints.definition   is declared as   NVarChar(max) .
select @FooDefaultValue = sdf.definition
  from sys.tables as st inner join
    sys.columns as sc on sc.object_id = st.object_id inner join
    sys.default_constraints as sdf on sdf.object_id = sc.default_object_id
    where st.name = 'Samples' and sc.name = 'Foo';
select @FooDefaultValue as 'Default Value NVarChar';

-- Cobble it from a string to an integer (since we know the correct data type).
declare @FooDefaultValueInteger as Int =
  Cast( Replace( Replace( @FooDefaultValue, '(', '' ), ')', '' ) as Int );
select @FooDefaultValueInteger as 'Default Value Integer';

-- Insert a few rows with data for both columns.
insert into Samples ( Foo, Bar )
  select Foo, Bar
    from ( values ( 1, 1 ), ( 2, 2 ), ( 3, 3 ) ) as Arthur( Foo, Bar );

-- Insert a few rows with an explicit default value for   Foo .
insert into Samples ( Foo, Bar )
  select @FooDefaultValueInteger, Bar
    from ( values ( 5 ), ( 6 ), ( 7 ) ) as Dent( Bar );

-- Display the results.
select * from Samples;

drop table Samples;

This is clumsy at best. The cobbling from string to integer strikes me as fragile. It should probably have more validation of the schema name, ... . It gets more interesting when the table is temporary. And unravels completely if the table is a variable. By separating getting the default value from its use it opens the possibility of names failing to agree, i.e. a quick cut'n'paste without editing the table or column name could result in bad things.

All said, I'd avoid this and add a comment near or in the insert explaining what ought to occur.

Answer 2

INSERT INTO table1 values (a, b, c) 
    SELECT a, b, c FROM table2