bulk insert system date and time

Question

bulk insert system date and time

hugh_vagn

SSC Veteran

Points: 272
More actions
March 14, 2011 at 12:56 pm

#236741

I would like to do a bulk insert using the following query but on the last field have a date and time stamp. What is the proper syntax?
sql:
bulk insert Location
from '\\test\sql\loc-test4sql1.txt'
with
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
Keepnulls
)
GO
Text file:
136,135,,PLT3,3-010407,3-010-07,,,[system date and time]
137,136,,PLT1,1-0010319,1-001-03-19,,,[system date and time]

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55553 More actions · Answer 1

getdate() is the system datetime. You could make getdate() be the default value in your SQL table so that it gets populated when rows are inserted.

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

Agreed except that you'll also need either a BCP format file or an insertable view to skip the defaulted column.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hugh_vagn SSC Veteran Points: 272 More actions · Answer 3

The problem is that this is not an empty table. I am trying to append the records into it. The date field is mandatory. I have tried your method..getdate() and no luck. Sorry, I'm new to sql.

thanks

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 4

Then my recommendation is to add GETDATE() to the table column as a default and create an "INSERT" view so you can skip it using BULK INSERT and have it still satisfy the "mandatory column" problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 5

Jeff Moden (3/15/2011)
Then my recommendation is to add GETDATE() to the table column as a default and create an "INSERT" view so you can skip it using BULK INSERT and have it still satisfy the "mandatory column" problem.

Scratch that. I never BULK INSERT directly into the final table. I ALWAYS BULK INSERT into a staging table to do data validations, etc. If you do that first, then when you insert into the final table from the staging table, you can just add GETDATE() as the value to insert.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)