bulk insert with format file with datetime

How do you specify the datetime format in format files for bulk insert?

I have some tables that I need to import, and I can easily do that with a simple bulk insert, but in this case I need to map fields because data is not identical in format with the table.

Here my problems start. No matter how I try, I can not create a date format that can be inserted when I use a format file.

CustodeAsked:
Who is Participating?
 
lauszCommented:
Did you try ?
0
 
lauszCommented:
Can you post a little sample  .?
0
 
CustodeAuthor Commented:
An example :

Format file

8.0
4
1       SQLINT        0       4       "|||"                     1     t2id          ""
2       SQLCHAR       0       20      "|||"                     2     t2_shopid     Danish_Norwegian_CI_AS
3       SQLCHAR       0       100     "|||"                     3     t2_f1         Danish_Norwegian_CI_AS
4       SQLDATETIME   0       8       "||||"                    4     t2_f3         ""


Data file

10|||||||||2002/01/01||||

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
lauszCommented:
I don't understand ..

is this the field t2id = 10|||
t2_shopid |||
t2_f1 = |||
t2_f3 2002/01/01||||

????

What are you using as column delimiter ?
What is the destination table design ?



0
 
CustodeAuthor Commented:
Create table t2 (
 t2id int primary key not null,
 t2_shopid varchar(20) null,
 t2_f1 varchar(100) null,
 t2_f3 datetime null
)

This is just a table I created to find a solution to the problem. It's easier to play with than a table with 400 fields.

FieldTerminator is '|||' and RowTerminator is '||||'

As I mentioned, it works fine if I use

bulk insert t2 from 'C:\test\t2.txt' with
 (
  FieldTerminator = '|||',
  RowTerminator = '||||'
)

but it doesn't work if I use a format file like the one above.

 
0
 
lauszCommented:
I try your example with your bulk insert and the datetime works ok.
What kind of problem do you have.

In your example I have the fields (t2_shopid  and t2_f1) with nulls and the other info ok.
0
 
CustodeAuthor Commented:
Did you do like

bulk insert t2 from 'c:\tmp\t2.txt' with
(
 formatfile = 'c:\tmp\t2.fmt'
)

and make it work?
0
 
lauszCommented:
TRY ONLY CHANGING IN LINE 4 FROM .FMT

4       SQLCHAR       0       10      "||||"                    4     t2_f3         ""

I think it works
0
 
CustodeAuthor Commented:
Yes, I tried, and It worked.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.