使用INSERT / SELECT语句插入NULL和DEFAULT值

In MICROSOFT SQL SERVER have the following table:

在MICROSOFT SQL SERVER中有如下表:

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语句,从另一个表中选择2列,并将所有其他列插入为NULL或default。这是我到目前为止所尝试的(但它不起作用):

    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

当我右键单击T1表并选择打开表时,表只有2列,即使在对象资源管理器的“文件夹”列中也有所有列

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!

我想要实现的是在T1中:在Col1和COL2中SELECT的结果和COL3中的NULL以及COL4和COL5中的默认值!

3 个解决方案

#1

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.

关闭该窗口并再次打开表以查看表的当前状态。

#2

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?

您收到的确切错误是什么?

#3

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

表错了,查询错误或权限错误。

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

右键单击SSMS中的表名,然后单击“刷新”。你看到5列吗? SELECT * FROM T1将提供您所拥有的。
When you right click.. open table, click the [SQL] button on the toolbar, do you see SELECT * FROM T1?

当你右键单击打开表格时,单击工具栏上的[SQL]按钮,你看到SELECT * FROM T1吗?
You do not have SELECT permissions on the other columns, but have INSERT (not very likely) so MetaData visibility is preventing SELECT * working.

您没有其他列的SELECT权限,但有INSERT(不太可能),因此MetaData可见性阻止SELECT *工作。

#1