Need stored proc. Add record to Tbl2 if it exists in Tbl1 and not

2 Sep 2006 8:57 PM

Sandy

Hello -

I have two tables. Tbl1 gets updated automatically. I would like to update
Tbl2 if a record exists in Tbl1 that is not in Tbl2 and delete a record from
Tbl2 if it is not in Tbl1. I am only concerned with one field -
Tbl1.FullName and Tbl2.FullName. They are both varchar(50).

How do I create a stored procedure to do this?

Any help will be greatly appreciated!

--
Sandy

2 Sep 2006 9:14 PM

David Portas

Sandy wrote:

> Hello -
>
> I have two tables. Tbl1 gets updated automatically. I would like to update
> Tbl2 if a record exists in Tbl1 that is not in Tbl2 and delete a record from
> Tbl2 if it is not in Tbl1. I am only concerned with one field -
> Tbl1.FullName and Tbl2.FullName. They are both varchar(50).
>
> How do I create a stored procedure to do this?
>
> Any help will be greatly appreciated!
> --
> Sandy

1. Easy answer:

CREATE VIEW tbl2 AS
SELECT fullname
FROM tbl1 ;

2. A less elegant alternative:

INSERT INTO tbl2 (fullname)
SELECT fullname
FROM tbl1
WHERE NOT EXISTS
   (SELECT *
    FROM tbl2
    WHERE tbl2.fullname = tbl1.fullname);

DELETE FROM tbl2
WHERE NOT EXISTS
   (SELECT *
    FROM tbl1
    WHERE tbl1.fullname = tbl2.fullname);

(untested)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

2 Sep 2006 9:56 PM

Sandy

Thank you very much, David! Works great!

--
Sandy

Show quote

"David Portas" wrote:

> Sandy wrote:
> > Hello -
> >
> > I have two tables. Tbl1 gets updated automatically. I would like to update
> > Tbl2 if a record exists in Tbl1 that is not in Tbl2 and delete a record from
> > Tbl2 if it is not in Tbl1. I am only concerned with one field -
> > Tbl1.FullName and Tbl2.FullName. They are both varchar(50).
> >
> > How do I create a stored procedure to do this?
> >
> > Any help will be greatly appreciated!
> > --
> > Sandy
>
> 1. Easy answer:
>
> CREATE VIEW tbl2 AS
> SELECT fullname
>   FROM tbl1 ;
>
>
> 2. A less elegant alternative:
>
> INSERT INTO tbl2 (fullname)
> SELECT fullname
>   FROM tbl1
>   WHERE NOT EXISTS
>    (SELECT *
>     FROM tbl2
>     WHERE tbl2.fullname = tbl1.fullname);
>
> DELETE FROM tbl2
>   WHERE NOT EXISTS
>    (SELECT *
>     FROM tbl1
>     WHERE tbl1.fullname = tbl2.fullname);
>
> (untested)
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>