We help IT Professionals succeed at work.
Get Started

Dynamically create insert statements for unicode data

942 Views
Last Modified: 2012-05-12
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
Database Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 13 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE