How to migrate SQL data and keep SP's

Question

I have two databases LIVE and DEVEL. I am currently working in DEVEL and have created and modified a number of stored procedures however I am running out of test data so I need to find a way to copy data from LIVE to DEVEL but I don't want to lose my stored procedures.

I am using SQL Server 2008 Management Studio and have tried Tasks -> Export Data. I then pick Drop and recreate new destination tables however I get errors like:

Violation of PRIMARY KEY constraint 'PK_Currency_Rate'. Cannot insert duplicate key in object 'dbo.Currency_Rate'.

So it is not dropping and recreating the tables.

I need to migrate data from LIVE to DEVEL quite often but haven't yet found a way of doing it and this is holding me back because I cannot test the implemented functionality.

Could anyone out there experienced enough in the matter point me in the right direction? I am not interested in paid third party tools like RedGate SQL Compare so please do not suggest them

Answer 1

I would ensure all my SP's are scripted off into files I can re-run on demand.

There are comparison tools within VS which allow you to script off changes, or you can manually go into the DEVEL database, expand the Programmability > Stored Procedures node, right click you new SP's and select Script Stored Procedure > Create to > File and save it somewhere sensible with your project.

Assuming this is the case, if you simply restore your LIVE database over the top of your DEVEL DB you can re-run your SP scripts on DEVEL and this will resolve the issue.

Answer 2

Execute this query for your tables, copy output data and run it's on another database.

Query:

DECLARE 
      @TableName SYSNAME
    , @ObjectID INT
    , @IsImportIdentity BIT = 1

DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR 
    SELECT 
          '[' + s.name + '].[' + t.name + ']'
        , t.[object_id]
    FROM (
        SELECT DISTINCT
              t.[schema_id]
            , t.[object_id]
            , t.name
        FROM sys.objects t WITH (NOWAIT)
        JOIN sys.partitions p WITH (NOWAIT) ON p.[object_id] = t.[object_id]
        WHERE p.[rows] > 0
            AND t.[type] = 'U'
    ) t
    JOIN sys.schemas s WITH (NOWAIT) ON t.[schema_id] = s.[schema_id]
    WHERE t.name IN ('<your table name>')

OPEN [tables]

FETCH NEXT FROM [tables] INTO 
      @TableName
    , @ObjectID

DECLARE 
      @SQLInsert NVARCHAR(MAX)
    , @SQLColumns NVARCHAR(MAX)
    , @SQLTinyColumns NVARCHAR(MAX)

WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT 
          @SQLInsert = ''
        , @SQLColumns = ''
        , @SQLTinyColumns = ''

    ;WITH cols AS 
    (
        SELECT 
              c.name
            , datetype = t.name
            , c.column_id
        FROM sys.columns c WITH (NOWAIT)
        JOIN sys.types t WITH (NOWAIT) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
        WHERE c.[object_id] = @ObjectID
            AND (c.is_identity = 0 OR @IsImportIdentity = 1)
            AND c.is_computed = 0
            AND t.name NOT IN ('xml', 'geography', 'geometry', 'hierarchyid')
    )
    SELECT 
          @SQLInsert = 'INSERT INTO ' + @TableName + ' (' + STUFF((
            SELECT ', [' + c.name + ']'
            FROM cols c
            ORDER BY c.column_id
            FOR XML PATH, TYPE, ROOT).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'
        , @SQLTinyColumns = STUFF((
            SELECT ', ' + c.name
            FROM cols c
            ORDER BY c.column_id
            FOR XML PATH, TYPE, ROOT).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
        , @SQLColumns = STUFF((SELECT CHAR(13) +
            CASE 
                WHEN c.datetype = 'uniqueidentifier' 
                    THEN ' + '', '' + ISNULL('''''''' + CAST([' + c.name + '] AS VARCHAR(MAX)) + '''''''', ''NULL'')' 
                WHEN c.datetype IN ('nvarchar', 'varchar', 'nchar', 'char', 'varbinary', 'binary') 
                    THEN ' + '', '' + ISNULL('''''''' + CAST(REPLACE([' + c.name + '], '''''''', '''''''''''' ) AS NVARCHAR(MAX)) + '''''''', ''NULL'')' 
                WHEN c.datetype = 'datetime'
                    THEN ' + '', '' + ISNULL('''''''' + CONVERT(VARCHAR, [' + c.name + '], 120) + '''''''', ''NULL'')' 
                ELSE 
                ' + '', '' + ISNULL(CAST([' + c.name + '] AS NVARCHAR(MAX)), ''NULL'')'
            END
            FROM cols c
            ORDER BY c.column_id
            FOR XML PATH, TYPE, ROOT).value('.', 'NVARCHAR(MAX)'), 1, 10, 'CHAR(13) + '', ('' +')

    DECLARE @SQL NVARCHAR(MAX) = '    
    SET NOCOUNT ON;
    DECLARE 
          @SQL NVARCHAR(MAX) = ''''
        , @x INT = 1
        , @count INT = (SELECT COUNT(1) FROM ' + @TableName + ')

    IF EXISTS(
        SELECT 1
        FROM tempdb.dbo.sysobjects
        WHERE ID = OBJECT_ID(''tempdb..#import'')
    )
        DROP TABLE #import;

    SELECT ' + @SQLTinyColumns + ', ''RowNumber'' = ROW_NUMBER() OVER (ORDER BY ' + @SQLTinyColumns + ')
    INTO #import
    FROM ' + @TableName + ' 

    WHILE @x < @count BEGIN

        SELECT @SQL = ''VALUES '' + STUFF((
        SELECT ' + @SQLColumns + ' + '')''' + '
        FROM #import 
        WHERE RowNumber BETWEEN @x AND @x + 9
        FOR XML PATH, TYPE, ROOT).value(''.'', ''NVARCHAR(MAX)''), 1, 2, CHAR(13) + '' '') + '';''

        PRINT(''' + @SQLInsert + ''')
        PRINT(@SQL)

        SELECT @x = @x + 10

    END'

    EXEC sys.sp_executesql @SQL

    FETCH NEXT FROM [tables] INTO 
          @TableName
        , @ObjectID

END

CLOSE [tables]
DEALLOCATE [tables]

Output:

INSERT INTO [Person].[Address] ([AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [PostalCode], [rowguid], [ModifiedDate])
VALUES 
  (1, '1970 Napa Ct.', NULL, 'Bothell', 79, '98011', '9AADCB0D-36CF-483F-84D8-585C2D4EC6E9', '2002-01-04 00:00:00')
, (2, '9833 Mt. Dias Blv.', NULL, 'Bothell', 79, '98011', '32A54B9E-E034-4BFB-B573-A71CDE60D8C0', '2003-01-01 00:00:00')
, (3, '7484 Roundtree Drive', NULL, 'Bothell', 79, '98011', '4C506923-6D1B-452C-A07C-BAA6F5B142A4', '2007-04-08 00:00:00')
, (4, '9539 Glenside Dr', NULL, 'Bothell', 79, '98011', 'E5946C78-4BCC-477F-9FA1-CC09DE16A880', '2003-03-07 00:00:00')
, (5, '1226 Shoe St.', NULL, 'Bothell', 79, '98011', 'FBAFF937-4A97-4AF0-81FD-B849900E9BB0', '2003-01-20 00:00:00')
, (6, '1399 Firestone Drive', NULL, 'Bothell', 79, '98011', 'FEBF8191-9804-44C8-877A-33FDE94F0075', '2003-03-17 00:00:00')
, (7, '5672 Hale Dr.', NULL, 'Bothell', 79, '98011', '0175A174-6C34-4D41-B3C1-4419CD6A0446', '2004-01-12 00:00:00')
, (8, '6387 Scenic Avenue', NULL, 'Bothell', 79, '98011', '3715E813-4DCA-49E0-8F1C-31857D21F269', '2003-01-18 00:00:00')
, (9, '8713 Yosemite Ct.', NULL, 'Bothell', 79, '98011', '268AF621-76D7-4C78-9441-144FD139821A', '2006-07-01 00:00:00')
, (10, '250 Race Court', NULL, 'Bothell', 79, '98011', '0B6B739D-8EB6-4378-8D55-FE196AF34C04', '2003-01-03 00:00:00');
INSERT INTO [Person].[Address] ([AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [PostalCode], [rowguid], [ModifiedDate])
VALUES 
  (11, '1318 Lasalle Street', NULL, 'Bothell', 79, '98011', '981B3303-ACA2-49C7-9A96-FB670785B269', '2007-04-01 00:00:00')
, (12, '5415 San Gabriel Dr.', NULL, 'Bothell', 79, '98011', '1C2C9CFE-AB9F-4F96-8E1F-D9666B6F7F22', '2007-02-06 00:00:00')
, (13, '9265 La Paz', NULL, 'Bothell', 79, '98011', 'E0BA2F52-C907-4553-A0DB-67FC67D28AE4', '2008-01-15 00:00:00')
, (14, '8157 W. Book', NULL, 'Bothell', 79, '98011', 'A1C658AE-C553-4A9D-A081-A550D39B64DF', '2004-01-05 00:00:00')
, (15, '4912 La Vuelta', NULL, 'Bothell', 79, '98011', 'F397E64A-A9D8-4E57-9E7C-B10928ACADD6', '2007-12-20 00:00:00')
, (16, '40 Ellis St.', NULL, 'Bothell', 79, '98011', '0312B65F-CB60-4396-9EC7-A78B2EAC1A1B', '2006-12-11 00:00:00')
, (17, '6696 Anchor Drive', NULL, 'Bothell', 79, '98011', 'CE9B3B47-9267-4727-BCD2-687C47482C06', '2007-12-10 00:00:00')
, (18, '1873 Lion Circle', NULL, 'Bothell', 79, '98011', '963854F7-E3CB-46A1-A3DB-1B05F71D6473', '2008-01-01 00:00:00')
, (19, '3148 Rose Street', NULL, 'Bothell', 79, '98011', '6B7ACB0F-CDBF-44FD-BA14-EB08A56C1582', '2008-05-04 00:00:00')
, (20, '6872 Thornwood Dr.', NULL, 'Bothell', 79, '98011', '4B1F1ED4-97A4-43FD-BB1E-9E05817718E8', '2003-03-09 00:00:00');