Script to Copy Data Between Two SQL Server Databases

I recently had a requirement to "copy" the data between two identical database schemas. Actually the requirement was to "rebuild" a database from the ground up (objects and data). I almost always Visual Studio SQL Server projects (available after installing SSDT as well as with the latest versions of Visual Studio) when doing database development which means I can deploy an "empty" database with more or less the click of a mouse so I won't get into creating the actual database, only populating it. If you need to copy the database objects to a new database there are tools built within SQL Server Management Studio to allow you to do this. You can check out this TechNet post for more information. You can also generate DML or data population scripts from SSMS but it can result in massive files if you have a database with a decent amount of rows. My databases were rather large so I wanted to copy the data from one database to the other without having to explicitly write anything to disk.

This stored procedure handles tables with Identity columns as well as Computed columns. It also only attempts to populate tables that have zero rows. You might have different requirements so feel free to enhance it and post any enhancements that you think would be valuable to others.

CREATE PROCEDURE [dbo].[apUtilCopyDataFromDatabase] @SourceLinkedServerName     VARCHAR(128) = NULL,
                                       @SourceDatabaseName         VARCHAR(128),
                                       @SourceSchemaName           VARCHAR(128),
                                       @SourceTableNamePrefix      VARCHAR(128) = '',
                                       @TargetLinkedServerName     VARCHAR(128) = NULL,
                                       @TargetDatabaseName         VARCHAR(128) = NULL,
                                       @TargetSchemaName           VARCHAR(128) = 'dbo',
                                       @OnlyPopulateEmptyTablesInd BIT = 0
AS
  BEGIN
 SET NOCOUNT ON;
 DECLARE @SQLCursorData       NVARCHAR(MAX),
              @SQLTruncate         VARCHAR(MAX),
              @SQL                 VARCHAR(MAX),
              @TableName           SYSNAME,
              @SchemaName          VARCHAR(MAX),
              @HasIdentity         BIT,
              @CurrentDatabaseName VARCHAR(128) = DB_NAME(),
 @TargetDatabase VARCHAR(128) = @TargetDatabaseName; --ToDo: Cleanup confusing names  

      DECLARE @SQLNoCheck NVARCHAR(4000) = 'USE ' + @TargetDatabase + '; EXEC sp_MSforeachtable @command1 = ''ALTER TABLE ? NOCHECK CONSTRAINT ALL''; USE ' + @CurrentDatabaseName + ';';
 EXEC sp_executesql @SQLNoCheck;

      SET @SourceDatabaseName = IIF(@SourceLinkedServerName IS NOT NULL, QUOTENAME(@SourceLinkedServerName) + '.', '')
                                + QUOTENAME(@SourceDatabaseName) + '.'
                                + QUOTENAME(@SourceSchemaName);

      SET @TargetDatabaseName = IIF(@TargetLinkedServerName IS NOT NULL, QUOTENAME(@TargetLinkedServerName) + '.', '')
                                + IIF(@TargetDatabaseName IS NOT NULL, QUOTENAME(@TargetDatabaseName) + '.', '')
                                + QUOTENAME(@TargetSchemaName)

      SET @SQLCursorData = 'DECLARE TableCursor CURSOR GLOBAL FOR
        SELECT
          s.NAME
          ,t.NAME
        FROM ' + @TargetDatabase + '.sys.tables t
          JOIN ' + @TargetDatabase + '.sys.schemas s
            ON t.schema_id = s.schema_id
          JOIN ' + @TargetDatabase + '.sys.objects AS o
            ON t.object_id = o.object_id
          JOIN ' + @TargetDatabase + '.sys.partitions AS p
            ON o.object_id = p.object_id
        WHERE
          t.type = ''U''
          AND t.NAME LIKE ''' + @SourceTableNamePrefix + '%''
        GROUP  BY
          s.NAME
          ,t.NAME
      HAVING SUM(p.rows) = 0 
OR ' + CAST(@OnlyPopulateEmptyTablesInd AS VARCHAR(1)) + ' = 0;'

      EXEC sp_executesql @SQLCursorData;

      OPEN TableCursor;

      FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;

      DECLARE @ColumnList VARCHAR(MAX);

      WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRY
PRINT CHAR(13) + CHAR(10)
                      + '-----------------------'
                      + QUOTENAME(@SchemaName) + '.'
                      + QUOTENAME(@TableName)
                      + '-----------------------'

--ToDo: Add logic to determine if there are FK constraints. TRUNCATE if not, DELETE if there are.
                IF (@OnlyPopulateEmptyTablesInd = 0)
BEGIN
SET @SQLTruncate = 'DELETE FROM '
  + QUOTENAME(@TargetDatabase) + '.'
  + QUOTENAME(@SchemaName) + '.'
  + QUOTENAME(@TableName) + ';';

EXEC(@SQLTruncate);

PRINT CAST(@@ROWCOUNT AS VARCHAR) + ' rows deleted.';
END

                DECLARE @SQLHasIdentity NVARCHAR(1000) = 
'USE ' + @TargetDatabase + '; 
SELECT @val = CAST(SUM(CAST(is_identity AS TINYINT)) AS BIT)
                        FROM ' + @TargetDatabase + '.sys.columns
                        WHERE object_id = OBJECT_ID(QUOTENAME(''' + @SchemaName + ''') + ''.'' + QUOTENAME(''' + @TableName + ''')); 
USE ' + @CurrentDatabaseName + ';';

                EXEC sp_executesql @SQLHasIdentity, N'@val bit OUTPUT', @val = @HasIdentity OUTPUT;

SET @ColumnList = NULL;

                DECLARE @SQLColumnList NVARCHAR(4000) = 
'USE ' + @TargetDatabase + '; 
DECLARE @ColList NVARCHAR(MAX);
SELECT @val = COALESCE(@val + '','', '''') + QUOTENAME(name)
FROM '
 + @TargetDatabase + '.sys.columns
WHERE
 object_id = OBJECT_ID(QUOTENAME(''' + @SchemaName + ''') + ''.'' + QUOTENAME(''' + @TableName + '''))
 AND is_computed = 0 
 AND NAME NOT IN ( ''CreatedDate'', ''CreatedUser'', ''LastUpdatedDate'', ''LastUpdatedUser'' ); 
 USE ' + @CurrentDatabaseName + ';';

                EXECUTE sp_executesql @SQLColumnList, N'@val VARCHAR(MAX) OUTPUT', @val = @ColumnList OUTPUT;                

                SET @SQL = IIF(@HasIdentity = 1, 'SET IDENTITY_INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ON;', '')
                           + 'INSERT INTO ' + @TargetDatabaseName + '.'
                           + @TableName + ' (' + @ColumnList + ') SELECT '
                           + @ColumnList + ' FROM '
                           + CONVERT(VARCHAR, @SourceDatabaseName) + '.'
                           + QUOTENAME(@TableName) + ' '
                           + IIF(@HasIdentity = 1, 'SET IDENTITY_INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' OFF ', '')
                           + ';';

                EXEC(@SQL);

PRINT CAST(@@ROWCOUNT AS VARCHAR) + ' rows inserted.';
            END TRY
            BEGIN CATCH
                PRINT ERROR_MESSAGE();
            END CATCH

            FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;;
        END

      CLOSE TableCursor;

      DEALLOCATE TableCursor;

 DECLARE @SQLCheck NVARCHAR(4000) = 'USE ' + @TargetDatabase + '; EXEC sp_MSforeachtable @command1 = ''ALTER TABLE ? CHECK CONSTRAINT ALL''; USE ' + @CurrentDatabaseName + ';';
 EXEC sp_executesql @SQLCheck;

      RETURN 0
  END

DISTINCT or GROUP BY?

GROUP BY and DISTINCT can return the same results when looking for uniqueness in a resultset. Consider the Sales table below.

Store     Product        Date

-------------------------------------
A          Monopoly    1/10/2010
B          Connect 4    1/12/2010
A          Monopoly    1/14/2010
A          Battle Ship   1/16/2010
B          Othelo         1/16/2010
 
If you wanted to know what games each store sold from 1/10/2010 through 1/16/2010 you could write either of the following queries:

SELECT DISTINCT
    Store,
    Product
FROM
    Sales

-OR-

SELECT
    Store,
    Product
FROM
    Sales
GROUP BY
    Store,
    Product

So which one?? I hate to admit it but back when I first got into SQL performance tuning I was working in an Oracle 8i environment. If I remember correctly, GROUP BY performed better than DISTINCT for one reason or another. Well in the days of SQL Server 2005 and 2008, GROUP BY and DISTINCT perform equivalently. Therefore, my rule of thumb is to use DISTINCT when no aggregations on additional, non-unique columns are necessary. If you just want unique combinations of store and game use distinct. If you want to know how many of each game were sold by store then use GROUP BY.

SQL Formatter Is Great for Auto Generated SQL

Ever had to try to decipher SQL that was generated by a report writer or sql generation tool such as Cognos Framework Manager? How about trying to read through a co-worker's messy SQL? Not fun.

One of my favorite tools is the Instant SQL Formatter. It will clean up any valid SQL statement based on user configurable options. I usually just stick with the defaults. It's a great tool and it's free. Can't beat that. One of the first things I do when I set up a machine at a new client site is to add it to my favorites in IE.

Performance Issue: UNPIVOT with a Large Number of Columns

I love the PIVOT and UNPIVOT commands. They have saved me a ton of time on quite a few projects. UNPIVOT especially, since many times when working on BI projects I'm handed a monster spreadsheet by someone on the business side. Business people like seeing pivoted data. Database people like seing normalized data. I won't get into the keywords/commands here since there is plenty of material out there, most of which do a better job than I ever could of explaining their use.

However, I will tell you that SQL Server does not like "UNPIVOTing" a large number of columns as I recently found out. I should note that the query I have contains a few nested SELECTs (which I'm sure is part of the problem). I don't know the internals of what the database engine is doing when you using these commands but I do know they produce some pretty ugly execution plans. Since I just started this project and have a year of work to deliver in about 2 months (I'm not kidding) I haven't had the time to deconstruct the execution (nor should I really be writing about this topic).

What I have figured out is that if I break up a 280+ column UNPIVOT into 20 column chunks (an unpivot for every 20 columns - yes this is a maintenance nightmare) then I get reasonable performance (a couple of minutes to execute) whereas my original query never completed.

There's no way I'm going to be ok with having 15 queries instead of one to solve a performance issue but this is what I know for now and I'm able to present a working proof-of-concept. I hope to have a better understanding of the issue and as a result, the solution soon. I'll keep you posted. In the meantime, if you know what's going on please share . . .

Clustered Index Doesn't Have to be the Primary Key

Believe it or not, you don’t have to cluster your primary keys.

Most SQL Server mortals assume that their primary keys should be clustered because that is the default behavior of SQL Server Management Studio when using the designer to create a table. Unfortunately, the primary key is not always the best candidate for the one and only clustered index you're allowed on a table.

I won't go into detail here about what a clustered index is but I'll say that it is essentially your table and the order in which your table rows/records/tuples are stored is based on the column(s) used in your clustered index.

In an OLTP database where your pimary keys consist of a single integer identity column you're usually in good shape if your clustered index is also your primary key.

However, if you use composite and/or "intelligent" keys you're going to experience a number of performance problems. I won't expound on these problems here because I almost never use intelligent or composite keys in an OLTP system and don't believe they should be used in such systems.

The place I most often have a non-primary key clustered index is in an OLAP fact table. OLAP systems are more concerned about high performance reads of blocks of data as opposed to reads and writes of individual rows as is the case in transactional systems.

In a data warehouse/OLAP database date is often an attribute used in most analyses. By creating a clustered index on the most important date in a fact table (i.e. order date in an order table), you enable better I/O and therefore faster query response times for the majority of queries executed.

Some might argue that the unique surrugate key of the fact table (if you even have one) should have a clustered index. Hmmm . . . how often do you query a fact table and have a surrogate key based predicate? The answer should probably be never (although I almost never say never)!

How about writes? Fact tables are often written to based on a date. By clustering on date we typically have sequential writes to the disk. When we load today's data we end up adding data to the end of the last data page for the table because today's date comes after yesterday's date. If we were to cluster on some kind of an intelligent key we'd end up having to insert rows into pre-existing data pages which leads to much higher I/O and therefore decreased performance.

How about archiving and partitioning? In most OLAP scenarios the business requirement is to have something like 25 months worth of data available in the system at all times. It's often beneficial for performance and disk expense reasons to take old data offline. The best way to do this in a SQL Server environment is to partition your fact tables using a year/month based partition function. For partitioning to be successful it is necessary for your partition column to be part of the clustered index. If you cluster on your date column and partition on your date column as well, you're all set.

In short, if you're working in a data warehouse/OLAP environment pay careful attention to what column(s) you create your clustered indexes on. It can make or break the performance of your solution!

Derived Table, Subquery, Correlated Subquery, etc.

I was recently attempting to explain to someone the difference between derived tables and subqueries. I didn't do a great job and came to the conclusion that I wasn't completely clear on the terminology. I don't like not knowing things so I did a little research and this is what I found . . .

A derived table is not what I thought it was. I always referred to a derived table as being a query in a FROM clause. For example:

    SELECT
        id AS EmployeeId,
        name AS EmployeeFullName,
        COUNT(*) AS PaychecksIssuedCount
    FROM
        (SELECT
             id,
             name
          FROM
              employee
          WHERE
               employee_type_cd = 101) emp
          INNER JOIN paycheck ps
              on emp.id = ps.emp_id

Apparently this is actually called a subquery and can be referred to as a subquery in a FROM clause. It's a bit of symantics but a derived table is actually the result of a table subquery.

For some reason people that use SQL Server (like me) tend to refer to this as a derived table. I guess I'll have to stop doing that now.

As for correlated subqueries, these can be found in the list of projected columns as well as the WHERE and HAVING clauses. For example:

SELECT
    id,
    name
FROM
    employee emp
WHERE
    years_employed =  
        (SELECT
            AVG(years_employed)
         FROM
            employee emp_avg_years
         WHERE
            emp_avg_years.business_unit = emp.business_unit)

As you can see, the subquery has a WHERE clause that refers (correlates) to the outer/parent query.

So there you have it. Stop saying derived tables! Most likely you're misusing it the way I have ever since I learned about the concept (of subqueries - not derived tables ;)). Or maybe you're not as anal as I am. However, if you've read this far I'd say you are.

T-SQL End of Previous Week, Month, Quarter, Year

I recently had a requirement to capture periodic snapshots of an incident/activity type OLTP table into a fact table. I already had an accumulating snapshot type fact table where I was capturing the same facts on a daily basis. But, in addition to this, the client wanted the ability to perform period over period (weekly, monthly, quarterly, yearly) reporting.

My approach was simple enough and in case anyone was thinking about an overly complicated approach I figured I'd share . . .

After my daily load of the fact table I check to see if snapshot data exists for the prior week, month, quarter, and year. If any of these snapshots do not exist I simply do an INSERT selecting all data from the accumulating snapshot table and adding the As-Of date along with the level of periodicity (W,M,Q,Y).

I used 4 T-SQL if statements to determine if the pior period data exists and if it doesn't I call the INSERT.

Below are the statements used to capture the pior period as-of dates in an INT key format. The same format you would get converting a date to a VARCHAR using the 112 style (e.g. 20091231 for December 31st, 2009). I'll leave it up to you to apply this logic to your own INSERT statements.

--Previous end of month
DECLARE @EndOfWeekKey AS INT
SET @EndOfWeekKey = (SELECT CAST(CONVERT(VARCHAR,DATEADD(wk,-1,DATEADD(dd,-(DATEPART(dw, GETDATE()) - 7), GETDATE())),112) AS INT))

--Previous end of month
DECLARE @EndOfMonthKey AS INT
SET @EndOfMonthKey = (SELECT CAST(CONVERT(VARCHAR,DATEADD(d,-1,CAST(CAST(MONTH(GETDATE()) AS VARCHAR) + '/1/' + CAST(YEAR(GETDATE()) AS VARCHAR) AS DATETIME)),112) AS INT))

--Previous end of quarter
DECLARE @EndOfQuarterKey AS INT
SET @EndOfQuarterKey = (
SELECT
    CASE
        WHEN MONTH(GETDATE()) IN (1,2,3) THEN CAST(YEAR(GETDATE()) - 1 AS VARCHAR) + '1231'
        WHEN MONTH(GETDATE()) IN (4,5,6) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0331'
        WHEN MONTH(GETDATE()) IN (7,8,9) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0630'
        WHEN MONTH(GETDATE()) IN (10,11,12) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0930'
END)

--Previous end of year
DECLARE @EndOfYearKey AS INT
SET @EndOfYearKey = (SELECT CAST(CAST(YEAR(GETDATE()) -1 AS VARCHAR) + '1231' AS INT))

What's That Confounded Character?? (ASCII and CHAR Functions)

I was on my way back from the kitchen at my client's site when I passed the office of one of their developers. We made momentary eye contact and I could see that look of desperation. She immediately called out my name and said something like, "Dave! I need your help. You're smart." I can't resist that kind of flattery so I immediately put down my yogurt and pulled up a chair.

It seemed like a simple problem. She just wanted to take part of a string from a larger one - a substring. Below are examples of the complete strings. They were created from some kind of a flat file extract from a legacy system. Assume the column name is PRODVAL.

ymkez 11/30/2009 456.8
nipin+ 11/30/2009 432.90
gapter- 11/30/2009 543.12

She simply wanted the first part of the string - up to the "space". I don't recall her logic at this point but it used the T-SQL SUBSTRING and PATINDEX functions.

I began to re-write her query. I came up with the following.
    SELECT SUBSTRING(PRODVAL, 1, CHARINDEX(' ', PRODVAL) - 1) FROM table

Well, it didn't work. To troubleshoot the problem I rewrote the above query to as follows.
     SELECT CHARINDEX(' ', PRODVAL) FROM table

The query returned a whole long list of zeros. Something was obviously wrong. I could see the space. So why couldn't the CHRINDEX function? Next step, figure out what the "space" character actually is. The next query helped determine that.
    SELECT ASCII(SUBSTRING(PRODVAL,6,1) FROM table

The first row in the table had a PRODVAL value with a "space" in the 6th position so the above query returned the ASCII value of this 6th character. It returned a 9 - Tab.

Ok great. So now I knew we were looking for a  tab, not a space. So, going back to our original query, we can modify it to use the CHAR function to look for the tab.
    SELECT SUBSTRING(PRODVAL, 1, CHARINDEX(CHAR(9), PRODVAL) - 1) FROM table

Voila! Using the ASCII function we identified the mysterious character and then used the CHAR function to convert the ASCII code for Tab (9) to a character so that we could use it in a SUBSTRING function.

SQL Server - What's My Version/Edition?

Here's a quick way to find out what version of the SQL Server engine you have installed.

SELECT 
    SERVERPROPERTY('productversion') AS ProductVersion,
    SERVERPROPERTY ('productlevel') AS ProductLevel,
    SERVERPROPERTY ('edition') AS Edition

Keep in mind that it is possible to have a SQL Server running the Enterprise edition of the SQL Server engine but the Standard edition of Analysis Services.

Top Clause - Using Dynamic Values

Prior to SQL Server 2005, the value in the TOP clause was limited to literal values which meant that variables could not be used. Hence, the number of rows returned could not be variable/dynamic. Below is an example:

SELECT TOP 10 * FROM table1

New to SQL Server 2005 is the ability to use a variable in the TOP clause.

DECLARE @top INT
SET @top = 10
SELECT TOP (@top) *
FROM table1

This is pretty useful when there is a requirement to have a user determine the number of rows returned in a result set. Note that the variable must be between parantheses.