Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Data Migration without SSIS (for a single table) - SQL Server

RSS
Modified on Mon, Aug 22, 2011, 1:39 PM by Administrator Categorized as SQL Server
{outline||Step <1> - } To transfer data without using SSIS (SQL Server Integration Services), execute the following procedure.

See Also

Data Migration without SSIS

Generate the SELECT Statement

Execute the following T-SQL code against the source database. This will generate a SELECT statement. Be sure to adjust the @tableName variable to the table of interest.

{copytext|sql}
declare 
     @tableName varchar(max)
    ,@targetDatabase varchar(max)
    ,@sql varchar(max)
    ,@hasIdentityColumn bit

                        -- be sure the table name is in the format
select                  -- [schema].[table], including the brackets!
     @tableName         = '[dbo].[dwr_subreport_config]'
    ,@targetDatabase    = 'smd_webreports'
    ,@sql               = ''
    ,@hasIdentityColumn = 0

if exists (
    select 
        2 
    from 
        sys.objects o 
        inner join sys.schemas s on o.schema_id = s.schema_id 
        inner join sys.columns c on o.object_id = c.object_id
    where 1=1
        and c.is_identity = 1
        and '[' + s.name + '].[' + o.name + ']' = @tableName
    )

    set @hasIdentityColumn = 1


if @hasIdentityColumn = 1

    set @sql = @sql + 'select n = 1, sql = ''set identity_insert ' + @tableName + ' on''' + char(13)

select
    @sql = @sql + 'union select n = 2, sql = ''insert into ' + @tableName  + ' ('

;with MyColumns as (
    select
         c.name
        ,c.column_id
        ,TypeName = t.name
        ,IsIdentity = is_identity
        ,AddTicks = case t.name
            when 'varchar' then 1
            when 'nvarchar' then 1
            when 'datetime' then 1
            when 'smalldatetime' then 1
            when 'xml' then 1
            else 0
            end
    from
        sys.objects o
        inner join sys.columns c
            on o.object_id = c.object_id
        inner join sys.schemas s
            on o.schema_id = s.schema_id
        inner join sys.types t 
            on c.user_type_id = t.user_type_id
    where 1=1
        and '[' + s.name + '].[' + o.name + ']' = @tableName
    )
select 
     @sql = @sql + case column_id when 1 then '[' else ', [' end + name + ']'

from 
    MyColumns
order by 
    column_id
    
select
    @sql = @sql + ') values ('''
    
;with MyColumns as (
    select
         c.name
        ,c.column_id
        ,TypeName = t.name
        ,IsIdentity = is_identity
        ,AddTicks = case t.name
            when 'varchar' then 1
            when 'nvarchar' then 1
            when 'datetime' then 1
            when 'smalldatetime' then 1
            when 'xml' then 1
            else 0
            end
    from
        sys.objects o
        inner join sys.columns c
            on o.object_id = c.object_id
        inner join sys.schemas s
            on o.schema_id = s.schema_id
        inner join sys.types t 
            on c.user_type_id = t.user_type_id
    where 1=1
        and '[' + s.name + '].[' + o.name + ']' = @tableName
    )
select
    @sql = @sql 
            + case column_id when 1 then '' else ' + '', ''' end
            + case when AddTicks=1 
                then 
                    ' + coalesce(' 
                    + case when AddTicks=1 then ''''''''' + ' else '' end                    
                    + 'convert(varchar(max), ' 
                    + case when AddTicks=1 then 'replace(' else '' end
                    + name 
                    + case when AddTicks=1 then ', '''''''', '''''''''''')' else '' end
                    + ')'
                    + case when AddTicks=1 then ' + ''''''''' else '' end                    
                    + ', ''NULL'')'
                else 
                    ' + coalesce(convert(varchar(max), ' + name + '), ''null'')'
                end
from
    MyColumns    
order by
    column_id
    
select 
    @sql = @sql + ' + '')'' from ' + @tableName + ' /* where */' + char(13)

if @hasIdentityColumn = 1

    set @sql = @sql + 'union select n = 3, sql = ''set identity_insert ' + @tableName + ' off''' + char(13)

set @sql = @sql + 'order by 1'

print @sql

Generating the INSERT Statements

  1. Copy the SELECT statement generated into a new query window connected to the source database.
  2. Complete its WHERE clause for the data to be copied.
  3. Execute it.

This will generate a set of INSERT statements.

Executing the INSERT Statements

Copy the INSERT statements generated into a new query window connected to the target database, and execute them.

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.