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

• Mile Higher Than Sea Level

  SSCarpal Tunnel

  Points: 4828

  More actions

  October 15, 2012 at 4:47 pm

  #283090

  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.

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  October 17, 2012 at 2:21 am

  #1549902

  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]