copying table to new schema not working

SQL 7.0
Trying to copy a table to a table with different schema
and getting an error.
Old Schema example
Name, char, 30 NOT NULL
address, char, 30 NOT NULL
New Schema
Name,char, 30 NOT NULL
id,number,10 NULL ..........new field
address, char, 30 NOT NULL
I had imagined that the fields that were common would
copy and the new field "ID" would not fill in. I'm pretty
sure my test worked yesterday, but today, it fails with
the error that old.address is trying to copy to
new.number and it fails.
There are roughly 100 million rows and if I try to modify
the old table directly by adding the ID field, SQL
eventually gives some kind of LOG error.
Any suggestions on how to convert this table to the new
schema?
Thanks,
Don
First of all, there is no datatype in SQL Server called 'number'. I'm not
sure why you're not getting an error as a result of that. Second, how are
you doing this copy? Can you post code and/or what method you're using?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
> SQL 7.0
> Trying to copy a table to a table with different schema
> and getting an error.
> Old Schema example
> Name, char, 30 NOT NULL
> address, char, 30 NOT NULL
> New Schema
> Name,char, 30 NOT NULL
> id,number,10 NULL ..........new field
> address, char, 30 NOT NULL
> I had imagined that the fields that were common would
> copy and the new field "ID" would not fill in. I'm pretty
> sure my test worked yesterday, but today, it fails with
> the error that old.address is trying to copy to
> new.number and it fails.
> There are roughly 100 million rows and if I try to modify
> the old table directly by adding the ID field, SQL
> eventually gives some kind of LOG error.
> Any suggestions on how to convert this table to the new
> schema?
> Thanks,
> Don
>
>
|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
> SQL 7.0
> Trying to copy a table to a table with different schema
> and getting an error.
> Old Schema example
> Name, char, 30 NOT NULL
> address, char, 30 NOT NULL
> New Schema
> Name,char, 30 NOT NULL
> id,number,10 NULL ..........new field
> address, char, 30 NOT NULL
> I had imagined that the fields that were common would
> copy and the new field "ID" would not fill in. I'm pretty
> sure my test worked yesterday, but today, it fails with
> the error that old.address is trying to copy to
> new.number and it fails.
> There are roughly 100 million rows and if I try to modify
> the old table directly by adding the ID field, SQL
> eventually gives some kind of LOG error.
> Any suggestions on how to convert this table to the new
> schema?
> Thanks,
> Don
>
>
How are you trying to copy it?
Are you using SEM to try to add the new field?
How about running:
ALTER TABLE ADD id numeric(x,y) NULL
Other choices that can avoid the LOG error issues..
BCP the data out.
TRUNCATE the table.
ALTER TABLE to add the column.
Remove indexes
BULK INSERT the data back in.
Recreate your indexes.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||I'd been using DTS to copy. Are you saying BULK copy will
not have the same LOG filling up problems?

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
pretty[vbcol=seagreen]
modify
>How are you trying to copy it?
>Are you using SEM to try to add the new field?
>How about running:
>ALTER TABLE ADD id numeric(x,y) NULL
>Other choices that can avoid the LOG error issues..
>BCP the data out.
>TRUNCATE the table.
>ALTER TABLE to add the column.
>Remove indexes
>BULK INSERT the data back in.
>Recreate your indexes.
>
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>
>.
>
|||"number" was just an example of a field type and not to
be taken literally.
Using DTS to do the copy. Just exporting from Source and
Destination, and then choosing the table to copy from and
the new table to copy to which has the new schema.

>--Original Message--
>First of all, there is no datatype in SQL Server
called 'number'. I'm not
>sure why you're not getting an error as a result of
that. Second, how are
>you doing this copy? Can you post code and/or what
method you're using?
>
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
pretty[vbcol=seagreen]
modify
>
>.
>
|||<anonymous@.discussions.microsoft.com> wrote in message
news:008701c4c82c$0d365fc0$a501280a@.phx.gbl...[vbcol=seagreen]
> I'd been using DTS to copy. Are you saying BULK copy will
> not have the same LOG filling up problems?
>
> message
> pretty
> modify
It depends on how you do the package.
DTS (if you look closely) will use BCP in a lot of cases.
Take a look at "Logged Bulk Copy" in the BOL. It should show you how to do
a minimally logged BCP command. BULK INSERT is generally faster however, so
keep that option in mind as well.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||The BULK COPY is nice and all, but why add an unnecessary field to an old
schema?
The problem with your DTS is that you haven't properly mapped the source
columns to the destination columns.
You could just use a straight INSERT statement.
INSERT INTO newschema.MyTable(name, address)
SELECT name, address
FROM oldschema.MyTable
Sincerely,
Anthony Thomas

"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:5f6d01c4c820$966b9c80$a301280a@.phx.gbl...
> SQL 7.0
> Trying to copy a table to a table with different schema
> and getting an error.
> Old Schema example
> Name, char, 30 NOT NULL
> address, char, 30 NOT NULL
> New Schema
> Name,char, 30 NOT NULL
> id,number,10 NULL ..........new field
> address, char, 30 NOT NULL
> I had imagined that the fields that were common would
> copy and the new field "ID" would not fill in. I'm pretty
> sure my test worked yesterday, but today, it fails with
> the error that old.address is trying to copy to
> new.number and it fails.
> There are roughly 100 million rows and if I try to modify
> the old table directly by adding the ID field, SQL
> eventually gives some kind of LOG error.
> Any suggestions on how to convert this table to the new
> schema?
> Thanks,
> Don
>
>
|||This might be a rediculous question, but if I detach and
reattach the log file to a larger hard drive with a lot
of space...i shouldn't get the out of LOG space message?
Is this a "duh" question?
Don
[vbcol=seagreen]
>--Original Message--
><anonymous@.discussions.microsoft.com> wrote in message
>news:008701c4c82c$0d365fc0$a501280a@.phx.gbl...
will[vbcol=seagreen]
schema[vbcol=seagreen]
with[vbcol=seagreen]
new
>
>It depends on how you do the package.
>DTS (if you look closely) will use BCP in a lot of cases.
>Take a look at "Logged Bulk Copy" in the BOL. It
should show you how to do
>a minimally logged BCP command. BULK INSERT is
generally faster however, so
>keep that option in mind as well.
>HTH
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
>