1

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,

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,

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?

Any help would be awesome.

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
i.n.n.m
  • 2,311
  • 6
  • 21
  • 43

3 Answers3

4

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.

Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
  • i like using declare @const [define exact data type you want] = [value]. This avoids all the different possible data types 1 can be (bit small int int big int etc) – KeithL Oct 26 '17 at 18:27
  • 2
    @KeithL I agree about datatype but you don't need to use a variable for that. Simply use CONVERT(YourDataType, 1) instead of just the literal 1. – Sean Lange Oct 26 '17 at 18:36
1

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.

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];
kchason
  • 2,586
  • 15
  • 22
  • I do not have`BinTable` created already, I am creating it on the fly as mentioned in the question. This statement needs the tables created beforehand – i.n.n.m Oct 26 '17 at 18:07
  • Updated my answer. – kchason Oct 26 '17 at 18:10
  • You can create a table immediately before, rather that implicitly creating one with a `SELECT ... INTO ...` I assume you are also going to be dropping the table at the end then? – kchason Oct 26 '17 at 18:13
  • 1
    Depending on your application, you may want to look at just using a view and essentially using a derived table. I would check both performance metrics https://stackoverflow.com/questions/16897323/what-to-use-view-or-temporary-table – kchason Oct 26 '17 at 18:16
0

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

SELECT Time, Terminal_ID, Count, 1
INTO BinTable
FROM FinlSls
Eric
  • 2,773
  • 1
  • 13
  • 20