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.
thanksIf 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@.shadhawk.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...
> > 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
> >
> >
>|||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@.shadhawk.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...
>> > 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
>> >
>> >
>>
>|||With the format file both native and character mode worked. Thanks again for
your time.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.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...
> >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@.shadhawk.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...
> >> > 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
> >> >
> >> >
> >>
> >>
> >
> >
>