SQL import excel add current date column

0
votes

I would like to import an excel sheet to SQL using the import wizard and add an additional column to the table which has the current date.

For example:

In Excel, I have the following: Col1, Col2, Col3, Col4

In the SQL Table I would like: Col1, Col2, Col3, Col4 and an additional Col with the current date.

The table has all 5 columns with the 5th column a datetime datatype. I will delete the rows in the destination table before importing.

Please help.

2
sqlsql-serverexcel
stackoverflow.com/questions/43357768/… – Abhijeetk431
Possible duplicate of How to insert date while importing Excel into SQL Server – Dan Bracuk

2 Answers

2
votes

Add a default value of GETDATE() to your table:

ALTER TABLE [dbo].[yourTable] 
      ADD CONSTRAINT [DF_yourDate]  DEFAULT (GETDATE()) FOR [yourDate]

Then just exclude yourDate column from inserts.

2
votes

When using Import Wizard you have the option of writing a query to specify the data transfer. This is adding the current datetime to a load from Sheet1.

    SELECT *,NOW() AS LoadDt FROM `Sheet1$`

LoadDt was placed first in data mapping, so in editing the SQL in that tab just moved that column to the end.