Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Solving a Problem Importing Integers Using Format Files Expand / Collapse
Author
Message
Posted Wednesday, February 15, 2012 10:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:13 AM
Points: 65, Visits: 366
Comments posted to this topic are about the item Solving a Problem Importing Integers Using Format Files
Post #1252869
Posted Thursday, February 16, 2012 4:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 20, 2013 11:34 PM
Points: 2, Visits: 18
BCP has the -C option, it converts the non characters datatypes to character datatypes
Post #1253013
Posted Thursday, February 16, 2012 6:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 14, 2014 1:13 PM
Points: 56, Visits: 127
Good to know all this time i was doing the same conversion you mentioned.

extra work reduced :)
Post #1253074
Posted Thursday, February 16, 2012 7:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 20, 2013 11:34 PM
Points: 2, Visits: 18
BCP has the -C option, it converts the non characters datatypes to character datatypes
Post #1253107
Posted Friday, February 17, 2012 1:09 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 5, 2014 12:21 PM
Points: 78, Visits: 193
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
Posted Friday, February 17, 2012 3:54 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:13 AM
Points: 65, Visits: 366
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
Posted Saturday, February 18, 2012 1:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Sunday, February 19, 2012 11:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:13 AM
Points: 65, Visits: 366
The numbers were wrong in all instances.
Post #1254540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse