0

So data can be imported into SQL Server from .csv files. Import CSV file into SQL Server

I'm trying to use this to import test data into a database. So I want the dates to be up-to-date. Currently we use .sql files with getdate() so after inserting the dates are all newly generated. But when inserting getdate() with bulk insert from a .csv file it will just say 'getdate()'. The dates are only an example, I need different rows to be calculated differently. So one date might get 5 added to it, another 10.

Community
  • 1
  • 1
MrFox
  • 4,852
  • 7
  • 45
  • 81
  • 1
    Possible duplicate of [SQL Server - Inserting default values bcp](http://stackoverflow.com/questions/32069586/sql-server-inserting-default-values-bcp) – ivan_pozdeev Dec 01 '15 at 17:39
  • Also related: http://stackoverflow.com/questions/3544526/how-do-i-bulk-insert-with-additional-column-showing-filename – ivan_pozdeev Dec 01 '15 at 22:35

1 Answers1

1

Although bulk insert does not let you specify function calls, you could work around the problem by changing your table definition: add default constraint to your date column, and do not insert anything into it through bulkinsert. This would ensure that SQL Server fills the column by calling getdate:

ALTER TABLE MyTable ADD CONSTRAINT
DF_MyTable_MyDateColumn_GetDate DEFAULT GETDATE() FOR MyDateColumn
Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • Thanks, but it needs to insert different dates. It seems this is not possible. – MrFox Dec 01 '15 at 14:25
  • @MrFox What do you mean by "inserting different dates"? This approach will insert different dates, too, based on the time when the row is inserted. The call to `GETDATE()` is made only when the data being inserted does not provide a value for `MyDateColumn`. – Sergey Kalinichenko Dec 01 '15 at 14:32
  • Within one set of rows the dates for different rows must be different. – MrFox Dec 01 '15 at 14:36
  • 1
    @MrFox How could you achieve this with `GETDATE()`, unless the data changes while your code is performing bulk insert? – Sergey Kalinichenko Dec 01 '15 at 14:46
  • Because we normal insert I can do GETDATE() + 5, GETDATE() + 10 on different records. Also it's really inconvienent to regenerate the .csv file every time it has to be inserted. – MrFox Dec 02 '15 at 08:35