BCP Format File

Hi,
1) How do we specify constant values in BCP Format File?
ex
FileABC -- CONSTANT 1044035814.
2) How do we map one Value to TWO column fields in BCP Format File.
ex -- Data file Value -- "123 ABCDE"
Filed1 -- 123
--
Filed 2.. I want the value to be "AB"
Field 3.. I want the value to be "CDE"
--
Regards
Govardhan MVIf data values are not present in the source file, you need to use DEFAULT
constraints on the columns so that the desired values are inserted instead
of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> Hi,
> 1) How do we specify constant values in BCP Format File?
> ex
> FileABC -- CONSTANT 1044035814.
> 2) How do we map one Value to TWO column fields in BCP Format File.
> ex -- Data file Value -- "123 ABCDE"
> Filed1 -- 123
> --
> Filed 2.. I want the value to be "AB"
> Field 3.. I want the value to be "CDE"
> --
> Regards
> Govardhan MV
>|||"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> schrieb im
Newsbeitrag news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> Hi,
> 1) How do we specify constant values in BCP Format File?
> ex
> FileABC -- CONSTANT 1044035814.
> 2) How do we map one Value to TWO column fields in BCP Format File.
> ex -- Data file Value -- "123 ABCDE"
> Filed1 -- 123
> --
> Filed 2.. I want the value to be "AB"
> Field 3.. I want the value to be "CDE"
> --
bcp is designed to do fast bulk copy operations. AFAIK there is no such
feature. You might be able to do this with DTS. With that you can define
quite flexible data conversions.
Alternatively preprocess the file with some scripting language tool of
your choice and then do the import with bcp.
Cheers
robert|||Can i Know if i specify the Default constraints , does the performance get
affected ,
our application the performance is very important . we will load millions of
data
at a time. or we have use update statements.
"Dan Guzman" wrote:

> If data values are not present in the source file, you need to use DEFAULT
> constraints on the columns so that the desired values are inserted instead
> of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
> parameter.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
>
>|||I doubt you will experience a noticeable performance hit with the default
constraint.
Hope this helps.
Dan Guzman
SQL Server MVP
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:AE8BEA49-4EDF-410D-B8AD-2B212044A2E6@.microsoft.com...[vbcol=seagreen]
> Can i Know if i specify the Default constraints , does the performance get
> affected ,
> our application the performance is very important . we will load millions
> of
> data
> at a time. or we have use update statements.
> "Dan Guzman" wrote:
>

BCP Format File

Hi,
1) How do we specify constant values in BCP Format File?
ex
FileABC -- CONSTANT 1044035814.
2) How do we map one Value to TWO column fields in BCP Format File.
ex -- Data file Value -- "123 ABCDE"
Filed1 -- 123
--
Filed 2.. I want the value to be "AB"
Field 3.. I want the value to be "CDE"
--
Regards
Govardhan MVIf data values are not present in the source file, you need to use DEFAULT
constraints on the columns so that the desired values are inserted instead
of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
parameter.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> Hi,
> 1) How do we specify constant values in BCP Format File?
> ex
> FileABC -- CONSTANT 1044035814.
> 2) How do we map one Value to TWO column fields in BCP Format File.
> ex -- Data file Value -- "123 ABCDE"
> Filed1 -- 123
> --
> Filed 2.. I want the value to be "AB"
> Field 3.. I want the value to be "CDE"
> --
> Regards
> Govardhan MV
>|||"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> schrieb im
Newsbeitrag news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> Hi,
> 1) How do we specify constant values in BCP Format File?
> ex
> FileABC -- CONSTANT 1044035814.
> 2) How do we map one Value to TWO column fields in BCP Format File.
> ex -- Data file Value -- "123 ABCDE"
> Filed1 -- 123
> --
> Filed 2.. I want the value to be "AB"
> Field 3.. I want the value to be "CDE"
> --
bcp is designed to do fast bulk copy operations. AFAIK there is no such
feature. You might be able to do this with DTS. With that you can define
quite flexible data conversions.
Alternatively preprocess the file with some scripting language tool of
your choice and then do the import with bcp.
Cheers
robert|||Can i Know if i specify the Default constraints , does the performance get
affected ,
our application the performance is very important . we will load millions of
data
at a time. or we have use update statements.
"Dan Guzman" wrote:
> If data values are not present in the source file, you need to use DEFAULT
> constraints on the columns so that the desired values are inserted instead
> of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
> parameter.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> > Hi,
> >
> > 1) How do we specify constant values in BCP Format File?
> > ex
> > FileABC -- CONSTANT 1044035814.
> >
> > 2) How do we map one Value to TWO column fields in BCP Format File.
> > ex -- Data file Value -- "123 ABCDE"
> >
> > Filed1 -- 123
> > --
> > Filed 2.. I want the value to be "AB"
> > Field 3.. I want the value to be "CDE"
> > --
> >
> > Regards
> > Govardhan MV
> >
> >
>
>|||I doubt you will experience a noticeable performance hit with the default
constraint.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:AE8BEA49-4EDF-410D-B8AD-2B212044A2E6@.microsoft.com...
> Can i Know if i specify the Default constraints , does the performance get
> affected ,
> our application the performance is very important . we will load millions
> of
> data
> at a time. or we have use update statements.
> "Dan Guzman" wrote:
>> If data values are not present in the source file, you need to use
>> DEFAULT
>> constraints on the columns so that the desired values are inserted
>> instead
>> of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
>> parameter.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
>> news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
>> > Hi,
>> >
>> > 1) How do we specify constant values in BCP Format File?
>> > ex
>> > FileABC -- CONSTANT 1044035814.
>> >
>> > 2) How do we map one Value to TWO column fields in BCP Format File.
>> > ex -- Data file Value -- "123 ABCDE"
>> >
>> > Filed1 -- 123
>> > --
>> > Filed 2.. I want the value to be "AB"
>> > Field 3.. I want the value to be "CDE"
>> > --
>> >
>> > Regards
>> > Govardhan MV
>> >
>> >
>>

BCP Format File

Hi,
1) How do we specify constant values in BCP Format File?
ex
FileABC -- CONSTANT 1044035814.
2) How do we map one Value to TWO column fields in BCP Format File.
ex -- Data file Value -- "123 ABCDE"
Filed1 -- 123
Filed 2.. I want the value to be "AB"
Field 3.. I want the value to be "CDE"
Regards
Govardhan MV
If data values are not present in the source file, you need to use DEFAULT
constraints on the columns so that the desired values are inserted instead
of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> Hi,
> 1) How do we specify constant values in BCP Format File?
> ex
> FileABC -- CONSTANT 1044035814.
> 2) How do we map one Value to TWO column fields in BCP Format File.
> ex -- Data file Value -- "123 ABCDE"
> Filed1 -- 123
> --
> Filed 2.. I want the value to be "AB"
> Field 3.. I want the value to be "CDE"
> --
> Regards
> Govardhan MV
>
|||"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> schrieb im
Newsbeitrag news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
> Hi,
> 1) How do we specify constant values in BCP Format File?
> ex
> FileABC -- CONSTANT 1044035814.
> 2) How do we map one Value to TWO column fields in BCP Format File.
> ex -- Data file Value -- "123 ABCDE"
> Filed1 -- 123
> --
> Filed 2.. I want the value to be "AB"
> Field 3.. I want the value to be "CDE"
> --
bcp is designed to do fast bulk copy operations. AFAIK there is no such
feature. You might be able to do this with DTS. With that you can define
quite flexible data conversions.
Alternatively preprocess the file with some scripting language tool of
your choice and then do the import with bcp.
Cheers
robert
|||Can i Know if i specify the Default constraints , does the performance get
affected ,
our application the performance is very important . we will load millions of
data
at a time. or we have use update statements.
"Dan Guzman" wrote:

> If data values are not present in the source file, you need to use DEFAULT
> constraints on the columns so that the desired values are inserted instead
> of NULL. BCP honors DEFAULT constraints unless overridden with the '-k'
> parameter.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:F2C48A4F-4500-415F-B439-2D9E61D1518E@.microsoft.com...
>
>
|||I doubt you will experience a noticeable performance hit with the default
constraint.
Hope this helps.
Dan Guzman
SQL Server MVP
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:AE8BEA49-4EDF-410D-B8AD-2B212044A2E6@.microsoft.com...[vbcol=seagreen]
> Can i Know if i specify the Default constraints , does the performance get
> affected ,
> our application the performance is very important . we will load millions
> of
> data
> at a time. or we have use update statements.
> "Dan Guzman" wrote:

BCP character conversion

I bcped in data into a database with SQL_Latin1_General_Cp1_CI_AS collation. The input data has an embedded character (ascii 174). I did not specify any code page using the -C parm. The data was converted to character (ascii 171). I ran the bcp trying -C1252 and -CRAW and both maintained the correct character. -C437 and -COEM change the character to .
Why did this happen? I thought that data would be converted to correctly without any code page specification.Different code pages map binary values to glyphs (the graphic symbols that humans know and love) differently. One binary value can map to many different glyphs using different code pages.

If BPC doesn't know which code page to use for translation, you get "pot luck", especially for characters that aren't well defined. Typically, you want the code page that created the data. Occaisionally, you want the code page that was intended (or at least used) to view the data. Because of the pot-pouri of mappings supported by the different code pages, the business of getting data from point A to point B has grown yet another potentially "interesting" twist to amuse those of us that do the moving!

-PatP|||Thanks for the quick reply.
BOL states:
"When bulk copying data using native or character format, bcp, by default, converts character data to:

OEM code page characters when exporting data from an instance of Microsoft SQL Server.

ANSI/Microsoft Windows code page characters when importing data into an instance of SQL Server. "

So wouldn't the bcp in use code page 1252 by default. This should be similar to -C1252.|||Well where did the data come from?|||The bcp ran from my workstation with a code page 437 - if that's your question.|||From Books Online topic bcp: "OEM Default code page used by the client. This is the default code page used by bcp if -C is not specified."
From that I suppose that SQL Server interpreted your file as being OEM 437 CP. mojza|||I guess I still don't understand why the character was changed during the bcp. I can view it correctly from my workstation which is 437, but if I bcp using -C437 or without -C(which uses default OEM code page) it gets converted. I think I'm missing something.|||In what editor can you see that character correctly? in ANSI (e.g.Notepad) or in OEM (e.g.Edit)? mojza|||Correctly in notepad or textpad, not correctly in edit. So bcp, running in a command window, is using 437 which changes the character to ?|||Then, in my opinion, your file was created in code page ANSI 1252 (notepad ok) and bcp interprets your file as cp 437 (default client OEM code page). That leads to a loss of some extended characters that are not compatible between these two pages unless you tell sql server to interpret him as 1252 or without any translation (RAW). Check out this Microsoft article. There is a good explanation and excellent examples. mojza

http://support.microsoft.com/default.aspx?scid=kb;en-us;199819|||Thanks for your help. That article definitely helped explain things. I also looked at the nls files for 437 and 1252 and character 174(offset x0178) reflects in the 1252 file and int 437 file.
Again, thanks.

BCP (BULK INSERT) with only <lf> as line delimiter (not <cr><lf>)...

Hi,

This might be a noob question, but is there any way to specify just <lf> as a line delimiter, and not <cr><lf> (/n) as line delimiter in T-SQL call for BULK INSERT (or BCP utility). How please? TIA.

Usually any of these three combination would work for all kinds of files.

/n
/r
/r/n

You might try /r first, I think that's the one you need if /n doesnt' work.

/Kenneth