Home > Link, Microsoft SQL Server, MSBI, Optimization, Query, Script, SQL Mentalist, SQL PraRup, SQL Query, SQL Server, Technology,, TSQL, Vishal Pawar > BI SQL # 137 : SQL Server DBA Scripts : Copy data from one database to another

BI SQL # 137 : SQL Server DBA Scripts : Copy data from one database to another

Hi Folks,

In this article we are going to cover How to Copy data from one database to another.

In this post we are going to discuss following points:

  • Problem Statement of SQL Script:
  • Description of SQL Script:
  • SQL Script Code
  • SQL Script Output Screenshot
  • User Level to execute

Problem Statement of SQL Script:

Copy data from one database to another.

Description of SQL Script:

Here is a script to copy all data from one database to another.

This script copies entire data from one database to another.

This disables all the foreign keys in the target database and copies all data from Source to target and enables all constraints back.

SQL Script Code

EXEC sp_MSforeachtable @command1 = 
    'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

DECLARE @linkedServer SYSNAME = 'TESTSERVER';
DECLARE @SourceDbName SYSNAME = 'AdventureWorksDW';
DECLARE @sql VARCHAR(8000)
DECLARE @tableName SYSNAME

DECLARE Cur_tab CURSOR
FOR
SELECT NAME
FROM sys.tables t
WHERE t.type = 'U';

OPEN cur_tab

FETCH NEXT
FROM CUR_TAB
INTO @tablename;

DECLARE @listStr VARCHAR(MAX)

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @listStr = NULL;

    SELECT @listStr = COALESCE(@listStr + ',', '') + NAME
    FROM sys.columns
    WHERE object_id = object_id(@tableName);

    SET @sql = 'SET IDENTITY_INSERT ' + @TABLENAME + 
        ' ON;INSERT INTO ' + @TABLENAME + '(' + @liststr + 
        ')SELECT ' + @liststr + ' FROM [' + convert(VARCHAR, 
            @linkedServer) + '].[' + convert(VARCHAR, 
            @SourceDbName) + '].[DBO].[' + @tableName + 
        '] 
        ;SET IDENTITY_INSERT ' + @TABLENAME + 
        ' OFF;'

    PRINT @SQL

    --EXEC(@SQL) 
    FETCH NEXT
    FROM CUR_TAB
    INTO @tablename;
END

CLOSE CUR_TAB

DEALLOCATE CUR_TAB

EXEC sp_MSforeachtable @command1 = 
    'ALTER TABLE ? CHECK CONSTRAINT ALL'

SQL Script Output Screenshot

image

User Level to execute

    300

    Hope you will like How to Copy data from one database to another.

    If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

Link Resource Website

For More information related to BI World visit my Mentalist Blog

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Infographic Mentalist >> Image worth explaining thousand Words

Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ

DBA Mentalist >>Advance SQL Server Blog

Microsoft BI Mentalist >> MS BI Development Update

Connect With me on

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: