Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1192
  • Last Modified:

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.

0
Custode
Asked:
Custode
  • 5
  • 4
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
lauszCommented:
Did you try ?
0
 
CustodeAuthor Commented:
Yes, I tried, and It worked.

Thanks.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now