Job - Scheduled update table in another databaes nightly - please review and comment on script / process

  • Please review Newbie's code and plan: Comments welcome

    SQL 2008 - Database name FOO FOO has 2 databases DBT and GIS Only Table in GIS is GISDaily

    Database GIS has SQL Server Security for the GIS dept username/Password set to DBO

    The GISDaily will be set to a scheduled task to run. The GIS_Info is a Table (or view) that will be modified (e.g. new fields) from time-to-time

    Each night - the GISDaily will be destroyed and rebuilt. The GIS Database will have GISDaily re-built each night.

    USE DBT;

    GO

    IF OBJECT_ID('dbo.GIS.GISDaily', 'U') IS NOT NULL

    DROP TABLE dbo.GIS.GISDaily;

    GO

    ALTER DATABASE GIS SET RECOVERY BULK_LOGGED;

    GO

    SELECT * INTO GIS.dbo.GISDaily

    FROM GIS_Info

    GO

    ALTER DATABASE GIS SET RECOVERY Simple;

    GO

    -- the idea is to rebuild GISDaily from a view in DBT. The GIS dept will provide changes in the DBT view.

    -- The UN/PW for the SQL server is only used for the GIS DB. So, the rebuilt table will be available to them.

  • Firstly, it should be GIS.dbo.GISDaily and not dbo.GIS.GISDaily.

    Secondly you can leave the database in simple recovery.

    USE DBT;

    GO

    IF OBJECT_ID('GIS.dbo.GISDaily', 'U') IS NOT NULL

    DROP TABLE GIS.dbo.GISDaily;

    GO

    SELECT *

    INTO GIS.dbo.GISDaily

    FROM GIS_Info

    GO

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply