I have a table, Table1, with 7 columns. Lets name them C1 to C7.
我有一个表，表1，有7列。我们把它们命名为C1到C7。
Columns C3-C6 allow for NULL values.
列C3-C6允许空值。
How do I write a SQL insert statement into Table1 that will grab the values for C1, and C2 from a different table (ReferenceTable), while setting C7 to the value 0?
如何在将C7设置为0的同时，在表1中写入SQL insert语句，以便从另一个表(ReferenceTable)中获取C1和C2的值?
Right now, I have the following:
现在，我有以下几点:
INSERT INTO Table1 (C1, C2)
SELECT @C1, C2
FROM ReferenceTable
This does almost everything I need, except for initializing C7 with the value 0. How would I go about doing this?
除了用0初始化C7之外，这几乎完成了我需要的所有工作。我该怎么做呢?
2 个解决方案
#1
3
You can append the 0 to the SELECT...
您可以将0附加到SELECT…
INSERT INTO Table1 (C1, C2, C7)
SELECT C1, C2, 0
FROM ReferenceTable
or, you can set C7 to have a default value of 0, which would be my preferred option.
或者，可以将C7设置为默认值0，这是我的首选选项。
ALTER TABLE Table1
ALTER COLUMN C7 SET DEFAULT (0)
So, C7 will always be 0 if nothing is inserted.
如果没有插入，C7总是0。
You'll see this in the designer (within Management Studio), when you modify a table, the field you are looking for is Default Value or Binding
您将在设计器(Management Studio内)中看到这一点，当您修改一个表时，您要查找的字段是默认值或绑定
#2
4
Use this insert query:
使用这个插入查询:
insert into table1 (c1,c2,c7) (select c1,c2,0 from reftable)
#1
3
You can append the 0 to the SELECT...
您可以将0附加到SELECT…
INSERT INTO Table1 (C1, C2, C7)
SELECT C1, C2, 0
FROM ReferenceTable
or, you can set C7 to have a default value of 0, which would be my preferred option.
或者，可以将C7设置为默认值0，这是我的首选选项。
ALTER TABLE Table1
ALTER COLUMN C7 SET DEFAULT (0)
So, C7 will always be 0 if nothing is inserted.
如果没有插入，C7总是0。
You'll see this in the designer (within Management Studio), when you modify a table, the field you are looking for is Default Value or Binding
您将在设计器(Management Studio内)中看到这一点，当您修改一个表时，您要查找的字段是默认值或绑定
#2
4
Use this insert query:
使用这个插入查询:
insert into table1 (c1,c2,c7) (select c1,c2,0 from reftable)