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,
DonFirst 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
>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
>
>
>.
>|||"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
>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
>>
>
>.
>|||<anonymous@.discussions.microsoft.com> wrote in message
news:008701c4c82c$0d365fc0$a501280a@.phx.gbl...
> 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
>>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
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
>--Original Message--
><anonymous@.discussions.microsoft.com> wrote in message
>news:008701c4c82c$0d365fc0$a501280a@.phx.gbl...
>> 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
>>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
>
>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
>
>.
>

Copying Row Data within the same table

I have the following table:

Table name: RR

columns:

Subject (varchar (35), Null)

Topic (varchar (35), Null)

RD (text, null)

RR (text, null)

Picture (varchar (50), Null)

Video (varchar (50), Null)

RRID (int, Not Null)

TSTAMP (datetime, Null)

RRCount (int, Not Null)

This table stores common information used in resolving technical problems based on Subject and Topic. However, I've now created a Subject/Topic where I want to copy all the data that corresponds to another Subject/topic.

Example:

There are 35 rows that correspond to Subject = 'Publisher01' and Topic = 'Subcategory03'. I want to create 35 new rows that contain the same RD and RR data, but have Subject = 'Publisher02' and Topic = 'Subcategory07'. Highest current RRID = 5008

I cannot figure out how to write that query. I apologize in advance for the fact that this is, no doubt, a seriously beginner question.

Hi,

would be nice to have some DDL on hand to see your additional table information and some expected results, but anyway:

if you just want to copy these rows (I don′t know what you mean by RRID ?!) the easiest insert statement is:

INSERT INTO RR
(...collist....,Subject,Topic )
SELECT
...collist...,'Publisher02','Subcategory07'
FROM RR
WHERE = 'Publisher01' and
Topic = 'Subcategory03'

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Copy subtree, recursive sproc with cursor doesn't work

Hi all,

I have a parent-child table, and i want to copy subtrees of it, so for instance this would be the starting point:

(id, parentId, label)

0, null, World

1, 0, US

2, 1, NY

3, 0, UK

4, 3, London

now i want to copy object 3 (UK) and it's children, so i would get

0, null, World

1, 0, US

2, 1, NY

3, 0, UK

4, 3, London

5, 0, UK_copy

6, 5, London_copy

I have this sproc:

Code Snippet

alter proc CopyObject

(@.ObjectId int,

@.NewParentId int)

as

declare @.NewId int,

@.NewName varchar

select @.NewId = max(Id) + 1 from Object

select @.NewName = [Name] + 'copy' from [Object] where Id = @.ObjectId

-- copy object

INSERT INTO [Object]

([Id]

,[Name]

,[ParentId]

select @.NewId,

@.NewName,

@.NewParentId

from [Object]

where Id = @.ObjectId

-- copy children and set their parent to the newly created object

declare c cursor fast_forward for

select Id

from [Object]

where ParentId = @.ObjectId

declare @.ChildId int

open c

fetch next from c into @.ChildId

while @.@.fetch_status = 0

begin

exec CopyObject

@.ObjectID = @.ChildId,

@.NewParentId = @.NewId

fetch next from c into @.ChildId

end

close c

deallocate c

But htis throws an error that the cursor already exists:

Msg 16915, Level 16, State 1, Procedure CopyObject, Line 66

A cursor with the name 'c' already exists.

Msg 16905, Level 16, State 1, Procedure CopyObject, Line 72

The cursor is already open.

I've tried to think of an approach without cursors, but i can't figure it out. Because on the first pass, the new parentId will be the same as the parentId of the object to be copied. But the copies of the children of this first original object should have the parentid set to id of the copied object, and so all the way down the tree.

Any ideas?

Thanks in advance,

Gert-Jan

The error makes sense as you are doing recursive calls to CopyObject, which would try to create a new cursor called "c" before closing and destroying the last one.

You could try CLR stored procedure (assuming you have SQL Server 2005), from where (e.g. in C#) you can also do recursion, but without the cursor name confict problem.

|||

I made the whole script dynamic instead, adding an extra parameter @.cycledepth, that i increment every time the proc calls itself, and then call the cursor 'c' + cast(@.cycledepth as varchar). That did the trick, although it's a bit slower tahn i had hoped, but that's probably because of a few triggers i should disable and then enable at the end.

Copy subtree, recursive sproc with cursor doesn't work

Hi all,

I have a parent-child table, and i want to copy subtrees of it, so for instance this would be the starting point:

(id, parentId, label)

0, null, World

1, 0, US

2, 1, NY

3, 0, UK

4, 3, London

now i want to copy object 3 (UK) and it's children, so i would get

0, null, World

1, 0, US

2, 1, NY

3, 0, UK

4, 3, London

5, 0, UK_copy

6, 5, London_copy

I have this sproc:

Code Snippet

alter proc CopyObject

(@.ObjectId int,

@.NewParentId int)

as

declare @.NewId int,

@.NewName varchar

select @.NewId = max(Id) + 1 from Object

select @.NewName = [Name] + 'copy' from [Object] where Id = @.ObjectId

-- copy object

INSERT INTO [Object]

([Id]

,[Name]

,[ParentId]

select @.NewId,

@.NewName,

@.NewParentId

from [Object]

where Id = @.ObjectId

-- copy children and set their parent to the newly created object

declare c cursor fast_forward for

select Id

from [Object]

where ParentId = @.ObjectId

declare @.ChildId int

open c

fetch next from c into @.ChildId

while @.@.fetch_status = 0

begin

exec CopyObject

@.ObjectID = @.ChildId,

@.NewParentId = @.NewId

fetch next from c into @.ChildId

end

close c

deallocate c

But htis throws an error that the cursor already exists:

Msg 16915, Level 16, State 1, Procedure CopyObject, Line 66

A cursor with the name 'c' already exists.

Msg 16905, Level 16, State 1, Procedure CopyObject, Line 72

The cursor is already open.

I've tried to think of an approach without cursors, but i can't figure it out. Because on the first pass, the new parentId will be the same as the parentId of the object to be copied. But the copies of the children of this first original object should have the parentid set to id of the copied object, and so all the way down the tree.

Any ideas?

Thanks in advance,

Gert-Jan

The error makes sense as you are doing recursive calls to CopyObject, which would try to create a new cursor called "c" before closing and destroying the last one.

You could try CLR stored procedure (assuming you have SQL Server 2005), from where (e.g. in C#) you can also do recursion, but without the cursor name confict problem.

|||

I made the whole script dynamic instead, adding an extra parameter @.cycledepth, that i increment every time the proc calls itself, and then call the cursor 'c' + cast(@.cycledepth as varchar). That did the trick, although it's a bit slower tahn i had hoped, but that's probably because of a few triggers i should disable and then enable at the end.