bulk insert question

Hi! We are in the process of adding few columns to our large tables (200
million rows) and altering few columns from varchar to char. One of the
option I am thinking is to bcp data out, change the schema and bulk insert
data in. Looks like with either bcp.exe or bulk insert command, you won't be
able to load the data in, if the schema of table get changed. Is this true?
I know that I can use DTS export/import to do this task but since bulk
insert is the fastest method I would like to try that option if possible.
Besides, Bulk insert I could also change schema with Alter table command. I
don't know if thats better than unloading/change/reload method that I
mentioned above.
I would appreiciate it, if anyone who have export/change schema/import large
table, give me some direction here.

thanks

If you use the Native mode I don't think you can do it but have you actually
tried?  BCP out a few thousand rows, create anew table and Bulk Insert it
back in.  If native wont work I am pretty sure char mode will.

--
Andrew J. Kelly  SQL MVP


"james" <kush@brandes.com> wrote in message
news:%23j3fNPKrFHA.3640@tk2msftngp13.phx.gbl...
> Hi! We are in the process of adding few columns to our large tables (200
> million rows) and altering few columns from varchar to char. One of the
> option I am thinking is to bcp data out, change the schema and bulk insert
> data in. Looks like with either bcp.exe or bulk insert command, you won't
> be
> able to load the data in, if the schema of table get changed. Is this
> true?
> I know that I can use DTS export/import to do this task but since bulk
> insert is the fastest method I would like to try that option if possible.
> Besides, Bulk insert I could also change schema with Alter table command.
> I
> don't know if thats better than unloading/change/reload method that I
> mentioned above.
> I would appreiciate it, if anyone who have export/change schema/import
> large
> table, give me some direction here.
>
> thanks
>
>

I have tried both native and character mode and both erroed out. Which is
kind of expected, since schema got changed, the program doesn't have any way
of knowing which column in datafile (native or char) maps to which column in
table.
I haven't tried format file so far, which is what I am going to do next and
see if I can alter format file and make this thing work.
Thanks for your Input.

"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:uWZ$MoLrFHA.2624@TK2MSFTNGP15.phx.gbl...
> If you use the Native mode I don't think you can do it but have you
 actually
> tried?  BCP out a few thousand rows, create anew table and Bulk Insert it
> back in.  If native wont work I am pretty sure char mode will.
>
> --
> Andrew J. Kelly  SQL MVP
>
>
> "james" <kush@brandes.com> wrote in message
> news:%23j3fNPKrFHA.3640@tk2msftngp13.phx.gbl... 
 insert[color=darkred
] 
 won't
 
possible. 
command. 
>
>

Yes if you change the columns around you need to use a format file.  But I
don't see why it wont work with the format file.

--
Andrew J. Kelly  SQL MVP


"james" <kush@brandes.com> wrote in message
news:%23Z9GTdWrFHA.2624@TK2MSFTNGP15.phx.gbl...
>I have tried both native and character mode and both erroed out. Which is
> kind of expected, since schema got changed, the program doesn't have any
> way
> of knowing which column in datafile (native or char) maps to which column
> in
> table.
> I haven't tried format file so far, which is what I am going to do next
> and
> see if I can alter format file and make this thing work.
> Thanks for your Input.
>
> "Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
> news:uWZ$MoLrFHA.2624@TK2MSFTNGP15.phx.gbl... 
> actually 
> insert 
> won't 
> possible. 
> command. 
>
>

With the format file both native and character mode worked. Thanks again for
your time.
"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:%23ov1hlWrFHA.3352@TK2MSFTNGP14.phx.gbl...
> Yes if you change the columns around you need to use a format file.  But I
> don't see why it wont work with the format file.
>
> --
> Andrew J. Kelly  SQL MVP
>
>
> "james" <kush@brandes.com> wrote in message
> news:%23Z9GTdWrFHA.2624@TK2MSFTNGP15.phx.gbl... 
 column[color=darkred
] 
it 
the 
bulk 
schema/ import[color=darkred
] 
>
>