Table Manipulation Issue

30 Sep 2005 9:48 AM

thomson

Hi all,
         i do have 3 tables with me

FIRST TABLE
     fid code description
      12    asc   newfor

SECOND TABLE
      sid FID code description
       20   12 FD    NEWDESC
THIRD TABLE

       TID SID CODE DESC PARENT_TID
        1   20 HJ   HJ   NULL
        2   20 LK   FD    1

My issue is In the front end if i give Copy it should copy the entire
three tables to some other three tables with same schema

I dont have any issues manipulating the first and second because iam
getting the identity seed value so, i will be able to insert it,

but when it comes to the third table i do have something known as
Parent_TId, iam not able to manipulate this.

Thanks in advance

thomson

30 Sep 2005 10:17 AM

David Portas

Please post DDL, sample data and required end results otherwise we can
only guess at your requirements. I'm guessing that parent_tid is a
self-referencing foreign key and that tid is an IDENTITY column. What I
cannot guess is what alternate key(s) exist in that table and that's
important to solving your apparent problem.

In my view IDENTITY is not a good choice for a self-referencing foreign
key because of the problem it creates with INSERTs. Use another key
instead. If you must use IDENTITY and you have a nullable foreign key
then one workaround is with an INSERT followed by an UPDATE. DDL and
sample data follows.

CREATE TABLE foo (foo_key INTEGER PRIMARY KEY, alt_key CHAR(10) NOT
NULL UNIQUE, parent_key INTEGER NULL REFERENCES foo (foo_key)) ;

INSERT INTO foo (foo_key, alt_key, parent_key) VALUES (1,'Alpha', NULL)
;
INSERT INTO foo (foo_key, alt_key, parent_key) VALUES (2,'Beta', 1) ;

CREATE TABLE bar (bar_key INTEGER IDENTITY(1000,1) PRIMARY KEY, alt_key
CHAR(10) NOT NULL UNIQUE, parent_key INTEGER NULL REFERENCES bar
(bar_key)) ;

Now populate Bar with Foo and update the foreign key:

INSERT INTO bar (alt_key)
SELECT alt_key
FROM foo ;

UPDATE bar
SET parent_key =
(SELECT B.bar_key
   FROM foo AS F1
   JOIN foo AS F2
    ON F1.parent_key = F2.foo_key
     AND F1.alt_key = bar.alt_key
   JOIN bar AS B
    ON F2.alt_key = B.alt_key) ;

Hope this helps, even though it is just a guess.

--
David Portas
SQL Server MVP
--

30 Sep 2005 11:57 AM

R.D

Create Primary key to last table, If I understand you correctly what sort of
copying you are doing.
Post DDL/Sample data/script that you are using.
Regards
R.D

Show quote

"David Portas" wrote:

> Please post DDL, sample data and required end results otherwise we can
> only guess at your requirements. I'm guessing that parent_tid is a
> self-referencing foreign key and that tid is an IDENTITY column. What I
> cannot guess is what alternate key(s) exist in that table and that's
> important to solving your apparent problem.
>
> In my view IDENTITY is not a good choice for a self-referencing foreign
> key because of the problem it creates with INSERTs. Use another key
> instead. If you must use IDENTITY and you have a nullable foreign key
> then one workaround is with an INSERT followed by an UPDATE. DDL and
> sample data follows.
>
> CREATE TABLE foo (foo_key INTEGER PRIMARY KEY, alt_key CHAR(10) NOT
> NULL UNIQUE, parent_key INTEGER NULL REFERENCES foo (foo_key)) ;
>
> INSERT INTO foo (foo_key, alt_key, parent_key) VALUES (1,'Alpha', NULL)
> ;
> INSERT INTO foo (foo_key, alt_key, parent_key) VALUES (2,'Beta', 1) ;
>
> CREATE TABLE bar (bar_key INTEGER IDENTITY(1000,1) PRIMARY KEY, alt_key
> CHAR(10) NOT NULL UNIQUE, parent_key INTEGER NULL REFERENCES bar
> (bar_key)) ;
>
> Now populate Bar with Foo and update the foreign key:
>
> INSERT INTO bar (alt_key)
> SELECT alt_key
>   FROM foo ;
>
> UPDATE bar
> SET parent_key =
>   (SELECT B.bar_key
>    FROM foo AS F1
>    JOIN foo AS F2
>     ON F1.parent_key = F2.foo_key
>      AND F1.alt_key = bar.alt_key
>    JOIN bar AS B
>     ON F2.alt_key = B.alt_key) ;
>
> Hope this helps, even though it is just a guess.
>
> --
> David Portas
> SQL Server MVP
> --
>
>