How to insert from one table into another with extra values in SQL?

Question

I have two tables. I want to insert from Table1 into Table2, and I would like to insert extra values into the columns that Table 2 has. Is there a way to do this all in one SQL statement?

Table 1

[Id]             UNIQUEIDENTIFIER NOT NULL,
[Description]    VARCHAR (140)    NULL,
[Cost]           MONEY            NULL,

Table 2

[Id]                  UNIQUEIDENTIFIER NOT NULL,
[Order Number]        NVARCHAR (10)    NULL,
[Order Date]          DATETIME         NULL,
[Item Number]         NVARCHAR (4)     NULL,
[Item Description]    VARCHAR (140)    NULL,
[Item Cost]           MONEY            NULL,
[Order Total Cost]    MONEY            NULL,
[Order Tax Cost]      MONEY            NULL,

Here is my SQL Statement:

INSERT INTO Table2 ([Id], [Item Description], [Item Cost])
SELECT NEWID(), Description, Cost FROM  Table1
WHERE Id = '1'

You are on the right path, whats stopping you from adding other values in your select statement from table1 , like you have added NEWID() , add other values as well. — M.Ali, Dec 13 '14 at 22:31
`INSERT INTO Table2 ([Id], [Item Description], [Item Cost], Col1 , col2) SELECT NEWID(), Description, Cost, @String1 , @String2 FROM Table1 WHERE Id = '1'` — M.Ali, Dec 13 '14 at 22:33
Possibly related to [**this question**](http://stackoverflow.com/questions/27462490/issues-with-getting-uniqueidentifier-auto-generate-in-sql-on-insert/27462506#27462506) — Radu Gheorghiu, Dec 13 '14 at 22:36

score 8 · Accepted Answer · answered Dec 13 '14 at 22:33

8

Just add the values onto the select line along with the columns in the column list:

INSERT INTO Table2 ([Id], [Item Description], [Item Cost], [Order Date])
    SELECT NEWID(), Description, Cost, '2014-12-13'
    FROM  Table1
    WHERE Id = '1';

answered Dec 13 '14 at 22:33

Gordon Linoff

1,242,037
58
646
786

score 3 · Answer 2 · answered Dec 14 '14 at 15:34

You can do it by adding values for particular column in select statement

Like,

INSERT INTO TABLE2([COL1], [COL2], [COL3], [COL4])
    SELECT [COL1], [COL2], 'Value1', 'Value2'
    FROM TABLE1

SQL statement for your tables

INSERT INTO TABLE2 ([Id], [Item Description], [Item Cost], [Order Number], [Order Date] )
    SELECT NEWID(), Description, Cost, '1234', '2012-12-12' 
    FROM  TABLE1
    WHERE Id = '1'

How to insert from one table into another with extra values in SQL?

2 Answers2