July 21, 2009 at 5:13 am
Hi everybody
I have around 6 tables connected using one to many relationship
I want to make a procedure where I supply the key of the parent record so that it makes a copy of it and all the related records
One solution I have is using cursors
But I think this is a frequent solution that has an efficient and easy solution
Am I right?
Thank you
July 21, 2009 at 5:30 am
a much better solution is to use a set based operation; cursors are much slower.
you didn't provide any details, so here's a rough example:
by "copying", I'm assuming you mean to another table with the same structure...here i'm assuming a different database, but on the same server:
the advantage is this works for one parent and all it's children, or millions of parent records and all their children, and it performs much better than any cursor.
USE DEVELOPER --i'm assuming a different database
SET IDENTITY_INSERT PARENTTABLE ON
INSERT INTO PARENTTABLE(PARENTID,COL1,COL2,ETC)
SELECT PARENTID,COL1,COL2,ETC
FROM PRODUCTION.dbo.PARENTTABLE
WHERE DATEFIELD > (GETDATE() -180) --gotta decide which records
SET IDENTITY_INSERT PARENTTABLE OFF
SET IDENTITY_INSERT CHILDTABLE ON
INSERT INTO PARENTTABLE(CHILDID,PARENTID,COL1,COL2,ETC)
SELECT CHILDID,PARENTID,COL1,COL2,ETC
FROM PRODUCTION.dbo.CHILDTABLE
WHERE PARENTID
IN(SELECT PARENTID
FROM PRODUCTION.dbo.PARENTTABLE
WHERE DATEFIELD > (GETDATE() -180) )
SET IDENTITY_INSERT CHILDTABLE OFF
--repeat for each table
Lowell
July 21, 2009 at 6:08 am
Thank you for the reply
This solution will not work in my case since each child table has other children tables connected to it
So my database is arranged as a tree of many levels, each table had an ID column and a ParentID column that links it to the parent table
Check the attached diagram please. I want to copy a record from tblBOQ to the same table with all the related records
July 21, 2009 at 6:33 am
yes, it will still work, you just have to follow all of the nodes of the tree:
for example, say my "Childtable" has FK records to it:
it might not be obvious, but the Grandchild table needs the ChildIDs which still go out to the ParentID.
SET IDENTITY_INSERT GRANDCHILDTABLE ON
INSERT INTO PARENTTABLE(GRANDCHILDID,CHILDID,COL1,COL2,ETC)
SELECT GRANDCHILDID,CHILDID,COL1,COL2,ETC
FROM PRODUCTION.dbo.GRANDCHILDTABLE
WHERE CHILDID
IN(SELECT CHILDID FROM CHILDTABLE WHERE PARENTID IN(SELECT PARENTID
FROM PRODUCTION.dbo.PARENTTABLE
WHERE DATEFIELD > (GETDATE() -180) ) )
SET IDENTITY_INSERT GRANDCHILDTABLE OFF
Lowell
July 21, 2009 at 6:46 am
if you can provide the actual CREATE TABLE statements for the tables in question, we could help you better;
sp_fkeys tblBOQ would give all the dependant tables to the table(is that the parent table?)
with the exception of grabbing metadata, I haven't never a solution that can be done in cursor that can not be done faster in a set based operation. when it comes to data, set based operations are the best practice.
Lowell
July 21, 2009 at 7:56 am
The database has many levels
I've attached a script for recreating the database
Did you check the last attachment?
July 21, 2009 at 8:06 am
Personally, I think Lowell has given you what you need to develop a set-based process for copying the records needed from one set of tables to another. I think it would be better to see what you put together instead of relying on members of SSC to do it for you.
Show us what you come up with and we'll see if it can be improved or not.
July 21, 2009 at 8:40 am
Scripting this out is not all that difficult; I slapped together the examples just from the top of my head...you just have to do it one piece at a time....the devil is in the details.
Lynn is absolutely right when he said you've got to put in some effort on this, or hire a consultant to do it for you; an internet forum only helps with concepts and examples.
From the data you provided, I see you use a database named [CostControlSQL].
where is the data you want to copy getting copied to? where it the actual destination? another table? another schema? another database? another server?
I'll put together an example based on your schema for parent-child-grandchild so you can grasp the concept, but you originally said 6 tables, and the schema you provided has 27 tables....
you are not really expecting someone to code those 27 insert examples for you, are you? and you were going to try and do that in a cursor, I assume because you are a little more familiar with row by row processing?
Lowell
July 21, 2009 at 8:40 am
Well, your answer made me look like a complete (grasshopper)!!
The code that he gave to me thankfully will not do the job
The following steps should take place, which I don't think are possible to implement without record sets and cursors:
Read the record to be copied
Insert it into the same table
Get the identity column value
foreach record in child1 where parentid = identity of parent record
{
insert new record in child1
get the identity column
foreach record in child2 where parentid = identity of child1 record
{
insert new record in child2
get the identity column
foreach record in child3 where parentid = identity of child2 record
{
insert new record in child3
get the identity column
.
.
.
.
}
}
}
July 21, 2009 at 8:42 am
Lowell (7/21/2009)
Scripting this out is not all that difficult; I slapped together the examples just from the top of my head...you just have to do it one piece at a time....the devil is in the details.Lynn is absolutely right when he said you've got to put in some effort on this, or hire a consultant to do it for you; an internet forum only helps with concepts and examples.
From the data you provided, I see you use a database named [CostControlSQL].
where is the data you want to copy getting copied to? where it the actual destination? another table? another schema? another database? another server?
I'll put together an example based on your schema for parent-child-grandchild so you can grasp the concept, but you originally said 6 tables, and the schema you provided has 27 tables....
you are not really expecting someone to code those 27 insert examples for you, are you? and you were going to try and do that in a cursor, I assume because you are a little more familiar with row by row processing?
1- I'm copying in the same tables
2- I'm not looking for somebody to code for me, I'm just looking for the concept
Please check the post above
July 21, 2009 at 10:13 am
Ahmed Yarub Hani (7/21/2009)
Well, your answer made me look like a complete (grasshopper)!!The code that he gave to me thankfully will not do the job
The following steps should take place, which I don't think are possible to implement without record sets and cursors:
Read the record to be copied
Insert it into the same table
Get the identity column value
foreach record in child1 where parentid = identity of parent record
{
insert new record in child1
get the identity column
foreach record in child2 where parentid = identity of child1 record
{
insert new record in child2
get the identity column
foreach record in child3 where parentid = identity of child2 record
{
insert new record in child3
get the identity column
.
.
.
.
}
}
}
Look into the OUTPUT command (new so 2005). It will allow you to do the insert in bulk like has been pointed out to you, but still get all of the new identity values back.
I have to agree with Lynn and Lowell - no need to use a cursor for this. You will end up with a MUCH better result if you steer clear of the cursor concept.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 21, 2009 at 10:18 am
Okay, help us help you. Read the first article I reference below in my signature block.
Provide us with the DDL for the tables, sample data for the tables, expected results, and what you have done so far to solve your problem.
To make things easier, keep it to 2 or 3 tables. The concepts used there would be easy to expand to more tables.
July 24, 2009 at 10:21 pm
After spending 8 hours, 1 hour planning and 7 hours doing the same routine task, I finished and tested the procedure using cursors
This is an excerpt of copying three tables at different levels:
USE [CostControlSQL]
GO
/****** Object: StoredProcedure [dbo].[subCopyBOQ] Script Date: 07/25/2009 08:15:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[subCopyBOQ]
@ChildID int
AS
DECLARE @BOQID int,
@GrandchildID int,
@TempFetch int
BEGIN
SET NOCOUNT ON;
INSERT INTO tblBOQ (Project,Notes,JobNo,Shortname,Locked) SELECT Project,Notes,JobNo,Shortname,0 FROM tblBOQ WHERE ID=@ChildID
SET @BOQID = @@IDENTITY
DECLARE curBOQ CURSOR FOR
SELECT ID FROM tblBOQ_Sections WHERE BOQID=@ChildID;
OPEN curBOQ
FETCH curBOQ INTO @GrandchildID
SET @TempFetch=@@Fetch_Status
IF @TempFetch = 0 EXECUTE subCopyBOQSections @GrandchildID, @BOQID
WHILE @TempFetch = 0
BEGIN
FETCH curBOQ INTO @GrandchildID
SET @TempFetch=@@Fetch_Status
IF @TempFetch = 0 EXECUTE subCopyBOQSections @GrandchildID, @BOQID
END
CLOSE curBOQ
DEALLOCATE curBOQ
DECLARE curBOQ CURSOR FOR
SELECT ID FROM tblMachinery WHERE ParentID=@ChildID;
OPEN curBOQ
FETCH curBOQ INTO @GrandchildID
SET @TempFetch=@@Fetch_Status
IF @TempFetch = 0 EXECUTE subCopyMachinery @GrandchildID, @BOQID
WHILE @TempFetch = 0
BEGIN
FETCH curBOQ INTO @GrandchildID
SET @TempFetch=@@Fetch_Status
IF @TempFetch = 0 EXECUTE subCopyMachinery @GrandchildID, @BOQID
END
CLOSE curBOQ
DEALLOCATE curBOQ
DECLARE curBOQ CURSOR FOR
SELECT ID FROM tblMonthlySpendings WHERE ParentID=@ChildID;
OPEN curBOQ
FETCH curBOQ INTO @GrandchildID
SET @TempFetch=@@Fetch_Status
IF @TempFetch = 0 EXECUTE subCopyMonthlySpendings @GrandchildID, @BOQID
WHILE @TempFetch = 0
BEGIN
FETCH curBOQ INTO @GrandchildID
SET @TempFetch=@@Fetch_Status
IF @TempFetch = 0 EXECUTE subCopyMonthlySpendings @GrandchildID, @BOQID
END
CLOSE curBOQ
DEALLOCATE curBOQ
END
Which calls the procedure:
USE [CostControlSQL]
GO
/****** Object: StoredProcedure [dbo].[subCopyBOQSections] Script Date: 07/25/2009 08:16:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[subCopyBOQSections]
@ChildID int,
@ParentID int
AS
DECLARE @SectionID int,
@GrandchildID int,
@TempFetch int
BEGIN
SET NOCOUNT ON;
INSERT INTO tblBOQ_Sections (BOQID,[Order],[SectionName],[SectionAmount],[Notes]) SELECT @ParentID,[Order],[SectionName],[SectionAmount],[Notes] FROM tblBOQ_Sections WHERE ID=@ChildID
SET @SectionID = @@IDENTITY
DECLARE curItems CURSOR FOR
SELECT ID FROM tblBOQ_Items WHERE SectionID=@ChildID;
OPEN curItems
FETCH curItems INTO @GrandchildID
SET @TempFetch=@@Fetch_Status
IF @TempFetch = 0 EXECUTE subCopyBOQItems @GrandchildID, @SectionID
WHILE @TempFetch = 0
BEGIN
FETCH curItems INTO @GrandchildID
SET @TempFetch=@@Fetch_Status
IF @TempFetch = 0 EXECUTE subCopyBOQItems @GrandchildID, @SectionID
END
CLOSE curItems
DEALLOCATE curItems
END
Which keeps on calling child tables until it reaches:
USE [CostControlSQL]
GO
/****** Object: StoredProcedure [dbo].[subCopyLPOItems] Script Date: 07/25/2009 08:17:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[subCopyLPOItems]
@ChildID int,
@ParentID int
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO tblLPO_Items (LPOID,[CodeNo],[Description],Quantity,UnitPrice,Unit,Amount,LPORef) SELECT @ParentID,[CodeNo],[Description],Quantity,UnitPrice,Unit,Amount,LPORef FROM tblLPO_Items WHERE ID=@ChildID
END
Please reference the tree structure in previous posts
July 25, 2009 at 10:05 am
I think that I provided all the required info
Can you give me any hint on implementing the same code using sets only?
July 25, 2009 at 11:52 am
Ahmed Yarub Hani (7/25/2009)
I think that I provided all the required infoCan you give me any hint on implementing the same code using sets only?
What is the performance of your cursor based solution?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply