Solved

Stored Procedure to identify and copy tables and data from one database to another database on a recurring basis

Posted on 2008-10-13
10
965 Views
Last Modified: 2013-11-10
I need to create a stored procedure that will actually copy the tables and the data in the tables from one database to another database on the same server instance.  I need this to be something that is dynamic and recurring.  It is for an ETL process to archive the previous days source data before loading the new source data.  It is possible that tables will be added or subtracted so I need this to run on variables.  I have a stored procedure that will rename tables.  That process will rename the Archive tables with the current date in front of them but not until after the new source data has been pulled.  

2 databases:  Staging and Archive
Orginal Data:  Staging
Archive Data: Archive

Step1: copy all the tables from Staging into Archive with the same table names
Step2: Truncate tables in Staging and reload that day's source data
Step3: If the source data fails to load correctly in staging then that table name is sent to a logging file and the rest of the container continues to load the Source data.
Step4: If there is a failure within the container then a stored procedure will lookup the name of the table and copy the Archive data for that table back into the Staging.
Step5:  Once all source data is loaded into Staging then the Rename procedure I already have will rename the Archive tables to have the day's date and time in front of them.

I cannot seem to get the part of the Copy the tables based on variables to work.  I want them to find every table and put a copy of the table and data into Archive.

This is needed very quickly as I have to have the whole process in place this week.  This is the only piece I seem to be having trouble with.  
0
Comment
Question by:mwrye
  • 4
  • 3
10 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 22707274
This is a snippet of SQL I use to find view names by a certain pattern.  You could find your table names the same way and then build a dynamic sql statement:
DECLARE @tablename nvarchar(100), @sql nvarchar(max)
 
SELECT @tablename = name 
FROM sys.sysobjects 
WHERE name LIKE 'pattern%'
AND xtype = 'U'
 
SET @sql = 'SELECT * INTO Archive.dbo.['+@tablename+'] FROM ['+@tablename+']'
 
EXEC(@sql)

Open in new window

0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 250 total points
ID: 22707658
It should be noted that no indexes or constraints (default, check, unique, etc.) will be copied over with this method.

Below is a procedure that chapmandew put together that I have tweaked a little.  You could modify it to accept new db name and whatever else you want to make it what you want.  It however will not copy over the data so that would need to be a different procedure.

If you want to explore something like the below procedure, let me know.  Otherwise mwvisa1's solution is correct if you don't care about the object types above being copied over.
set nocount on
use [master]
go
if object_id('[dbo].[sp_ScriptTable]') is not null
     drop procedure [dbo].[sp_ScriptTable]
go
CREATE PROCEDURE [dbo].[sp_ScriptTable]
      @TableName              SYSNAME
     ,@IncludeConstraints     BIT = 1
     ,@IncludeIndexes         BIT = 1
     ,@NewTableName           SYSNAME = NULL
     ,@UseSystemDataTypes     BIT = 0
     ,@CreateStatement        nvarchar(max) = null output
     ,@OutputOnly             bit = 0
     
/*********************************************************
*
*    original author:    Tim Chapman(dew)
*    modified by:        Brandon Galderisi
*    purpose:            Given a table, it outputs the necessary 
*                        SQL to recreate such table.
*
*    last modified:      07-Oct-2008
*
*********************************************************/
AS
set nocount on
 
    DECLARE @MainDefinition TABLE
    (
        FieldValue VARCHAR(1000)
    )
 
    DECLARE @DBName SYSNAME
    DECLARE @ClusteredPK BIT
    DECLARE @TableSchema NVARCHAR(255)
    SET @DBName = DB_NAME(DB_ID())
 
    --//Make sure the table name is the same case.
    SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)
        
    DECLARE @ShowFields TABLE
    (
        FieldID INT IDENTITY(1,1),
        DatabaseName VARCHAR(100),
        TableOwner VARCHAR(100),
        TableName VARCHAR(100),
        FieldName VARCHAR(100),
        ColumnPosition INT,
        ColumnDefaultValue VARCHAR(100),
        ColumnDefaultName VARCHAR(100),
        is_System_Named bit,
        IsNullable BIT, 
        DataType VARCHAR(100),
        MaxLength INT,
        NumericPrecision INT,
        NumericScale INT,
        DomainName VARCHAR(100), 
        FieldListingName VARCHAR(110), 
        FieldDefinition CHAR(1),
        IdentityColumn BIT, 
        IdentitySeed INT,
        IdentityIncrement INT, 
        IsCharColumn BIT
    )
 
    DECLARE @HoldingArea TABLE
    (
        FldID SMALLINT IDENTITY(1,1),
        Flds VARCHAR(4000), 
        FldValue CHAR(1) DEFAULT(0)
    )
 
 
    DECLARE @PKObjectID TABLE
    (
        ObjectID INT
    )
 
    DECLARE @Uniques TABLE
    (
        ObjectID INT
    )
 
    DECLARE @HoldingAreaValues TABLE
    (
        FldID SMALLINT IDENTITY(1,1),
        Flds VARCHAR(4000), 
        FldValue CHAR(1) DEFAULT(0)
    )
 
    DECLARE @Definition TABLE
    (
        DefinitionID SMALLINT IDENTITY(1,1),
        FieldValue VARCHAR(200)
    )
 
    INSERT INTO @ShowFields
    (        
        DatabaseName,
        TableOwner,
        TableName,
        FieldName,
        ColumnPosition,
        ColumnDefaultValue,
        ColumnDefaultName,
        is_system_named,
        IsNullable, 
        DataType,
        MaxLength,
        NumericPrecision,
        NumericScale,
        DomainName,
        FieldListingName, 
        FieldDefinition, 
        IdentityColumn,
        IdentitySeed, 
        IdentityIncrement, 
        IsCharColumn
    )
 
    SELECT 
        DB_NAME(), 
        TABLE_SCHEMA,
        TABLE_NAME,
        COLUMN_NAME,
        CAST(ORDINAL_POSITION AS INT),
        COLUMN_DEFAULT,
        dobj.name AS ColumnDefaultName,
        skc.is_system_named,
        CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
        DATA_TYPE,
        CAST(CHARACTER_MAXIMUM_LENGTH AS INT),
        CAST(NUMERIC_PRECISION AS INT),
        CAST(NUMERIC_SCALE AS INT),
        DOMAIN_NAME,
        COLUMN_NAME + ',',
        '' AS FieldDefinition,
        CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn, 
        CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed, 
        CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement, 
        CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn
    FROM 
        INFORMATION_SCHEMA.COLUMNS c
        JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
        LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
        JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
        LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'
        left outer join sys.default_constraints skc on dobj.object_id = skc.object_id
--
    WHERE c.TABLE_NAME = @TableName
    ORDER BY 
        c.TABLE_NAME, c.ORDINAL_POSITION
 
    SELECT TOP 1 @TableSchema = TableOwner
    FROM @ShowFields
    
    INSERT INTO @HoldingArea (Flds) VALUES('(')
 
    INSERT INTO @Definition(FieldValue)
    VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE '['+@DBName + '].[' + @TableSchema + '].[' + @TableName+']' END)
 
    INSERT INTO @Definition(FieldValue)
    VALUES('(')
 
    INSERT INTO @Definition(FieldValue)
        SELECT 
                '['+FieldName + '] ' + 
                                    CASE 
                                        WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
                                        ELSE UPPER(DataType) + 
                                            CASE WHEN IsCharColumn = 1 THEN '(' + case maxlength when -1 then 'MAX' else CAST(MaxLength AS VARCHAR(10)) end + ')' ELSE '' END +
                                            CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +
                                            CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +
                                            CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN case is_system_named when 1 then '' else 'CONSTRAINT [' + ColumnDefaultName + '] ' end + 'DEFAULT' + ColumnDefaultValue ELSE '' END
                                    END + 
                                    
                                    CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END
        FROM    @ShowFields 
 
        IF @IncludeConstraints = 1
        BEGIN
            INSERT INTO @Definition(FieldValue)
            SELECT 
            ',CONSTRAINT ' + case when is_system_named = 1 then '' else '[' + name + '] ' end + 'FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'
            FROM
            (
                SELECT is_system_named,
                ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name,
                REVERSE(SUBSTRING(REVERSE((
                SELECT cp.name + ','
                FROM
                sys.foreign_key_columns fkc
                JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
                WHERE fkc.constraint_object_id = fk.object_id
                FOR XML PATH('')
                )), 2, 8000)) ParentColumns, 
                REVERSE(SUBSTRING(REVERSE((
                SELECT cr.name + ','
                FROM
                sys.foreign_key_columns fkc
                JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
                WHERE fkc.constraint_object_id = fk.object_id
                FOR XML PATH('')
                )), 2, 8000)) ReferencedColumns
                FROM sys.foreign_keys fk
            ) a
            WHERE ParentObject = @TableName
 
            INSERT INTO @Definition(FieldValue)
            SELECT', ' + case when is_system_named =1 then '' else  'CONSTRAINT [' + name + '] ' end + 'CHECK ' + definition FROM sys.check_constraints
            WHERE OBJECT_NAME(parent_object_id) = @TableName
        
--------------------------------------------------------------------------------------------
 
        INSERT INTO @PKObjectID(ObjectID)
        SELECT DISTINCT 
            PKObject = cco.object_id
        FROM 
            sys.key_constraints cco
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
        WHERE 
            OBJECT_NAME(parent_object_id) = @TableName    AND    
            i.type = 1 AND 
            is_primary_key = 1
 
        INSERT INTO @Uniques(ObjectID)
        SELECT DISTINCT 
            PKObject = cco.object_id
        FROM 
            sys.key_constraints cco
            JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
            JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
        WHERE 
            OBJECT_NAME(parent_object_id) = @TableName AND        
            i.type = 2 AND
            is_primary_key = 0 AND
            is_unique_constraint = 1 
            
        SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
 
        INSERT INTO @Definition(FieldValue)
        SELECT    ', ' + case when cco.is_System_Named=1 then '' else 'CONSTRAINT ['+cco.name+']' end + CASE type WHEN 'PK' THEN ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END 
                            WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' +
        REVERSE(SUBSTRING(REVERSE((
            SELECT 
                c.name +  + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
            FROM 
                sys.key_constraints ccok
                LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
                LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id
                LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
            WHERE 
                i.object_id = ccok.parent_object_id AND 
                ccok.object_id = cco.object_id
            FOR XML PATH('')
        )), 2, 8000)) + ')'
        FROM 
            sys.key_constraints cco
            LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID
            LEFT JOIN @Uniques u ON cco.object_id = u.objectID
        WHERE 
            OBJECT_NAME(cco.parent_object_id) = @TableName
        END
----------------------------------------------------------------------------------------------
        INSERT INTO @Definition(FieldValue)
        VALUES(')')
 
        IF @IncludeIndexes = 1
        BEGIN
            INSERT INTO @Definition(FieldValue)
            SELECT 
                'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' +  OBJECT_NAME(object_id) + '] (' + 
                REVERSE(SUBSTRING(REVERSE((
                    SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
                    FROM 
                        sys.index_columns sc
                        JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
                    WHERE 
                        OBJECT_NAME(sc.object_id) = @TableName AND
                        sc.object_id = i.object_id AND 
                        sc.index_id = i.index_id
                    ORDER BY index_column_id ASC
                    FOR XML PATH('')
            )), 2, 8000)) + ')'
            FROM sys.indexes i
            WHERE 
                OBJECT_NAME(object_id) = @TableName
                AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1
                AND is_unique_constraint = 0
                AND is_primary_key = 0
                and i.Type <> 0
        END
            INSERT INTO @MainDefinition(FieldValue)
            SELECT FieldValue FROM @Definition 
            ORDER BY DefinitionID ASC
 
        SELECT @CreateStatement= isnull(@CreateStatement +char(13),'') + FieldValue FROM @MainDefinition
 
if @OutputOnly=0
     select @CreateStatement
 
 
go
 
exec sp_MS_marksystemobject '[dbo].[sp_ScriptTable]'
 
--Sample data below
--
--if object_id('[dbo].[abcd]') is not null
--  exec ('drop table [ABCd]')
--go
--create table [ABCd] (ident int identity(1,2) not null primary key nonclustered
--     ,timCh nvarchar(max)
--     ,timC varchar(1) default 'a' 
--     --,timb varchar(1) unique 
--     )
----create unique clustered index [cidx_abc-d_timc] on [ABCd](timc)
--go
--
--
--declare @CreateStatement nvarchar(max)
--exec [dbo].[sp_ScriptTable]
--
--      @TableName              ='ABCd'
--     ,@IncludeConstraints     = 1
--     ,@IncludeIndexes         = 1
--     ,@NewTableName           = NULL
--     ,@UseSystemDataTypes     = 0
--     ,@CreateStatement        = @CreateStatement output
--     ,@OutputOnly             = 1
--
--select @CreateStatement
--
--go
--
--
--if object_id('[dbo].[abcd]') is not null
--  exec ('drop table [ABCd]')
--go
--create table [ABCd] (ident int identity(1,2) not null primary key nonclustered
--     ,timCh nvarchar(max)
--     ,timC varchar(1) default 'a' 
--     --,timb varchar(1) unique 
--     )
--create unique clustered index [cidx_abc-d_timc] on [ABCd](timc)
--go
--
--
--declare @CreateStatement nvarchar(max)
--exec [dbo].[sp_ScriptTable]
--
--      @TableName              ='ABCd'
--     ,@IncludeConstraints     = 1
--     ,@IncludeIndexes         = 1
--     ,@NewTableName           = NULL
--     ,@UseSystemDataTypes     = 0
--     ,@CreateStatement        = @CreateStatement output
--     ,@OutputOnly             = 1
--
--select @CreateStatement
--
--go
--
--
--if object_id('[dbo].[abcd]') is not null
--  exec ('drop table [ABCd]')
--go
--create table [ABCd] (ident int identity(1,2) not null primary key nonclustered
--     ,timCh nvarchar(max)
--     ,timC varchar(1) default 'a' 
--     ,timb varchar(1) unique 
--     )
--create unique clustered index [cidx_abc-d_timc] on [ABCd](timc)
--go
--
--
--declare @CreateStatement nvarchar(max)
--exec [dbo].[sp_ScriptTable]
--
--      @TableName              ='ABCd'
--     ,@IncludeConstraints     = 1
--     ,@IncludeIndexes         = 1
--     ,@NewTableName           = NULL
--     ,@UseSystemDataTypes     = 0
--     ,@CreateStatement        = @CreateStatement output
--     ,@OutputOnly             = 1
--
--select @CreateStatement
--
--
--

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22707660
Maybe "if you don't care about" doesn't sound right.  Maybe:

if the objects are not required.

:)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22707673
Thanks for extra detail Brandon.  I didn't have the energy to go into all that. :)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22707941
mwvisa1:
it's tEEmwork :)

email me.  my email isn't that hard to find if you google me.  You just may have to get beyond the EE pages or add the following to your search.

-site:experts-exchange.com
0
 

Author Comment

by:mwrye
ID: 22714225
Hello BrandonGalderisi and mwvisa1.  Thank you so much for the responses.  Both are very good and sort of what I do to rename my archive tables (except that I find the table names like this and then do the sp_rename).

 This does indeed get the table names built dynamically but the main issue I have is getting the data to go with the tables.  I have not been able to dynamically call each table, find the match, and copy over the data.  

I really like your responses but I think I might have to temporarily create the SSIS package from the Import/Export Wizard and call it from my main package.  I can then go back with more time and work on getting the data to dynamically populate after creating the tables using the method list above so I don't have to worry anytime a new source table is added with modifying the XML from the import/export.

Is it OK with both of you that I leave this open for a little while and come back to you?  I really think you both have me almost where I need to be on this but am really stressing to get it done today.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22714530
Just don't wait too long.  21 days classifies a question as abandoned and is eligible for cleanup (deletion).
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22720590
And take a look at our solutions again to make sure as mine uses a SELECT...INTO statement that will copy the table structure and data over; however, as Brandon said not indexes so he used an INSERT INTO statement.  Both should be copying data, so please advise once you do your research what exactly is happening.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now