How to add insert into a table from select statement

Question

I would like to insert data into one table, from selected data from another. For example, I'm doing this:

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;

For example,

It the select statement would return, and insert:

Robert    USA
Richard   Germany
Pat       USA

This works fine.

What i'm looking to do now, is populate a datetransfered column that is not part of the suppliers table. The date will just be today (getdate)

The insert statement would be:

insert into Customer(CustomerName, Country, datetransfered)

How would I add the 'datetransfered' portion as "Today's Date" (GetDate) for each row returned in the select statement?

I'd like it to insert:

Robert    USA         9/2/15
Richard   Germany     9/2/15
Pat       USA         9/2/15

FutbolFan · Accepted Answer · 2015-09-02 19:37:01Z

3

Just add the getdate() and cast it to a date in your select statement like this:

INSERT INTO Customers (CustomerName, Country,datetransfered)
SELECT SupplierName, Country,cast(getdate() as date) FROM Suppliers;

Result:

+--------------+---------+----------------+
| CustomerName | Country | datetransfered |
+--------------+---------+----------------+
| Robert       | USA     | 2015-09-02     |
| Richard      | Germany | 2015-09-02     |
| Pat          | USA     | 2015-09-02     |
+--------------+---------+----------------+

SQL Server Demo

edited Sep 2, 2015 at 19:37

answered Sep 2, 2015 at 19:21

FutbolFan

13.9k3 gold badges27 silver badges35 bronze badges

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

Comments

Rahul Tripathi · Accepted Answer · 2015-09-02 19:30:19Z

2

Try this:

INSERT INTO Customers (CustomerName, Country, datetransfered)
SELECT SupplierName, Country, getdate() FROM Suppliers;

getdate() will insert todays date in your datetransfered column.

EDIT:

If you want to store only the date part then you need to cast it as getdate() return type is datetime.

INSERT INTO Customers (CustomerName, Country, datetransfered)
SELECT SupplierName, Country, cast(getdate() as date) FROM Suppliers

or using Convert()

INSERT INTO Customers (CustomerName, Country, datetransfered)
SELECT SupplierName, Country, CONVERT(DATE, GETDATE()) FROM Suppliers

edited Sep 2, 2015 at 19:30

answered Sep 2, 2015 at 19:21

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Collectives™ on Stack Overflow

How to add insert into a table from select statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related