CODEWITHSUNDEEP
sqlsql-serversql-insert
Aspiring Developer
Aspiring Developer

Reputation: 680

Using a SQL SELECT * Insert Into Statement But With Missing Columns Being Null

If you have 1 table with columns Type and Amount, then another table that has Type, Amount and 12 other columns that are not in table 1, is there an easier way than writing this below? Repeating "NULL" 12 time just seems archaic, maybe ? Or is this the only known solution right now ?

There are similar questions on here but none really address the multiple NULL column issue (from what I see), with the classic error of `"Column name or number of supplied values does not match table definition." coming up in different posts. I'm using SQL Server 2017.

  INSERT INTO Table2 
  SELECT 
    Type
   ,Amount
   ,--Insert 12 NULLs here
  FROM Table1

Upvotes: 1

Views: 1875

Answers (2)

forpas
forpas

Reputation: 164214

Specify the columns after the name of the table:

  INSERT INTO Table2 (Type, Amount)
  SELECT 
    Type
   ,Amount
  FROM Table1

All the other columns will get a NULL or their DEFAULT values.
This will not work if the other not specified columns are defined as NOT NULL without a DEFAULT value.

Upvotes: 1

daShier
daShier

Reputation: 2116

Yes, you can identify the columns explicitly: 

     INSERT INTO Table2 (Type, Amount)
     SELECT Type, Amount   
     FROM Table1

I would think you would need a WHERE clause, but this should get you the idea.

Upvotes: 1

Related Questions