We help IT Professionals succeed at work.

Dynamically create insert statements for unicode data

Hi,

I am using the following dynamic sql to create insert statements, so I can insert data from oracle into SQL Server.
The problem is that I am trying to create the statement so the values are preceded with N'' for unicode data, so the statement would be:

INSERT INTO TBL_TEMP(ID_L,DESCR) VALUES(N'A',N'¿¿¿¿¿')

I am struggling where the insertion of the N' should be in the sql syntax below.

Thank you
DECLARE @tableName VARCHAR(100)

SET @tableName = 'TBL_TEMP'

DECLARE cursCol CURSOR FAST_FORWARD
    FOR
        SELECT  A.COLUMN_NAME ,
                A.DATA_TYPE
        FROM    (   
				SELECT * FROM ORACLELINKEDSERVER..SYS.DBA_TAB_COLUMNS WHERE OWNER = 'GE' AND TABLE_NAME =@tableName                
                ) A
    OPEN cursCol
    DECLARE @string NVARCHAR(3000) --for storing the first half of INSERT statement
    DECLARE @stringData NVARCHAR(3000) --for storing the data (VALUES) related statement
    DECLARE @dataType NVARCHAR(1000) --data types returned for respective columns
    SET @string = 'INSERT INTO ' + @tableName + '('
    SET @stringData = ''

    DECLARE @colName NVARCHAR(50)

    FETCH NEXT FROM cursCol INTO @colName, @dataType

    IF @@fetch_status <> 0 
        BEGIN
            PRINT 'Table ' + @tableName + ' not found, processing skipped.'
            CLOSE cursCol
            DEALLOCATE cursCol
            RETURN
        END

    WHILE @@FETCH_STATUS = 0 
        BEGIN
            IF @dataType IN ( 'varchar', 'char', 'nchar', 'nvarchar' ) 
                BEGIN
                    SET @stringData = @stringData + '''' + '''+isnull('''''
                        + '''''+' + @colName + '+'''''
                        + ''''',''NULL'')+'',''+'
                END
            ELSE 
                IF @dataType IN ( 'text', 'ntext' ) --if the datatype is text or something else 
                    BEGIN
                        SET @stringData = @stringData
                            + '''''''''+isnull(cast(' + @colName
                            + ' as varchar(2000)),'''')+'''''',''+'
                    END
                ELSE 
                    IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
                        BEGIN
                            SET @stringData = @stringData
                                + '''convert(money,''''''+isnull(cast('
                                + @colName
                                + ' as varchar(200)),''0.0000'')+''''''),''+'
                        END
                    ELSE 
                        IF @dataType = 'datetime' 
                            BEGIN
                                SET @stringData = @stringData
                                    + '''convert(datetime,' + '''+isnull('''''
                                    + '''''+convert(varchar(200),' + @colName
                                    + ',121)+'''''
                                    + ''''',''NULL'')+'',121),''+'
                            END
                        ELSE 
                            IF @dataType = 'image' 
                                BEGIN
                                    SET @stringData = @stringData
                                        + '''''''''+isnull(cast(convert(varbinary,'
                                        + @colName
                                        + ') as varchar(6)),''0'')+'''''',''+'
                                END
                            ELSE --presuming the data type is int,bit,numeric,decimal 
                                BEGIN
                                    SET @stringData = @stringData + ''''
                                        + '''+isnull('''''
                                        + '''''+convert(varchar(200),'
                                        + @colName + ')+'''''
                                        + ''''',''NULL'')+'',''+'
                                END

            SET @string = @string + @colName + ','

            FETCH NEXT FROM cursCol INTO @colName, @dataType
        END
    DECLARE @query NVARCHAR(4000)

    SET @query = 'SELECT ''' + SUBSTRING(@string, 0, LEN(@string))
        + ') VALUES(''+' + SUBSTRING(@stringData, 0, LEN(@stringData) - 2)
        + '''+'')'' FROM ORACLELINKEDSERVER..GE.' + @tableName

    EXEC sp_executesql @query

    CLOSE cursCol
    DEALLOCATE cursCol

Open in new window

Comment
Watch Question

Commented:
I think, there is no need of N . Try without N
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
I am not sure what you are trying to achieve with the query above however I can tell that text and ntext don't mix and same with char vs nchar, or varchar vs nvarchar.

That litle "n" means UNICODE for SQL and this is where you are getting data conversion from ORACLE UTF8 to SQL NON-UNICODE

I also suggest use nvarchar(max) in SQL instead of ntext or other like nvarchar(3000).
crompnkData Management Specialist

Author

Commented:
Hi,

I'm trying to create insert statements so I can move data from an oracle table into an SQL table using a linked sever. I thought that I would need to create individual insert statements with the 'n' before the varchar values to convert the data from Oracle unicode, otherwise I just get '??????' characters.
I'm trying to do this dynamically as there are a lot of records in the Oracle table to import.
It didn't work when I tried selecting INTO a table.

Thanks
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
Can you give an example where this failed? Just destination column data type in SQL and source column data type in ORACLE.

Also, what data provider are you using in your linked server and what versions are you using on both sides and on which platform - 32 or 64?

Are you using MSDAORA SQL provider for your link server by any chance? If yes then I suggest you better get the "Oracle Data Provider for .NET"
http://www.oracle.com/technetwork/topics/dotnet/index-085163.html
crompnkData Management Specialist

Author

Commented:
Hi,
The destination column in SQL Server 2005 is nvarchar(max) and the source column in Oracle is varchar2(1000)

I'm using SQL Server 2005 and Oracle9i (version 9.2.0.1.0) and the Patch Oracle is 9.2.0.7.0 on a 32 bit XP platform

The linked server privide is MSDAORA

Should I use OraOLEDB.Oracle
crompnkData Management Specialist

Author

Commented:
I tried it with OraOLEDB.Oracle provider, and recieved this error:

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLELINKEDSERVER" supplied inconsistent metadata for a column. The column "ID_L" (compile-time ordinal 1) of object ""GE"."TBL_TEMP"" was reported to have a "DBTYPE" of 129 at compile time and 130 at run time.
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
If posible I would definitely use OraOLEDB. I had to do similar thing on SQL 2005 32bit and all I needed at that time was to install the ORACLE client on my SQL box. For my speciffic data transfers I used "Oracle9i Release 2 (9.2.0.4)" but I can't see why "Oracle9i Release 2 ODAC 92070" won't work.
http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html

I believe MSDAORA (tied into the (in)famous MCAD) was developed based on ORACLE 7 and not sure how much more it got updated since.
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
tied into the (in)famous MCAD - should be:"tied into the (in)famous MDAC" sorry about typo.
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
If it's not confidential, could you post the two table structures in SQL and ORACLE? Change column name if you'd like but please leave data type/length unchanged.
Database Analyst
CERTIFIED EXPERT
Commented:
It may be as simple as using OPENQUERY instead of direct insert with full name qalifier - can you try the query below?

INSERT INTO dbo.TBL_TEMP
SELECT * FROM OPENQUERY(SAMPLE, 'SELECT ID_L, DESCR FROM TBL_TEMP')


If this doesn't work we need to CAST/CONVERT to match the data types in order to avoid implicit data conversion from the driver and that's why I asked for the tables structure from both sides.
crompnkData Management Specialist

Author

Commented:
Oracle table:

CREATE TABLE "GE"."TBL_TEMP" ("ID_L" VARCHAR2(32 byte) NOT NULL,
    "ID_LAN" VARCHAR2(32 byte) NOT NULL, "DESCR" VARCHAR2(1000
    byte),
    CONSTRAINT "PK_TBL_TEMP_1" PRIMARY KEY("ID_L",
    "ID_LAN")
    USING INDEX  
    TABLESPACE "USERS"
    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
    2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255)
     
    TABLESPACE "USERS" PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS
    255
    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
    2147483645 PCTINCREASE 0)
    LOGGING

SQL table:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_TEMP](
      [ID_L] [nvarchar](max) COLLATE Cyrillic_General_CI_AI NULL,
      [ID_LAN] [nvarchar](max) COLLATE Cyrillic_General_CI_AI NULL,
      [DESCR] [nvarchar](max) COLLATE Cyrillic_General_CI_AI NULL
) ON [PRIMARY]
crompnkData Management Specialist

Author

Commented:
Hi
Many thanks lcohan
the sql worked when I used the openquery with the OraOLEDB.Oracle provider
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
Glad to hear that and for the future please keep in mind that you may need to explicitely cast the fileds in openquery statement at source (inside select) and destination - the outside SELECT * FROM OPENQUERY....

Other than that I believe you could have your SQL table as

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_TEMP](
      [ID_L] [nvarchar](32) COLLATE Cyrillic_General_CI_AI NULL,
      [ID_LAN] [nvarchar](32) COLLATE Cyrillic_General_CI_AI NULL,
      [DESCR] [nvarchar](max) COLLATE Cyrillic_General_CI_AI NULL
) ON [PRIMARY]


instead of nvarchar(max) for the ID_L and ID_LAN as I can't see those changing data type at the source. Either way you are covered for DESCR with nvarchar(max) and as far the other two remaining as they are still not a space waste in SQL just because of the data type.