Questions concerning performance/production resources where exporting / importing big data sets I have a MS SQL Server database that contains data for many customers and I am working on a feature to export a single customers data.

I have basically created a script that generates inserts for all the customer records to run on another database. This is working great in my test environment but I am concerned about what impact this will have on production.

We could of used a product like RedGate but we decided to integrate this logic within our app itself.

Export Action 


No transaction required
Need to be run on live database but will be off hours.
Right now I have a stored procedure that returns the insert statements.


Import Action


Transaction required.
Need to be run on live database but will be off hours.
I run the returned insert statements on the new database.


Questions (mostly regarding the production environment)


The export script touches virtually every table in the system (with no lock), should I be concerned about this action? I have had suggestions to break up this action into pieces, to not create one giant execution. Would there be an advantage to this?
The import script will potentially be running millions of inserts. With similar concerns to the export, should I be adding "GO"s every X inserts? Is that enough? Should this be broken into multiple executions? Is this a scenario where I need to use SQL Bulk Copy?


Thank you very much for your help.
I hope i have understood everything good.

You have MSSQL server let's name it S1 from whitch you have to export data to other MSSQL server, let's name it S2.

The best solution depends from possibilites.


With Linked Server object.
If you have access over network to both of them, I would suggest using linked server to communicate over network and retrive data from S1 to S2.
https://www.google.pl/webhp?sourceid=chrome-instant&amp;ion=1&amp;espv=2&amp;ie=UTF-8#q=mssql%20linked%20server


Here is my script, may help.

DECLARE @OPENQUERY NVARCHAR(4000) ,
    @TSQL NVARCHAR(4000) ,
    @LinkedServer NVARCHAR(4000);

SET @LinkedServer = 'lnk_test';
SET @OPENQUERY = 'SELECT * FROM OPENQUERY(' + @LinkedServer + ',''';

----------------------------------------------------------------------------
-------------------------------- Retrieving Data ---------------------------
----------------------------------------------------------------------------
--##StatusMig - Table containg progres and times

IF OBJECT_ID('tempdb..##StatusMig') IS NOT NULL
    BEGIN
        DROP TABLE ##StatusMig;
    END;
CREATE TABLE ##StatusMig
    (
      LP INT ,
      NAME VARCHAR(MAX) ,
      DATA DATETIME
    );

INSERT  INTO ##StatusMig
        ( LP ,
          NAME ,
          DATA
        )
        SELECT  ISNULL(MAX(LP), 0) + 1 ,
                'Start' ,
                GETDATE()
        FROM    ##StatusMig;

-----------------------------------------------------------------
-------- TABLE_A
-----------------------------------------------------------------

IF OBJECT_ID('tempdb..##TABLE_A') IS NOT NULL
    BEGIN
        DROP TABLE ##TABLE_A;
    END;

CREATE TABLE ##TABLE_A ( col_1 INT, col_2 INT );


INSERT  INTO ##StatusMig
        ( LP ,
          NAME ,
          DATA
        )
        SELECT  ISNULL(MAX(LP), 0) + 1 ,
                'Start Start TABLE_A' ,
                GETDATE()
        FROM    ##StatusMig;

PRINT 'Start TABLE_A';

SET @TSQL = '
        select col_1, col2_2 from TABLE_A
          '')';

PRINT @OPENQUERY + @TSQL;

INSERT  INTO ##TABLE_A
        EXEC ( @OPENQUERY + @TSQL
            );

INSERT  INTO ##StatusMig
        ( LP ,
          NAME ,
          DATA
        )
        SELECT  ISNULL(MAX(LP), 0) + 1 ,
                'End TABLE_A' ,
                GETDATE()
        FROM    ##StatusMig;



No network access, possibility to use files.
Generating insert statments and execution them is last thing you want to do. Will work slow and it will consume alot of resources.


Instead of creating insert statments, you can create new database for example data_migration with customer data.

S1 - MSSQL server from you export data
S2 - MSSQL server to you export data


On S1 create job whitch backup data_migration table every day.
Send data_migraction backup file over ftp etc.
On S2 create job whitch checks ftp and if backup from today is avalaible restores it.
Run T-SQL with inserts.


Remember, work with sets not single values.
Sorry for spelling and grammar im after 36h work marathon.