Solving a Problem Importing Integers Using Format Files

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Brandon Forest » Solving a Problem Importing Integers Using...

Solving a Problem Importing Integers Using Format Files

Rate Topic

Display Mode

Topic Options

Author

Message

Brandon Forest

Posted Wednesday, February 15, 2012 10:27 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:29 AM
Points: 60, Visits: 303

Comments posted to this topic are about the item Solving a Problem Importing Integers Using Format Files

Post #1252869

csolervicens

Posted Thursday, February 16, 2012 4:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 16, 2012 3:36 PM
Points: 2, Visits: 17

BCP has the -C option, it converts the non characters datatypes to character datatypes

Post #1253013

Ash DBA

Posted Thursday, February 16, 2012 6:29 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:36 PM
Points: 56, Visits: 117

Good to know all this time i was doing the same conversion you mentioned.

extra work reduced :)

Post #1253074

csolervicens

Posted Thursday, February 16, 2012 7:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 16, 2012 3:36 PM
Points: 2, Visits: 17

BCP has the -C option, it converts the non characters datatypes to character datatypes

Post #1253107

Caruncles

Posted Friday, February 17, 2012 1:09 PM

Valued Member

Group: General Forum Members
Last Login: Friday, May 03, 2013 2:29 PM
Points: 55, Visits: 150

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."

Post #1254136

Brandon Forest

Posted Friday, February 17, 2012 3:54 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:29 AM
Points: 60, Visits: 303

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

Post #1254232

wmjas.shaw

Posted Saturday, February 18, 2012 1:11 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 20, 2012 10:08 AM
Points: 1, Visits: 1

It isn't clear from your description what "unsatisfactory" means. Were the numbers simply wrong in some instances?

Post #1254376

Brandon Forest

Posted Sunday, February 19, 2012 11:14 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:29 AM
Points: 60, Visits: 303

The numbers were wrong in all instances.

Post #1254540

« Prev Topic | Next Topic »

Permissions