Copying Tables and generating new keys

I have a large table that I need to copy, but I need to generate a new value for my id field using a SPROC and replace my existing ID value. I also have a few mapping tables I need to copy, so I need to store this new ID for later use. I currently have a SPROC that performs all these actions, but it takes about 3 or 4 minutes to complete and completely hogs the CPU time. Thus, I can't perform any actions until it finishes.

I'm looking for a way to run this procedure in the background. Unfortunately, my ID field value is not a GUID nor an IDENTITY column. I've researched Integration Services, but I was unable to find any DataFlow Tranformations to call a SPROC to retreive a new id nor could I find anything that would let me store my new id to update my mapping tables. SQLBulkCopy wasn't a good solution either.

If anyone has any insight to this, it would be greatly appreciated. Thanks,

You can use the OLE DB Command to call a stored proc and get an output value, but it tends to slow down execution. Any way to change this to a set based operation?

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.

Copy SPROCs to different database

Is there a manual or code way to copy stored procedures from 1one sql db to
another without opening each one, copying, creating a new SPROC in target db
and pasting?
Can DTS accomplish this?You can use DTS. If you have enterprise manager, you can also just
right-click on a database and choose to import or export data. When you get
into the wizard, choose to "Copy objects and data betwen SQL Server
databases". You can then save this entire thing as a DTS package, which you
can manipulate, schedule, etc.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"scott" wrote:

> Is there a manual or code way to copy stored procedures from 1one sql db t
o
> another without opening each one, copying, creating a new SPROC in target
db
> and pasting?
> Can DTS accomplish this?
>
>|||You can use Enterprise Manager and right click on the db and choose "All
Tasks - Generate SQL Script". Then choose all the sp's and you will get one
script for all the sps. Just run that in the other db.
Andrew J. Kelly SQL MVP
"scott" <sbailey@.mileslumber.com> wrote in message
news:e45c5eteGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Is there a manual or code way to copy stored procedures from 1one sql db
> to another without opening each one, copying, creating a new SPROC in
> target db and pasting?
> Can DTS accomplish this?
>|||You can use DTS to copy database objects from one database to another.
Are you using Sql Server 2000 or Sql Server 2005? I think, if I rememer
correctly, there is a copy database or copy database objects task in 2000.
2005 makes it a whole lot easier.
-Tim
"scott" <sbailey@.mileslumber.com> wrote in message
news:e45c5eteGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Is there a manual or code way to copy stored procedures from 1one sql db
> to another without opening each one, copying, creating a new SPROC in
> target db and pasting?
> Can DTS accomplish this?
>