CODEWITHSUNDEEP
sql-serversql-server-2005t-sqlinsertdefault-value
Cynthia
Cynthia

Reputation: 2150

SQL Server: want to insert data into one column, rest nulls: not letting me

Here's my script:

INSERT INTO
 AP09
SELECT
  cds AS CDSCODE
FROM
  P_Schools
WHERE 
  active = 1;

There are ten columns in the AP09 table. All but the primary key (CDSCODE) are nullable. I just want to insert CDSCODEs from another table, and have the rest default to null. Instead I get an error:

SQL Server Database Error: Insert Error: Column name or number of supplied values does not match table definition.

If I supply the right number of nulls, the insert works. But the Microsoft documentation says (for INSERT command):

The Database Engine automatically provides a value for the column if the column: 

--Has an IDENTITY property. The next incremental identity value is used.
--Has a default. The default value for the column is used.
--Has a timestamp data type. The current timestamp value is used.
--Is nullable. A null value is used.
--Is a computed column. The calculated value is used.

Anyone know why this does not seem to be working as advertised? This is costing me a lot of time, as I have to do this for a number of tables! (It's SQL Server 2005 BTW)

Upvotes: 2

Views: 23824

Answers (5)

rhett
rhett

Reputation: 131

The behavior and the documentation is correct.

When performing a insert into foo select att1, att2... you need to provide the exact number of columns in the select statement.

The Microsoft documentation's statement of "automatically providing a value...for nullable columns" is concerning the type of insert statement below, where the columns of the values being inserted are explicitly listed:

INSERT INTO Cities (Location)
VALUES ( 'Chicago' );

in the statement above, if there were 9 other columns that accepted null, they would be implicitly populated with null.

Upvotes: 0

Kell
Kell

Reputation: 3317

INSERT INTO 
 AP09 (YourTargetColumnName)
SELECT 
  cds AS CDSCODE 
FROM 
  P_Schools 
WHERE  
  active = 1;

Upvotes: 0

Vishal
Vishal

Reputation: 12369

Since the table AP09 and CDSCODE dont have the same number of columns ... you need to specify the cols to insert. Try something like this - 

INSERT INTO
 AP09 (Col1)

SELECT
  cds AS CDSCODE 
FROM
  P_Schools
WHERE 
  active = 1;

Upvotes: 7

Philip Kelley
Philip Kelley

Reputation: 40289

You specified one column in your select list, without specifying which of ten columns in the table it is to be loaded into. SQL does not know and will not assume that it can figure out which column in the table to load it into--you'll have to specify which column to insert into. (It only "assumes" if you provide as many columns as there are to be inserted.)

SQL guessing and getting it wrong would be much much worse than just saying "duh, I don't know". Yes, here it would seem to be an easy choice, but they'd have to write algorithms that worked in every possible situation, and that's just not possible.

Upvotes: 0

Donnie
Donnie

Reputation: 46903

Provide a column list to insert.

insert into foo (col1) select ...

Upvotes: 0

Related Questions