469,623 Members | 1,831 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

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

Aug 16 '07 #1
14 6769
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
Aug 16 '07 #2
Yas
On 16 Aug, 13:46, Erland Sommarskog <esq...@sommarskog.sewrote:
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.

Aug 16 '07 #3
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
Aug 16 '07 #4
Yas
On 16 Aug, 15:16, Erland Sommarskog <esq...@sommarskog.sewrote:
Yas (yas...@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.
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 :-)


Aug 16 '07 #5
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
Aug 16 '07 #6
Yas
On 16 Aug, 17:48, Roy Harvey <roy_har...@snet.netwrote:
On Thu, 16 Aug 2007 08:13:47 -0700, Yas <yas...@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.
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

Aug 16 '07 #7
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
Aug 16 '07 #8
Yas
On 16 Aug, 22:58, Erland Sommarskog <esq...@sommarskog.sewrote:
Yas (yas...@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...
DOH!!! what a silly mistake. :-) do you think apart from that its fine
for inserting new rows into Table2 from Table1 trigger?

Thanks agian

Aug 16 '07 #9
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
Aug 17 '07 #10
Yas
On 17 Aug, 08:11, Erland Sommarskog <esq...@sommarskog.sewrote:
Yas(yas...@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.
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

Aug 18 '07 #11
On Fri, 17 Aug 2007 19:47:32 -0700, Yas wrote:
>On 17 Aug, 08:11, Erland Sommarskog <esq...@sommarskog.sewrote:
>Yas(yas...@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.

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
Aug 18 '07 #12
Yas
On 17 Aug, 09:11, Erland Sommarskog <esq...@sommarskog.sewrote:
Yas(yas...@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.
Thanks. Just out of curiosity how would you modify the above to use
NOT EXISTS ?

cheers
Yas

Aug 21 '07 #13
On Aug 16, 2:14 pm, Yas <yas...@gmail.comwrote:
On 16 Aug, 13:46, Erland Sommarskog <esq...@sommarskog.sewrote:
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?

Aug 21 '07 #14
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
Aug 21 '07 #15

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Kevin Gale | last post: by
reply views Thread by JC-Atl | last post: by
4 posts views Thread by Geoff Jones | last post: by
2 posts views Thread by Devlei | last post: by
2 posts views Thread by jogisarge | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.