help with the script - 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)

help with the script

Author

Topic

deal11deal1
Starting Member

34 Posts

Posted - 2011-01-24 : 09:27:17

Hello every one ,
I have a script that was written by some 1 else in the company who no longer works here.My boss wanst to know exactly what this script means or doing , needless to say I am not that good in T-sql .
can some 1 please explain this script to me?? below is the script.

DELETE FROM DBO.PREMISES_BACKUP

WHERE facilitynumber IN

(SELECT DISTINCT facilitynumber

FROM [Hedgehog Inspector].dbo.vw_FacilityDetailCurrent a

WHERE a.LastUpdateDateTime >=

(SELECT LEFT(CAST(GETDATE() - 10 AS datetime), 11) + ' 00:00:00.001') AND a.LastUpdateDateTime =

(SELECT MAX(z.LastUpdateDateTime)

FROM [Hedgehog Inspector].dbo.vw_FacilityDetailCurrent z

WHERE a.facilitynumber = z.facilitynumber AND z.LastUpdateDateTime >=

(SELECT LEFT(CAST(GETDATE() - 10 AS datetime), 11) + ' 00:00:00.001')));

INSERT

INTO dbo.PREMISES_BACKUP(FacilityName, FacilityID, FacilityDetailID, Address, City, area1, PostalCode, Telephone, FacilityCategory,

FacilityStyle, InspectionFrequency, LastUPDATE, FacilityRating, Xcoord, Ycoord, TileNum, Region, ISTobacco, Multiple, Origin, SiteFrom,

SiteStreet, SiteDirection, RowStatus, Effectivedate, InsertionDate, DISplay, FacilityNumber)

SELECT FacilityName, TaxCertificate1, FacilityDetailID, NULL, SiteCity, Area1, SitePostalCode, SiteTelephone, FacilityCategory1,

FacilityCategoryStyle1, InspectionFrequency, LastUpdateDateTime, FacilityStatus1, NULL, NULL, NULL, NULL, 'N', 0, NULL,

SiteFrom, SiteStreet, SiteDirection, RowStatus, EffectiveDate, CreationDateTime, NULL, FacilityNumber

FROM [Hedgehog Inspector].dbo.vw_FacilityDetailCurrent a

WHERE a.LastUpdateDateTime >=

(SELECT LEFT(CAST(GETDATE() - 10 AS datetime), 11) + ' 00:00:00.001') AND a.LastUpdateDateTime =

(SELECT MAX(z.LastUpdateDateTime)

FROM [Hedgehog Inspector].dbo.vw_FacilityDetailCurrent z

WHERE a.facilitynumber = z.facilitynumber AND z.LastUpdateDateTime >=

(SELECT LEFT(CAST(GETDATE() - 10 AS datetime), 11) + ' 00:00:00.001'));

UPDATE dbo.PREMISES_BACKUP

SET Address = SiteStreet

WHERE SiteFrom IS NULL AND lastUPDATE >=

(SELECT LEFT(CAST(GETDATE() - 10 AS datetime), 11) + ' 00:00:00.001');

UPDATE dbo.PREMISES_BACKUP

SET Address = SiteStreet + ' ' + SiteDirection

WHERE SiteFrom IS NULL AND SiteDirection IS NOT NULL AND lastUPDATE >=

(SELECT LEFT(CAST(GETDATE() - 10 AS datetime), 11) + ' 00:00:00.001');

UPDATE dbo.PREMISES_BACKUP

SET Address = SiteFrom + ' ' + SiteStreet

WHERE SiteFrom IS NOT NULL AND lastUPDATE >=

(SELECT LEFT(CAST(GETDATE() - 10 AS datetime), 11) + ' 00:00:00.001');

UPDATE dbo.PREMISES_BACKUP

SET Address = SiteFrom + ' ' + SiteStreet + ' ' + SiteDirection

WHERE SiteFrom IS NOT NULL AND SiteDirection IS NOT NULL AND

lastUPDATE >=

(SELECT LEFT(CAST(GETDATE() - 10 AS datetime), 11) + ' 00:00:00.001');

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-24 : 10:20:20

Looks like it's adding/updating data in PREMISES_BACKUP which has been updated (in the current tables) later than
LEFT(CAST(GETDATE() - 10 AS datetime), 11) + ' 00:00:00.001')

It deletes data in the backup table updated in the current table later tan that datetime then adds everything from the current table (the delete is to prevent duplicates).
Then it does some updates of the backup data.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources