locked
How to SELECT * into a SQL table incremntally by date? RRS feed

  • Question

  • I have a SQL Server table called "tblProducts".

    Sometimes I backup this table by making a copy of it with this simple query:

    SELECT *
    INTO [test01].[dbo].[tblProducts_20141206]
    FROM [test01].[dbo].[tblProducts]

    Every time when making a backup, the date is included in the table name.

    I would like to create a SQL Job that runs this kind of query once every week.

    Is it possible to maybe in a stored procedure or declaring a variable to achieve this that allows the backed-up table name to be named like [tblProducts_todaysDate]?

    Thanks.

    Sunday, December 7, 2014 12:23 AM

Answers

  • yes if you use dynamic sql

    Declare @date datetime,@sql varchar(1000)
    
    SET @date = getdate()
    
    SET @sql='SELECT *
    INTO [test01].[dbo].[tblProducts_' + CONVERT(varchar(8),@date,112) +']
    FROM [test01].[dbo].[tblProducts]'
    EXEC (@Sql)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by SathyanarrayananS Sunday, December 7, 2014 2:20 AM
    • Marked as answer by dchencm Tuesday, December 9, 2014 6:04 PM
    Sunday, December 7, 2014 1:41 AM
  • DECLARE @SQLQuery AS NVARCHAR(500)
    DECLARE @TableName AS NVARCHAR(500)='dbo.test1'+ convert(varchar(10),getdate(),112)
    DECLARE @SQLQuery2 AS NVARCHAR(500)
    SET @SQLQuery2 ='IF OBJECT_ID ('''+@TableName+ ''',N''U'') IS NOT NULL drop table ' +@TableName
    
    EXEC (@SQLQuery2 )
    
    
    SET @SQLQuery = 'SELECT * into ' + @TableName + ' FROM  dbo.test1' 
    
    EXEC(@SQLQuery)
    
     --EXEC ('select * from '+ @TableName )

    Sunday, December 7, 2014 1:42 AM

All replies

  • yes if you use dynamic sql

    Declare @date datetime,@sql varchar(1000)
    
    SET @date = getdate()
    
    SET @sql='SELECT *
    INTO [test01].[dbo].[tblProducts_' + CONVERT(varchar(8),@date,112) +']
    FROM [test01].[dbo].[tblProducts]'
    EXEC (@Sql)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by SathyanarrayananS Sunday, December 7, 2014 2:20 AM
    • Marked as answer by dchencm Tuesday, December 9, 2014 6:04 PM
    Sunday, December 7, 2014 1:41 AM
  • DECLARE @SQLQuery AS NVARCHAR(500)
    DECLARE @TableName AS NVARCHAR(500)='dbo.test1'+ convert(varchar(10),getdate(),112)
    DECLARE @SQLQuery2 AS NVARCHAR(500)
    SET @SQLQuery2 ='IF OBJECT_ID ('''+@TableName+ ''',N''U'') IS NOT NULL drop table ' +@TableName
    
    EXEC (@SQLQuery2 )
    
    
    SET @SQLQuery = 'SELECT * into ' + @TableName + ' FROM  dbo.test1' 
    
    EXEC(@SQLQuery)
    
     --EXEC ('select * from '+ @TableName )

    Sunday, December 7, 2014 1:42 AM
  • hi ,dchencm

    i just want to point out the some bad effect of this pratice

    first point is

    when your db has be corrupt,your backup out of work

    i think you should backup your table to other db ensure that when your db has be corrupt you still have real backup

    just like

    SELECT *
    INTO [test01_backup].[dbo].[tblProducts_20141206]
    FROM [test01].[dbo].[tblProducts]

    another point is your pratice is total amount of backup not  incremental backup

    when your table  become bigger and bigger ,and then the number of record reach several million or several ten million or several hundred million, you must import all  data the table have

    so, this is not a good idea

    i just suggest  apply replication or logshipping etc to copy the diff data the table proceded is the better


     the steps of detail  as following

    step 1

    USE [test01]
    GO
    /****** 对象:  StoredProcedure [dbo].[sp_ImportBackupData]    脚本日期: 12/25/2010 16:47:49 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE [dbo].[sp_importbackupdata]
    AS
        BEGIN
            BEGIN
    
                DECLARE @date DATETIME ,
                    @sql VARCHAR(1000)
    
                SET @date = GETDATE()
    
                SET @sql = 'SELECT *  INTO [test01].[dbo].[tblProducts_'
                    + CONVERT(VARCHAR(8), @date, 112)
                    + ']  FROM [test01].[dbo].[tblProducts]'
                EXEC (@Sql)
            END
    
        END

    step 2

    USE [msdb]
    GO
    /****** 对象:  Job [import data]    脚本日期: 02/22/2011 09:22:44 ******/
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    /****** 对象:  JobCategory [Database Engine Tuning Advisor]    脚本日期: 02/22/2011 09:22:44 ******/
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Engine Tuning Advisor' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Engine Tuning Advisor'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    
    END
    
    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'sp_importbackupdata', 
    		@enabled=1, 
    		@notify_level_eventlog=0, 
    		@notify_level_email=0, 
    		@notify_level_netsend=0, 
    		@notify_level_page=0, 
    		@delete_level=0, 
    		@description=N'sp_importbackupdata', 
    		@category_name=N'Database Engine Tuning Advisor', 
    		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** 对象:  Step [import data]    脚本日期: 02/22/2011 09:22:44 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'import data', 
    		@step_id=1, 
    		@cmdexec_success_code=0, 
    		@on_success_action=1, 
    		@on_success_step_id=0, 
    		@on_fail_action=2, 
    		@on_fail_step_id=0, 
    		@retry_attempts=0, 
    		@retry_interval=0, 
    		@os_run_priority=0, @subsystem=N'TSQL', 
    		@command=N'exec sp_importbackupdata', 
    		@database_name=N'sss', 
    		@flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'import frequency', 
    		@enabled=1, 
    	    @freq_type=8, 
    		@freq_interval=2, 
    		@freq_subday_type=1, 
    		@freq_recurrence_factor=1, 
    		@active_start_time=20000
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:

    the schedule time is 2 am  in the midnight  every monday 

    Certainly, you can edit it for yourself





    Sunday, December 7, 2014 3:12 AM
  • Why not backup the entire database instead?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 7, 2014 9:32 AM
  • 1) Google "tbl-" as a bad code smell. Really old software had to give the system or the compilers hints in the variable and file names because they were simple one-pass parsers. 

    2) Google the history of  mag tape files. The IBM convention in the 1950's was to use the truncated ordinal date in the tape labels (yy-ddd). This was part of the Y2K panic. You just re-invented it! 

    3) We have tools today that will backup the whole database. Since an RDBMS uses the whole schema as the unit of work for DRI, queries, etc. why would you make a copy of only one table? 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, December 7, 2014 7:15 PM
  • 1) Google "tbl-" as a bad code smell.

    So I did:

    Look no hits!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, December 7, 2014 8:15 PM
  • https://www.simple-talk.com/books/sql-books/119-sql-code-smells/


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, December 7, 2014 9:50 PM