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
– M.Ali, Commented Dec 13, 2014 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
– M.Ali, Commented Dec 13, 2014 at 22:33

Gordon Linoff · Accepted Answer · 2014-12-13 22:33:55Z

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, 2014 at 22:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

eavom · Accepted Answer · 2014-12-14 15:34:22Z

3

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'

answered Dec 14, 2014 at 15:34

eavom

1,1051 gold badge9 silver badges27 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related