Insert NULL and DEFAULT values with an INSERT/SELECT statement

Question

Hi, In MICROSOFT SQL SERVER have the following table:

CREATE TABLE [T1]
(
 [ID] int IDENTITY (1, 1) NOT NULL,
 [col1] int NOT NULL,
 [col2] int NOT NULL,
 [col3] int NULL,
 [col4] datetime NOT NULL DEFAULT (getdate()),
 [col5] datetime NOT NULL DEFAULT (getdate())
)

I want to write an insert statement that select 2 columns from another table and insert all the other column as NULL or default. This is what I have tried so far (but it doesn't work):

    INSERT INTO [T1] ([col1],[col2], [COL3])
SELECT [1column],[2column],NULL
FROM [T2]

When I right click on T1 table and select open table the table has only 2 columns, even if in the columns "folder" in object explorer there are all the columns

What I want to achieve is to have in T1: in Col1 and COL2 the result of the SELECT and in COL3 NULL and in COL4 and COL5 the default value!

Answer 1

INSERT INTO [T1] ([col1], [col2], [COL3])
SELECT [1column],[2column],NULL
FROM [T2]

This should work fine, just checked:

INSERT
INTO    [T1] ([col1], [col2], [col3])
SELECT  1, 2, NULL

SELECT  *
FROM    [T1]

id    col1  col2  col3  col4                     col5
---   ---   ---   ---   ---                      ---
1     1     2     NULL  2009-04-22 15:46:47.090  2009-04-22 15:46:47.090

What's the exact error you are receiving?

Answer 2

Table is wrong, query is wrong or permissions are wrong.

1. Right click on the table name in SSMS and click refresh. Do you see 5 columns? SELECT * FROM T1 will give what you have.

2. When you right click.. open table, click the [SQL] button on the toolbar, do you see SELECT * FROM T1?

3. You do not have SELECT permissions on the other columns, but have INSERT (not very likely) so MetaData visibility is preventing SELECT * working.

Answer 3

When you select to open the table and you already have a window with the table open from before, it will show you that window instead of opening another window. What you see is an old result from when the table only had two columns.

Close that window and open the table again to see the current state of the table.