sql script logic - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql script logic

Author

Topic

gudushen
Starting Member

16 Posts

Posted - 2008-10-24 : 10:18:15

Hi, I'm new to sql and is working on a stored project. I would just like to get an idea on how to approach this small project logically. So no need to give me actual code, just the steps you would normally take.
Basically, the script should compare 2 similar tables and if table A is newer than table B, one would combine 2 other tables to A (table C and D), and filter the results on a column in D. Then I need to update B with the filtered list of table A. (the procedure run daily)
My initial thought was to create another table and work from there, by my supervisor tells me that was not needed. So i'm kind of lost.
An help is appreciated.
thank you in advance.

ps. I have the following query currently, but it doesn't run

select distinct top 100 *
from tableA, tableB
where tableA.date_updated > tableB.date_updated
Inner Join tableC
on tableA.PK = tableC.PK
Inner Join tableD
on tableC.FK = tableD.PK
where tableD.Image_Required = "COM3" or tableD.Image_Required = "COM4"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 10:23:51

what do you mean by newer table? records exist on A not in B?

gudushen
Starting Member

16 Posts

Posted - 2008-10-24 : 10:25:49

quote:
Originally posted by visakh16

what do you mean by newer table? records exist on A not in B?

I mean if A is more updated than B, they should both contain the same records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 10:46:00

something like

IF (SELECT MAX(date_updated) from tableA) >(SELECT MAX(date_updated) from tableB)
begin
select distinct top 100 *
from tableA
Inner Join tableC
on tableA.PK = tableC.PK
Inner Join tableD
on tableC.FK = tableD.PK
where tableD.Image_Required = "COM3" or tableD.Image_Required = "COM4"
end

Update tableB
...

gudushen
Starting Member

16 Posts

Posted - 2008-10-24 : 11:12:37

quote:

Originally posted by visakh16

something like

IF (SELECT MAX(date_updated) from tableA) >(SELECT MAX(date_updated) from tableB)
begin
select distinct top 100 *
from tableA
Inner Join tableC
on tableA.PK = tableC.PK
Inner Join tableD
on tableC.FK = tableD.PK
where tableD.Image_Required = "COM3" or tableD.Image_Required = "COM4"
end

Update tableB
...

makes sense. but i explained it wrong, i meant that for each entry in table A that is more updated than the corresponding entry in table B.
A while loop perhaps?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-24 : 12:42:35

Are you using sql 2005?

gudushen
Starting Member

16 Posts

Posted - 2008-10-24 : 13:12:20

quote:
Originally posted by visakh16

Are you using sql 2005?

i'm using sql server 7.0

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-10-24 : 13:33:27

If same records needs to be exists in both the table A and B. then check directly with common column u are prefering for these two tables.If it exists in table A and but not exists in table B then insert directly.In case of updated compare what Visakh mentioned above condtion with respect to each records updated in table A.then update in table B.

malay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources