How to write loop in sql - 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

SQL Server 2005 Forums

Transact-SQL (2005)

How to write loop in sql

Author

Topic

Avinash
Starting Member

5 Posts

Posted - 2007-12-11 : 05:38:04

Hi Experts,

I have two database in same server I.e. D1 and D2. D1 has one table T1 and D2 has one table T2. T1 and T2 is having same table structure and same columns. But D1 is online database and D2 is offline database.D1 will be updating regularly but for D2 I have to update once in day. In T1 and T2 we have column Time_created (it will capture the time when data is created) and Time_modified (it will capture the time when data is modified)

I have to write a query that will take max(time_created) from T2 and compare with each row of T1 anf IF time_created > max(time_created) of T2 then it should insert the that particular row in T2 ELSE it should check Time_modified of that row if it s within 24 hour than it should update that row in T2.

I have to apply below logic:

Select max(Time_created) from D2.username.T2 as R
IF (Time_created.D1 > R)
Insert row in T2.D2
Else IF(Time_modified is within 12 hour)
Update row in T2.D2

But if else will check only for one row.How can I write above in Loop.

Please suggest.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-11 : 07:18:16

Do you have a primary key in the table ? Why not use it to check ? How do you intend to do if the server was down for more than 12 hours ? using last 12 hours of time_created will surely missed records created earlier.


insert into D2..T2( . . . )
select . . .
from   D1..T1 t1 left join D2..T2 t2
       on t1.pk = t2.pk
where  t2.pk is null
and    t1.time_created >= dateadd(hour, datediff(hour, 0, getdate()), -12)

update t2
set    . . . 
       . . .
from   D1..T1 t1 left join D2..T2 t2
       on t1.pk = t2.pk
where  t1.time_modified >= dateadd(hour, datediff(hour, 0, getdate()), -12)

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-11 : 11:59:24

I think you can achieve the same result by means of a set based operation.
Using your logic, Get the pks of all records from T1 onto temp table where time_created > max(time_created) from T2 or time_modified is within 24 hours. Now use this temp table for updating/inserting to T2 using left outer join.
i.e
CREATE table #temp
(
PKcolumn
)
Declare MaxTime datetime

SET MaxTime=MAX(Time_created) from T2

INSERT #temp
SELECT {pk col}
FROM T1
WHERE T1.Time_created >MaxTime
OR T1.Time_modified >dateadd(d,-1,getdate())

--inserting newly created once this wont have matching recs in T2
INSERT INTO T2
SELECT *
FROM T1 t1
INNER JOIN #temp #t
ON #t.{pkcol}=t1.{pkcol}
LEFT OUTER JOIN T2 t2
ON t2.{pkcol}=t1.{pkcol}
WHERE t2.{pkcol} IS NULL

--updating existing T2 ones
UPDATE t2
SET t2.{all fileds}=t1.{allfields}
FROM T1 t1
INNER JOIN #temp #t
ON #t.{pkcol}=t1.{pkcol}
LEFT OUTER JOIN T2 t2
ON t2.{pkcol}=t1.{pkcol}
WHERE t2.{pkcol} IS NOT NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-11 : 23:02:33

Forgot to add...You might have to refer T1 as {dbname}..T1 when you are executing this on T2 table's db...

Avinash
Starting Member

5 Posts

Posted - 2007-12-12 : 10:10:45

Thanks Vikash and Khtan for your solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources