INSERT using SELECT

[nwm76]

Hi,

I'm trying to copy data from one table to another.

The source table has columns:

Name varchar(30), Day decimal(8,2), Depth decimal(8,2),
Name1 varchar(30), Day1 decimal(8,2), Depth1 decimal(8,2),
Name2 varchar(30), Day2 decimal(8,2), Depth2 decimal(8,2)


Destination table is similar, but has many more columns

Name2 varchar(30), Day2 decimal(8,2), Depth2 decimal(8,2)
.
.
Name85 varchar(30), Day85 decimal(8,2), Depth85 decimal(8,2)


If I try and run:

INSERT INTO dest_table
SELECT * FROM source_table

I get:
Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.

All columns in the destination table are nullable, and the column names are the same as in the source, so I would have expected this to work OK. I need to be able to do this without stating an input column list if possible.

TIA,
Neil

 

[JamesLean]

You have two options. Easiest is to specify the column list:

INSERT dest_table (col1, col2, col3)
SELECT col1, col2, col3
FROM source_table

Alternatively, specify NULL values for the remaining columns:

INSERT dest_table
SELECT col1, col2, col3, NULL, NULL, NULL, ...
FROM source_table

--James