2

I have a table with a XML Column, each XML document size is 15kb and I have like 2 million rows.

I need to move this XML column into a new table. I tried:

insert into newtable 
    select xmlcolumn 
    from originaltable

but is taking too long.

Any other way to do it? What's the best way to achieve this?

0

1 Answer 1

3

You can use BCP OUT and BULK INSERT in native mode which will be super fast.

I just tested below script on [AdventureWorks2008R2].[Production].[ProductModel] and it work with XML data as well.

Alternatively, you can use SSIS to do it on a more frequent basis if you want.

Below is the script that will help you with BCP OUT and BULK INSERT :

BCP OUT

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" [AdventureWorks2008R2].[Production].[ProductModel] out C:\BCP_OUT\Production_ProductModel.dat -T -E -SSERVER\INSTNCENAME -n

BULK INSERT

BULK INSERT [AdventureWorks2008R2].[Production].[ProductModel_kin] from 'C:\BCP_OUT\Production_ProductModel.dat' 
        with (
        KEEPIDENTITY,
        DATAFILETYPE = 'native',  
        TABLOCK
        )
print 'Bulk insert for Production_ProductModel_kin is done'

Whole SCRIPT:

/************************************************************************************************************************************************
Author      :   KIN SHAH    *********************************************************************************************************************
Purpose     :   Move data from one server to another or JUST BCP OUT and BULK INSERT DATA FROM AND IN SQL SERVER !!!*********************************************************************************************
DATE        :   05-28-2013  *********************************************************************************************************************
Version     :   1.0.0   *************************************************************************************************************************
RDBMS       :   MS SQL Server 2008R2 and 2012   *************************************************************************************************
*************************************************************************************************************************************************/

-- save below output in a bat file by executing below in SSMS in TEXT mode
-- 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) 
2
  • Thanks Kin for the elaborated answer!, In this case I just want to move data from a xml column, to a new table inside the same database, that's why I was trying to use insert into with select.
    – Juan
    Mar 24, 2014 at 17:14
  • 2
    You can modify the script to do it ..
    – Kin Shah
    Mar 24, 2014 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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