SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Solving a Problem Importing Integers Using Format Files


Solving a Problem Importing Integers Using Format Files

Author
Message
Brandon Forest
Brandon Forest
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 446
Comments posted to this topic are about the item Solving a Problem Importing Integers Using Format Files
csolervicens
csolervicens
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 46
BCP has the -C option, it converts the non characters datatypes to character datatypes
Ash DBA
Ash DBA
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 140
Good to know all this time i was doing the same conversion you mentioned.

extra work reduced Smile
csolervicens
csolervicens
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 46
BCP has the -C option, it converts the non characters datatypes to character datatypes
Caruncles
Caruncles
SSChasing Mays
SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)SSChasing Mays (623 reputation)

Group: General Forum Members
Points: 623 Visits: 244
The -n option in BCP is supposed to import to it's native format. I haven't had the kind of problem you describe, but then again my business requirements may not be as critical. I can say that I have not had a problem importing integer data types. I seem to have more trouble with the Imp/Exp wizard.

.. copied from somewhere-- can't remember-
-n
Performs the bulk copy operation using the native (database) data types of the data. This option does not prompt for each field; it uses the native values.
-c
Performs the bulk copy operation using a character data type. This option does not prompt for each field; it uses char as the storage type, no prefixes, \t (tab character) as the field separator, and (newline character) as the row terminator.
-N
Performs the bulk copy operation using the native (database) data types of the data for noncharacter data, and Unicode characters for character data. This option offers a higher performance alternative to the -w option, and is intended for transferring data from one SQL Server to another using a data file. It does not prompt for each field. Use this option when you are transferring data that contains ANSI extended characters and you want to take advantage of the performance of native mode. -N cannot be used with SQL Server 6.5 or earlier.

Wallace Houston
Sunnyland Farms, Inc.

"We must endeavor to persevere."
Brandon Forest
Brandon Forest
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 446
The -n (native format) is a great way to transfer data between SQL server tables, but that's not the case in this scenario. These are text files we receive from a Vendor. It is simple enough to import them as Char data types into the raw extract tables and implicitly converting them to Integer between the other SQL tables, and I was doing that initially. The business requirements changed however, and I was forced to import them as integers directly into the raw extract tables. This article describes how to import text data directly into integer fields.

Cheers!

Brandon Forest
wmjas.shaw
wmjas.shaw
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 2
It isn't clear from your description what "unsatisfactory" means. Were the numbers simply wrong in some instances?
Brandon Forest
Brandon Forest
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 446
The numbers were wrong in all instances.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search