Home
%3CLINGO-SUB%20id%3D%22lingo-sub-317163%22%20slang%3D%22en-US%22%3EMigrating%20UTF8%20data%20from%20Oracle%20to%20SQL%20Server%20using%20SSMA%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-317163%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Aug%2009%2C%202012%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EBefore%20we%20could%20begin%20our%20Data%20Migration%2C%20you%E2%80%99ll%20need%20to%20clearly%20understand%20what%20is%20%3CB%3E%20UTF8%20is%20the%208-bit%20encoding%20of%20Unicode%3F%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3EIt%20is%20a%20variable-width%20encoding%20and%20a%20strict%20superset%20of%20ASCII.%20This%20means%20that%20each%20and%20every%20character%20in%20the%20ASCII%20character%20set%20is%20available%20in%20UTF-8%20with%20the%20same%20code%20point%20values.%20One%20Unicode%20character%20can%20be%201%20byte%2C%202%20bytes%2C%203%20bytes%2C%20or%204%20bytes%20in%20UTF-8%20encoding.%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20Oracle%E2%80%99s%20support%20for%20Unicode%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3EOracle%20started%20supporting%20Unicode%20as%20a%20database%20character%20set%20in%20version%207.Unicode%20characters%20can%20be%20stored%20in%20an%20Oracle%20database%20in%20two%20ways.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20%3CI%3E%20Unicode%20database%20%3C%2FI%3E%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3EThe%20end%20user%20can%20create%20a%20Unicode%20database%20that%20enables%20you%20to%20store%20UTF-8%20encoded%20characters%20as%20SQL%20CHAR%20datatypes%20(CHAR%2C%20VARCHAR2%2C%20CLOB%2C%20and%20LONG).%20For%20this%20you%20have%20to%20specify%20the%20database%20character%20set%20as%20UTF-8%20when%20creating%20the%20database.%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20SQL%20Server%20support%20for%20Unicode%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3ESQL%20Server%20started%20supporting%20Unicode%20as%20database%20character%20set%20from%20version%207.%20It%20uses%20UCS-2%20character%20encoding%20for%20storing%20Unicode%20data.%3C%2FP%3E%0A%20%20%3CP%3E%3CI%3E%20Now%20let%E2%80%99s%20move%20on%20to%20how%20to%20Migrate%20Oracle%20database%20with%20UTF8%20data%20to%20SQL%20Server%20using%20SSMA.%20%3C%2FI%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20Unicode%20database%20solution%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIf%20database%20is%20Unicode%20database%2C%20then%20Oracle%20is%20configured%20to%20store%20multi-byte%20strings%20in%20VARCHAR2%20%2F%20CHAR%20columns%20or%20variables.%20In%20this%20case%2C%20you%20can%20use%20customized%20mappings%20to%20map%20the%20character%20types%20to%20Unicode%20types%20in%20SQL%20Server.%3C%2FP%3E%0A%20%20%3CTABLE%3E%0A%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CP%3E%3CB%3E%20Oracle%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3CTD%3E%3CP%3E%3CB%3E%20SQL%20Server%202005%2F2008%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%20%20%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CP%3EVARCHAR2%3C%2FP%3E%0A%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3CTD%3E%3CP%3Envarchar%3C%2FP%3E%0A%20%20%20%20%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CP%3ECHAR%3C%2FP%3E%0A%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3CTD%3E%3CP%3Enchar%3C%2FP%3E%0A%20%20%20%20%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%20%3CTR%3E%0A%20%20%20%20%3CTD%3E%3CP%3ELONG%2C%20CLOB%3C%2FP%3E%0A%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3CTD%3E%3CP%3Envarchar(max)%3C%2FP%3E%0A%20%20%20%20%3C%2FTD%3E%0A%20%20%20%3C%2FTR%3E%0A%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EOtherwise%2C%20non-ASCII%20strings%20can%20be%20distorted%20during%20data%20migration%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20Unicode%20data%20type%20solution%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3EFor%20Unicode%20datatype%20solution%2C%20source%20strings%20declared%20as%20national%20(NVARCHAR2%20and%20NCHAR)%20are%20automatically%20mapped%20to%20%3CB%3E%20%3CI%3E%20nvarchar%20%3C%2FI%3E%20%3C%2FB%3E%20and%20%3CB%3E%20%3CI%3E%20nchar.%20%3C%2FI%3E%20%3C%2FB%3E%20Large%20object%20types%20like%20%3CB%3E%20%3CI%3E%20nclob%20%3C%2FI%3E%20%3C%2FB%3E%20is%20automatically%20mapped%20to%20%3CB%3E%20%3CI%3E%20nvarchar(max).%20%3C%2FI%3E%20%3C%2FB%3E%20So%20there%20is%20no%20need%20to%20change%20the%20default%20mappings%20for%20the%20above%20datatypes.%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20Customizing%20Data%20Type%20Mappings%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3EYou%20can%20customize%20type%20mappings%20at%20the%20project%20level%2C%20object%20category%20level%20(such%20as%20all%20tables)%2C%20or%20object%20level.%20Settings%20are%20inherited%20from%20the%20higher%20level%20unless%20they%20are%20overridden%20at%20a%20lower%20level.%20For%20example%2C%20if%20you%20map%20smallmoney%20to%20money%20at%20the%20project%20level%2C%20all%20objects%20in%20the%20project%20will%20use%20this%20mapping%20unless%20you%20customize%20the%20mapping%20at%20the%20object%20or%20category%20level.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EThe%20following%20procedure%20shows%20how%20to%20map%20data%20types%20at%20the%20project%2C%20database%2C%20or%20object%20level%3A%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CB%3E%20To%20map%20data%20types%20follow%20the%20below%20steps%3A%20%3C%2FB%3E%3C%2FP%3E%0A%20%20%3CP%3ETo%20customize%20data%20type%20mapping%20for%20the%20whole%20project%2C%20open%20the%20%3CB%3E%20Project%20Settings%20%3C%2FB%3E%20dialog%20box%3A%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3Ea.%20On%20the%20%3CB%3E%20Tools%20%3C%2FB%3E%20menu%2C%20select%20%3CB%3E%20Project%20Settings%20%3C%2FB%3E%20.%3C%2FP%3E%0A%20%20%3CP%3Eb.%20In%20the%20left%20pane%2C%20select%20%3CB%3E%20Type%20Mapping%20%3C%2FB%3E%20.%20The%20type%20mapping%20chart%20and%20buttons%20appear%20in%20the%20right%20pane.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EOr%2C%20to%20customize%20data%20type%20mapping%20at%20the%20database%2C%20table%2C%20view%2C%20or%20stored%20procedure%20level%2C%20select%20the%20database%2C%20object%20category%2C%20or%20object%20in%20Oracle%20Metadata%20Explorer%3A%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3Ec.%20In%20Oracle%20Metadata%20Explorer%2C%20select%20the%20folder%20or%20object%20to%20customize.%3C%2FP%3E%0A%20%20%3CP%3Ed.%20In%20the%20right%20pane%2C%20click%20the%20%3CB%3E%20Type%20Mapping%20%3C%2FB%3E%20tab.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3E%20Author%20%3A%20Ajay(MSFT)%2C%20SQL%20Developer%20Engineer%2C%20Microsoft%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3E%20Reviewed%20by%20%3A%20Smat%20(MSFT)%2C%20SQL%20Escalation%20Services%2C%20Microsoft%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-317163%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Aug%2009%2C%202012%20Before%20we%20could%20begin%20our%20Data%20Migration%2C%20you%E2%80%99ll%20need%20to%20clearly%20understand%20what%20is%20UTF8%20is%20the%208-bit%20encoding%20of%20Unicode%3F%20It%20is%20a%20variable-width%20encoding%20and%20a%20strict%20superset%20of%20ASCII.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-317163%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESSMA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719976%22%20slang%3D%22en-US%22%3ERe%3A%20Migrating%20UTF8%20data%20from%20Oracle%20to%20SQL%20Server%20using%20SSMA%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719976%22%20slang%3D%22en-US%22%3E%3CP%3ETo%20update%20here%2C%20with%20SQL%20Server%202019%20and%20Azure%20SQL%20Database%20UTF-8%20is%20supported%20in%20addition%20to%20UTF-16.%20More%20details%20in%26nbsp%3B%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fcollations%2Fcollation-and-unicode-support%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fcollations%2Fcollation-and-unicode-support%3C%2FA%3E%3C%2FFONT%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
First published on MSDN on Aug 09, 2012

Before we could begin our Data Migration, you’ll need to clearly understand what is UTF8 is the 8-bit encoding of Unicode?

It is a variable-width encoding and a strict superset of ASCII. This means that each and every character in the ASCII character set is available in UTF-8 with the same code point values. One Unicode character can be 1 byte, 2 bytes, 3 bytes, or 4 bytes in UTF-8 encoding.

Oracle’s support for Unicode

Oracle started supporting Unicode as a database character set in version 7.Unicode characters can be stored in an Oracle database in two ways.

Unicode database

The end user can create a Unicode database that enables you to store UTF-8 encoded characters as SQL CHAR datatypes (CHAR, VARCHAR2, CLOB, and LONG). For this you have to specify the database character set as UTF-8 when creating the database.

SQL Server support for Unicode

SQL Server started supporting Unicode as database character set from version 7. It uses UCS-2 character encoding for storing Unicode data.

Now let’s move on to how to Migrate Oracle database with UTF8 data to SQL Server using SSMA.

Unicode database solution

If database is Unicode database, then Oracle is configured to store multi-byte strings in VARCHAR2 / CHAR columns or variables. In this case, you can use customized mappings to map the character types to Unicode types in SQL Server.

Oracle

SQL Server 2005/2008

VARCHAR2

nvarchar

CHAR

nchar

LONG, CLOB

nvarchar(max)

Otherwise, non-ASCII strings can be distorted during data migration

Unicode data type solution

For Unicode datatype solution, source strings declared as national (NVARCHAR2 and NCHAR) are automatically mapped to nvarchar and nchar. Large object types like nclob is automatically mapped to nvarchar(max). So there is no need to change the default mappings for the above datatypes.

Customizing Data Type Mappings

You can customize type mappings at the project level, object category level (such as all tables), or object level. Settings are inherited from the higher level unless they are overridden at a lower level. For example, if you map smallmoney to money at the project level, all objects in the project will use this mapping unless you customize the mapping at the object or category level.

The following procedure shows how to map data types at the project, database, or object level:

To map data types follow the below steps:

To customize data type mapping for the whole project, open the Project Settings dialog box:

a. On the Tools menu, select Project Settings .

b. In the left pane, select Type Mapping . The type mapping chart and buttons appear in the right pane.

Or, to customize data type mapping at the database, table, view, or stored procedure level, select the database, object category, or object in Oracle Metadata Explorer:

c. In Oracle Metadata Explorer, select the folder or object to customize.

d. In the right pane, click the Type Mapping tab.

Author : Ajay(MSFT), SQL Developer Engineer, Microsoft

Reviewed by : Smat (MSFT), SQL Escalation Services, Microsoft

1 Comment
Microsoft

To update here, with SQL Server 2019 and Azure SQL Database UTF-8 is supported in addition to UTF-16. More details in https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support