CODEWITHSUNDEEP
sqlsql-server
Shmewnix
Shmewnix

Reputation: 1573

How to add insert into a table from select statement

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

Upvotes: 2

Views: 136

Answers (2)

FutbolFan
FutbolFan

Reputation: 13763

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

Upvotes: 3

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

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

Upvotes: 2

Related Questions