3

I have a database DBv1 on a SQL Server, and I want to migrate the data in it to DBv2 (on the same box, save server). DBv2 has very similar schema. The only difference in schema is that DBv2 has one less table, because it is de-normalized.

What would be the most practical way to migrate the data from DBv1 to DBv2 given that change in schema, and the need for some transformation?

Is there a tool that would do that for me? Or do I have to script that manually?

For clarification: this is a one-time load only since it is just a migration. By schema here I do not refer to the SQL object with the same name. Rather, I am referring to the structure of the tables. Due to de-normalization, one table is removed, and a new column is added to the parent table that would have the data in the deleted table.

  • 1
    Since you can't simply restore over DBv2, the answer will be affected somewhat depending on whether this will be a one-time load or a recurring update (Note: Reloading from scratch can be considered a one-time load.) For a one-time load, an SSIS package is probably the easiest to do. Copy one-for-one for all of the identical tables (don't forget to set Identity Insert on) ,then use a query that generates the info you want in the one new table from the two old tables as source, and upload that into the new trable. – Laughing Vergil Jul 16 '18 at 22:26
3

You can use bcp out and bulk insert to achieve what you are trying to do.

You can use my script

Create a folder D:\BCP_OUT\ locally on the server where the bcp out files will reside.

/************************************************************************************************************************************************
Author      :   KIN SHAH    *********************************************************************************************************************
Purpose     :   Move data from one server to another or from one db to another *********************************************************************************************
DATE        :   05-28-2013  *********************************************************************************************************************
Version     :   1.0.0   *************************************************************************************************************************
RDBMS       :   MS SQL Server 2008R2 and 2012 & up  *************************************************************************************************
*************************************************************************************************************************************************/

-- save below output in a bat file by executing below in SSMS in TEXT mode. Review the OUTPUT and run the .bat file. This will create the bcp out files with data in them.
-- clean up: create a bat file with this command --> del D:\BCP_OUT\*.dat 

select '"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" '-- path to BCP.exe
        +  QUOTENAME(DB_NAME())+ '.'                                    -- Current Database
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.'            
        +  QUOTENAME(name)  
        +  ' out D:\BCP_OUT\'                                           -- Path where BCP out files will be stored
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_' 
        +  REPLACE(name,' ','') 
        + '.dat -T -E -SSERVERNAME\INSTANCE -n'                         -- ServerName, -E will take care of Identity, -n is for Native Format
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'                       -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'                     -- Optional to exclude any schema 
order by schema_name(schema_id)                         



--- Execute this on the destination server.database from SSMS.
--- Make sure the change the @Destdbname and the bcp out path as per your environment.

declare @Destdbname sysname
set @Destdbname = 'destination_database_Name'               -- Destination Database Name where you want to Bulk Insert in
select 'BULK INSERT '                                       -- Remember Tables **must** be present on destination Database
        +  QUOTENAME(@Destdbname)+ '.'
        +  QUOTENAME(SCHEMA_NAME(SCHEMA_ID))+'.' 
        +  QUOTENAME(name) 
        + ' from ''D:\BCP_OUT\'                             -- Change here for bcp out path
        +  REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'
        +  REPLACE(name,' ','') 
        +'.dat'' 
        with (
        KEEPIDENTITY,
        DATAFILETYPE = ''native'',  
        TABLOCK
        )'  + char(10) 
        + 'print ''Bulk insert for '+REPLACE(SCHEMA_NAME(schema_id),' ','') + '_'+  REPLACE(name,' ','')+' is done... '''+ char(10)+'go' 
from sys.tables
where is_ms_shipped = 0 and name <> 'sysdiagrams'           -- sysdiagrams is classified my MS as UserTable and we dont want it
and schema_name(schema_id) <> 'some_schema_exclude'         -- Optional to exclude any schema 
order by schema_name(schema_id)
1

You can use Import/Export in the SSMS for transferring data (right-click a database in Object Explorer -> Tasks -> Import Data... / Export Data ...) - pick SQL connection to DBv1 as a source and DBv2 as a destination and select objects to be transferred along with mapping in the next steps.

Here's Import/Export tutorial by Ray Barley

For where you have a difference in the schema you might try creating a view that would translate in the source table according to your new destination in the DBv2 and in the Import/Export dialog map that to appropriate object(s) in the destination.

0

Perhaps the easiest method for doing this would be to restore a copy of DBv1 as DBv2, then alter the table to be retained by adding a column, then copying the data from the table to be dropped into the new column.

For example, something like this:

CREATE TABLE dbo.retain
(
    retain_id int NOT NULL
        PRIMARY KEY CLUSTERED
    ...cols...
);

CREATE TABLE dbo.to_be_dropped
(
    to_be_dropped int NOT NULL
        PRIMARY KEY CLUSTERED
    , retain_id int NOT NULL
        FOREIGN KEY 
        REFERENCES dbo.retain(retain_id)
    , someData varchar(30) NOT NULL
);

ALTER TABLE dbo.retain
ADD someData varchar(30) NOT NULL;

UPDATE dbo.retain
SET someData = tbd.someData
FROM dbo.retain r
    INNER JOIN dbo.to_be_dropped tbd ON r.retain_id = tbd.retain_id;

Once you've validated the data has been correctly copied, simple drop the unneeded table:

DROP TABLE dbo.to_be_dropped;
0

Thanks to @Kin for referencing the bcp tool. I was able to solve my problem using bcp.

The way to do that for me was to export the data as a query of joinging

bcp "select A.column1, B.column2 from A join B on A.BId = B.Id" queryout C:\temp\AJoinB.dat -n -S serverHostName -T

Then

bcp MyNewTable in C:\temp\AJoinB.dat -n -S serverHostName -T

And that did it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.