MS SQL copy new and modified rows from TABLE1 to TABLE2

[Yas]

Hello,

I have 2 tables, Table1 and Table2. I have copied all data from Table1
to Table2.
However Table1 is dynamic it has new rows added and some old rows
modified everyday or every other day...
How can I continue to keep Table2 up to date without always having to
copy everything from Table1?

Basically from now on I would only like to copy new rows or modified
rows in Table1 to Table2 and skip rows that are already present and
have not been modified in Table1. I would like to not do anything for
any rows that were removed in Table1 and continue to keep a copy of
them in Table2.
Is using a DTS package the best way to automate this update of Table2
to make sure Table2 is always up-to-date with Table1?
Thanks for any help or advise :-)

Yas

[Erland Sommarskog]

Yas (ya****@gmail.com) writes:

I have 2 tables, Table1 and Table2. I have copied all data from Table1
to Table2.
However Table1 is dynamic it has new rows added and some old rows
modified everyday or every other day...
How can I continue to keep Table2 up to date without always having to
copy everything from Table1?

Basically from now on I would only like to copy new rows or modified
rows in Table1 to Table2 and skip rows that are already present and
have not been modified in Table1. I would like to not do anything for
any rows that were removed in Table1 and continue to keep a copy of
them in Table2.
Is using a DTS package the best way to automate this update of Table2
to make sure Table2 is always up-to-date with Table1?

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?

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

If tbe tables are on different server, triggers are still possible, but
if the remote server is unavailable, this would cause the operation on
the source table to fail. In this case, replication may be a way to go.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Yas]

On 16 Aug, 13:46, Erland Sommarskog <[e-mail]:

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.

[Erland Sommarskog]

Yas (ya****@gmail.com) writes:

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?

Since the tables are in the same database, triggers is definitely the
way to go.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Yas]

On 16 Aug, 15:16, Erland Sommarskog <[e-mail]:

Yas ([e-mail]) writes:

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?

Since the tables are in the same database, triggers is definitely the
way to go.

Thanks. This is what I'm trying to do now... do you know how I can
refer to the row that has just been added or modified?

In Table1 I have...

CREATE TRIGGER (tr_updateMaster) ON dbo.Table2
FOR INSERT, UPDATE
AS
Here I would like to put something like...
Insert into Table2 new row + 2 extra columns (status and date)
AND/OR
Update modified row in dbo.Table2 with different values in Table1
Thanks again :-)

[Roy Harvey]

On Thu, 16 Aug 2007 08:13:47 -0700, Yas <ya****@gmail.comwrote:

>Since the tables are in the same database, triggers is definitely the
way to go.

Thanks. This is what I'm trying to do now... do you know how I can
refer to the row that has just been added or modified?

Read up on the INSERTED and DELETED virtual tables that are available
to triggers.

Roy Harvey
Beacon Falls, CT

[Yas]

On 16 Aug, 17:48, Roy Harvey <[e-mail]:

On Thu, 16 Aug 2007 08:13:47 -0700, Yas <[e-mail]:

Since the tables are in the same database, triggers is definitely the
way to go.

Thanks. This is what I'm trying to do now... do you know how I can
refer to the row that has just been added or modified?

Read up on the INSERTED and DELETED virtual tables that are available
to triggers.

Hi I'm trying the following for INSERT trigger attached to Table1 but
it doesn't seem to work in that it doesn't insert the new rows into
Table2 from Table1

CREATE TRIGGER my_Trigger ON [dbo].[Table2]
FOR INSERT
AS

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
GO

The syntax according to MS SQL server is correct but nothing happens
when a new row is inserted into Table1.

The idea here is basically when a new row is inserted in Table1, the
above insert command is run and the new row copied over to Table2

Any help?

Thanks in advance

[Erland Sommarskog]

Yas (ya****@gmail.com) writes:

CREATE TRIGGER my_Trigger ON [dbo].[Table2]
FOR INSERT
AS

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
GO

The syntax according to MS SQL server is correct but nothing happens
when a new row is inserted into Table1.

Well, the code you posted is a trigger on Table2, so...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Yas]

On 16 Aug, 22:58, Erland Sommarskog <[e-mail]:

Yas ([e-mail]) writes:

CREATE TRIGGER my_Trigger ON [dbo].[Table2]
FOR INSERT
AS

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
GO

The syntax according to MS SQL server is correct but nothing happens
when a new row is inserted into Table1.

Well, the code you posted is a trigger on Table2, so...

DOH!!! what a silly mistake. :-) do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?

Thanks agian

[Erland Sommarskog]

Yas (ya****@gmail.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.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Yas]

On 17 Aug, 08:11, Erland Sommarskog <[e-mail]:

Yas([e-mail]) 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

[Hugo Kornelis]

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

>On 17 Aug, 08:11, Erland Sommarskog <[e-mail]:

>Yas([e-mail]) 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

[Yas]

On 17 Aug, 09:11, Erland Sommarskog <[e-mail]:

Yas([e-mail]) 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

[stephen]

On Aug 16, 2:14 pm, Yas <[e-mail]:

On 16 Aug, 13:46, Erland Sommarskog <[e-mail]:

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?

[Roy Harvey]

On Tue, 21 Aug 2007 01:29:46 -0700, Yas <ya****@gmail.comwrote:

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