Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Switching tables

By tomer lev, 2008/09/05

Run the script and edit the procedure called usp_process.

Edit it in a way you can make one process to your offline table while writing to your onlien table - see remarks in the script.

good luck,

Tomer Lev

http://www.nuconomy.com

/*
created by: Tomer Lev / Nuconomy.com

*/

CREATE TABLE [dbo].[Parameters](
    [Name] [nvarchar](50) NOT NULL,
    [Value] [int] NOT NULL,
CONSTRAINT [PK_Parameters] PRIMARY KEY CLUSTERED 
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go

/*
your tables must be built at the same schema

*/
insert into Parameters
select 'your first table',0
union all
select 'your second table',1
go

create PROCEDURE [dbo].[SwitchOnlineOffline]
AS
BEGIN
    begin tran

        declare @OfflineTable varchar(50)
        declare @OnlineTable varchar(50)
     declare @strSQL nvarchar(200)

        set @OfflineTable = (select name from Parameters where Value = 0)
        set @OnlineTable =     (select name from Parameters where Value = 1)


        set @strSQL = 'update Parameters set value = 1 where name = ' + '''' + @OfflineTable + ''''
        exec (@strSQL)
        set @strSQL = 'update Parameters set value = 0 where name = ' + '''' + @OnlineTable + ''''
        exec (@strSQL)

    commit tran
END

go


/*
put this procedure in a job and shedule it -
it works great...

DO NOT FORGET TO EDIT THE SP BELOW - SEE REMARKS
*/
create PROCEDURE [dbo].[usp_process] 

AS
BEGIN

print ' 
' 
/* 
we disable notifiations on records count 
*/ 
 
SET NOCOUNT ON 


    
    begin tran
    exec [SwitchOnlineOffline]
    commit tran


    declare @OfflineTable varchar(50)
    declare @xmltmp xml
    declare @id int
    declare @message varchar(80)
    declare @paramdefinision nvarchar(500)
    declare @strSQL nvarchar(4000) 

    set @OfflineTable = (select name from Parameters where Value = 0)

  /* put some code on your @offline table
     make some process u need to make on it; while writing
     to your online table (after the switch has been made)	
	
	could be in dimnamic sql for example:	

	--set @strSQL = 'make some operation on table ' + @OfflineTable    
	--exec (@strSQL)
  */
      
   




END

Total article views: 902 | Views in the last 30 days: 1