stored procedure..

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kdsv - 08 Jul 2008 23:22 GMT

Dear Friends,

I would like to create a stored procedure that loops thru table a and
inserts values in table b.

table a has the following parent child relations.

TABLE A

identity column
id    parent_id    description
1    0    test1
2    1    test2
3    1    test3
4    2    test4
5    2    test5
6    3    test6
7    3    test7
8    5    test8
9    6    test9
10    9    test10

here, id 1 has 2 childs and id 2 has 2 childs and id 3 has 2 childs,
but id 4 has no childs, id 5 has one child and so on.

Now I want to loop thru the table a and insert into table b which is
exactly same as table a, but with different identity values and
different parents based on created identity values, because every time
a row is inserted in to table b gets new identity value. that identity
value should be available to child records.

so the new table will look like the following.

TABLE B

identity column
id    parent_id    description
new id 1    0    test1
new id 2    new id 1    test2
new id 3    new id 1    test3
new id 4    new id 2    test4
new id 5    new id 2    test5
new id 6    new id 3    test6
new id 7    new id 3    test7
new id 8    new id 5    test8
new id 9    new id 6    test9
new id 10    new id 9    test10

so basically I want this to be created based on table a maintaining
same relations.
so in table a it has
id 1 has 2 childs and id 2 has 2 childs and id 3 has 2 childs, but id
4 has no childs, id 5 has one child and
and table b has the same things..

Your help is really appreciated.

Thanks,
kdsv

Reply to this Message

John Bell - 09 Jul 2008 07:40 GMT

> Dear Friends,
>
[quoted text clipped - 55 lines]
> Thanks,
> kdsv

Hi

Please post your table definitions as DDL and the example data as inserts,
this will reduce the amount of work for anyone answering the question

CREATE TABLE tableA ( id int not null identity,
parent_id int not null,
description varchar(30) )

INSERT INTO tableA ( parent_id, description )
SELECT 0, 'test1'
UNION ALL SELECT 1, 'test2'
UNION ALL SELECT 1, 'test3'
UNION ALL SELECT 2, 'test4'
UNION ALL SELECT 2, 'test5'
UNION ALL SELECT 3, 'test6'
UNION ALL SELECT 3, 'test7'
UNION ALL SELECT 5, 'test8'
UNION ALL SELECT 6, 'test9'
UNION ALL SELECT 9, 'test10'

SELECT * FROM tableA
GO

-- by adding the old id column you will be able to to a single update to
correct the parents
CREATE TABLE tableB ( id int not null identity (100,1),
old_id int not null,
parent_id int not null,
description varchar(30) )

INSERT INTO tableb ( old_id, parent_id, description )
SELECT id, parent_id, description
FROM tableA

SELECT * FROM tableB
GO
/*
id old_id parent_id description
----------- ----------- ----------- ------------------------------
100 1 0 test1
101 2 1 test2
102 3 1 test3
103 4 2 test4
104 5 2 test5
105 6 3 test6
106 7 3 test7
107 8 5 test8
108 9 6 test9
109 10 9 test10
*/
UPDATE B
SET parent_id = C.id
FROM TABLEB B
JOIN TABLEB C ON c.old_id = b.parent_id

SELECT * FROM tableB
GO
/*
id old_id parent_id description
----------- ----------- ----------- ------------------------------
100 1 0 test1
101 2 100 test2
102 3 100 test3
103 4 101 test4
104 5 101 test5
105 6 102 test6
106 7 102 test7
107 8 104 test8
108 9 105 test9
109 10 108 test10
*/

John

Reply to this Message

Hugo Kornelis - 09 Jul 2008 21:42 GMT

>Dear Friends,
>
>I would like to create a stored procedure that loops thru table a and
>inserts values in table b.

Hi kdsv,

It's almost always more efficient to process sets at a time that loop
through rows and process a single row at a time.

(snip)

>Now I want to loop thru the table a and insert into table b which is
>exactly same as table a, but with different identity values and
>different parents based on created identity values, because every time
>a row is inserted in to table b gets new identity value. that identity
>value should be available to child records.

Why do you copy the rows in a second table? Most database design is
aimed at REDUCING redundant storage of data, not about adding to it.

Also, why not remove the identity attributte from the second table and
just keep all the values as they currently are?

(snip)

>Your help is really appreciated.

If you really need to allow for changed identity values, you'll have to
use the table's real(*) key to match "old" and "new" identity values.

Real, as opposed to surrogate key. An identity is a surrogate key and
should never be the only key of a table. Check the logical data model to
find the logical key. The identity column is added later, during
implementation, for performance reasons.

Signature

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Reply to this Message