MS SQL copy new and modified rows from TABLE1 to TABLE2

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

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

Yas (yasar1@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

On 16 Aug, 13:46, Erland Sommarskog 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.

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

Yas (yasar1@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

On 16 Aug, 15:16, Erland Sommarskog wrote:
> 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 :-)

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

On Thu, 16 Aug 2007 08:13:47 -0700, Yas wrote:

>> 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

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

On 16 Aug, 17:48, Roy Harvey wrote:
> On Thu, 16 Aug 2007 08:13:47 -0700, Yas wrote:
> >> 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,lastname2,name,com pany,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

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

Yas (yasar1@gmail.com) writes:
> CREATE TRIGGER my_Trigger ON [dbo].[Table2]
> FOR INSERT
> AS
>
> INSERT INTO
> Table2(STATUS,attribute15,email,lastname1,lastname2,name,com pany,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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

On 16 Aug, 22:58, Erland Sommarskog wrote:
> Yas (yas...@gmail.com) writes:
> > CREATE TRIGGER my_Trigger ON [dbo].[Table2]
> > FOR INSERT
> > AS
>
> > INSERT INTO
> > Table2(STATUS,attribute15,email,lastname1,lastname2,name,com pany,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

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

Yas (yasar1@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

On 17 Aug, 08:11, Erland Sommarskog wrote:
> 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

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

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

>On 17 Aug, 08:11, Erland Sommarskog wrote:
>> 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

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

On 17 Aug, 09:11, Erland Sommarskog wrote:
> 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

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

On Aug 16, 2:14 pm, Yas wrote:
> On 16 Aug, 13:46, Erland Sommarskog 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?

Re: MS SQL copy new and modified rows from TABLE1 to TABLE2

On Tue, 21 Aug 2007 01:29:46 -0700, Yas wrote:

> INSERT INTO
> Table2(STATUS,attribute15,email,lastname1,lastname2,name,com pany,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