MS SQL copy new and modified rows from TABLE1 to TABLE2

#11

On 17 Aug, 08:11, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Yas(yas... (AT) gmail (DOT) com) writes:
DOH!!! what a silly mistake. :-)

It's often that when you work with something you are not really confident
that you look for the difficult mistakes and overlook the simple typos.

do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?

Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
as I think that expresses more clearly what is going on. But that's a matter
of taste.

Thanks for all your advise and help! by the way do you if there is a
way to edit/change a Trigger once it has been created in MS SQL?

Yas

#12

On Fri, 17 Aug 2007 19:47:32 -0700, Yas wrote:

Quote:

On 17 Aug, 08:11, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Yas(yas... (AT) gmail (DOT) com) writes:
DOH!!! what a silly mistake. :-)

It's often that when you work with something you are not really confident
that you look for the difficult mistakes and overlook the simple typos.

do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?

Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
as I think that expresses more clearly what is going on. But that's a matter
of taste.

Thanks for all your advise and help! by the way do you if there is a
way to edit/change a Trigger once it has been created in MS SQL?

Hi Yas,

Yes. Simply use ALTER TRIGGER instead of CREATE TRIGGER.

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

#13

On 17 Aug, 09:11, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

Yas(yas... (AT) gmail (DOT) com) writes:
DOH!!! what a silly mistake. :-)

It's often that when you work with something you are not really confident
that you look for the difficult mistakes and overlook the simple typos.

do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?

Looks good to me. I would have used NOT EXISTS rather than the LEFT JOIN,
as I think that expresses more clearly what is going on. But that's a matter
of taste.

Thanks. Just out of curiosity how would you modify the above to use
NOT EXISTS ?

cheers
Yas

#14

On Aug 16, 2:14 pm, Yas <yas... (AT) gmail (DOT) com> wrote:

Quote:

On 16 Aug, 13:46, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

The first question is why do you want to do this in the first place? It
seems funny that you would want to have two identical tables in the same
database? Or ar the tables in different databases on different servers?

Hi, sorry perhaps I should have been a bit more clear. Well, Table2 is
essentially a Master table that will have a record of all users that
were ever added to Table1. So even if at a later date userA and userB
were removed from Table1, a record of UserA and UserB will always be
there in Table2.

So yes right now Table1 and 2 are identical and that seems
pointless...however soon Table2 will be different in that it will have
a record of rows that are no longer present in Table1. I'm keeping
track of them via another method which checks if a row has been
removed from Table1 if so it adds the date of removal to a column of
that row in Table2. This is why I dont want to update Table2 if a row
is removed in Table1...only if a new row is added or an existing one
modified.

I hope that explains what I'm trying to do :-) can I still use
Triggers to do this?

If the tables are on the same server, a trigger would be the best way
to do it.

Yes, they are on the same server and in the same Database.

Couldn't you just use one table and add column use as a DELETED flag
to logically delete a user so the physical row is still there?

#15

On Tue, 21 Aug 2007 01:29:46 -0700, Yas <yasar1 (AT) gmail (DOT) com> wrote:

Quote:

INSERT INTO
Table2(STATUS,attribute15,email,lastname1,lastname 2,name,company,startDate)
SELECT 'Active' AS STATUS, b.Attribute15, b.email, b.lastname1,
b.lastname2, b.name,
b.company, b.startDate
FROM Inserted b LEFT OUTER JOIN
Table2 a ON b.Attribute15 = a.Attribute15
WHERE a.Attribute15 IS NULL

Just out of curiosity how would you modify the above to use
NOT EXISTS ?

INSERT INTO Table2
(STATUS,attribute15,email,
lastname1,lastname2,name,
company,startDate)
SELECT 'Active' AS STATUS, b.Attribute15, b.email,
b.lastname1, b.lastname2, b.name,
b.company, b.startDate
FROM Inserted b
WHERE NOT EXISTS
(SELECT * FROM Table2 a
WHERE b.Attribute15 = a.Attribute15)

Roy Harvey
Beacon Falls, CT

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

MS SQL copy new and modified rows from TABLE1 to TABLE2

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2 - 08-17-2007 , 09:47 PM

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2 - 08-18-2007 , 01:25 AM

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2 - 08-21-2007 , 03:29 AM

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2 - 08-21-2007 , 04:25 AM

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2 - 08-21-2007 , 06:01 AM