使用T-SQL中具有常量值的新列插入来自不同表的值

I am inserting all values from a table into another table, which is created on the fly. I want to insert one extra column with constant value to the new table I create.

我将表中的所有值插入到另一个表中，该表是动态创建的。我想在我创建的新表中插入一个具有常量值的额外列。

I have my FinSls table like this,

我有这样的FinSls表，

Time             Terminal_ID   Count
------------------------------------
2017-10-19 06:03:00     1       5
2017-10-19 06:04:00     1       2
2017-10-19 06:05:00     1       2
2017-10-19 06:06:00     1       2
2017-10-19 06:03:00     9       2
2017-10-19 06:04:00     9       3
2017-10-19 06:05:00     9       2
2017-10-19 06:06:00     9       3

I am creating a new table BinTable and I want to add a column cons_val with a constant value, for example: 1, like this,

我正在创建一个新表BinTable，我想添加一个带有常量值的列cons_val，例如：1，像这样，

Time              Terminal_ID  Count  Cons_value
------------------------------------------------
2017-10-19 06:03:00     1       5      1
2017-10-19 06:04:00     1       2      1
2017-10-19 06:05:00     1       2      1
2017-10-19 06:06:00     1       2      1
2017-10-19 06:03:00     9       2      1
2017-10-19 06:04:00     9       3      1
2017-10-19 06:05:00     9       2      1
2017-10-19 06:06:00     9       3      1

So far I have my script like this,

到目前为止，我有这样的脚本，

select *
into  
   BinTable
from 
   FinlSls

How can I add a new column Cons_Value into my BinTable? I saw this post that already has the table created and then inserting value. But in my situation, how can I do this?

如何在我的BinTable中添加新列Cons_Value？我看到这篇帖子已经创建了表，然后插入了值。但在我的情况下，我该怎么做？

Any help would be awesome.

任何帮助都是极好的。

3 个解决方案

#1

You just add it:

你只需添加它：

select f.*, 1 as cons_value
into BinTable
from FinlSls f;

There may be some criticism in using select * instead of listing out the columns.

使用select *而不是列出列可能会有一些批评。

#2

You can treat it like any other INSERT statement and choose which columns are inserted into. In addition, you can choose which columns are selected, which allows you to joins as well.

您可以像处理任何其他INSERT语句一样对待它，并选择插入哪些列。此外，您可以选择选择哪些列，这也允许您加入。

It's good to define the table first, to ensure you create primary keys etc, I thought this was already being created as part of your process. I'm assuming this is a one time deal, in which case you would want tables indexed properly. If this was a temporary table for use in a stored procedure, then I would suggest using a temporary table (begins with a #).

最好先定义表，确保创建主键等，我认为这已经是作为流程的一部分创建的。我假设这是一次性交易，在这种情况下，您希望表格正确索引。如果这是一个用于存储过程的临时表，那么我建议使用临时表（以＃开头）。

CREATE TABLE
   [BinTable]
(
   [BinTableID] INT PRIMARY KEY IDENTITY(1,1),
   [Time] DATETIME,
   [Terminal_ID] INT,
   [Count] INT,
   [Cons_val] INT
);

Then you can insert the data.

然后你可以插入数据。

INSERT INTO
   [BinTable]
([Time], [Terminal_ID], [Count], [Cons_value])
SELECT
    [Time], 
    [Terminal_ID], 
    [Count],
    1 AS [Cons_value]
FROM
    [FinSls];

#3

If the new table already exists, use INSERT SELECT, else you can use the code below

如果新表已存在，请使用INSERT SELECT，否则您可以使用下面的代码

SELECT Time, Terminal_ID, Count, 1
INTO BinTable
FROM FinlSls

#1