Hmm,
I understand...

declare @lcSQLINS VARCHAR(MAX)
 exec dbo.XXXXX_InsertTableScript 'ORA',YourTable',ID=1',@lcSQLINS OUT
EXEC dbo.XXXXX_Print @lcSQLINS

IF NOT OBJECT_ID('dbo.XXXXX_GetTableStructure') IS NULL DROP FUNCTION dbo.XXXXX_GetTableStructure
GO
CREATE FUNCTION dbo.XXXXX_GetTableStructure(@lcTABLE NVARCHAR(128))
RETURNS  @loColumns TABLE (colid INT, name NVARCHAR(128), xtype INT, typename NVARCHAR(128), prec INT, scale INT, isnullable INT)
AS
BEGIN
 -- načti seznam položek tabulky
 IF CHARINDEX('tempdb.dbo',@lcTABLE)=0 BEGIN
    INSERT INTO @loColumns
      SELECT AA.COLID, AA.name, AA.xtype, AB.name, AA.prec, AA.scale, AA.isnullable 
        FROM syscolumns AA, systypes AB WHERE AA.id=OBJECT_ID(@lcTABLE) AND AA.xtype=AB.xusertype
        ORDER BY AA.colid
 END

 IF CHARINDEX('tempdb.dbo',@lcTABLE)>0 BEGIN
    INSERT INTO @loColumns
      SELECT AA.COLID, AA.name, AA.xtype, AB.name, AA.prec, AA.scale, AA.isnullable 
        FROM tempdb.dbo.syscolumns AA, tempdb.dbo.systypes AB WHERE AA.id=OBJECT_ID(@lcTABLE) AND AA.xtype=AB.xusertype
        ORDER BY AA.colid
 END


RETURN 
END --XXXXX_GetTableStructure
GO

IF NOT OBJECT_ID('dbo.XXXXX_SafetyString') IS NULL DROP FUNCTION dbo.XXXXX_SafetyString
GO
CREATE FUNCTION dbo.XXXXX_SafetyString(@lcVB VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS  
BEGIN
 DECLARE @lii INT
 SET @lii=1
 SET @lcVB=''''+REPLACE(@lcVB,'''','''''')+''''

 WHILE @lii<=31 BEGIN
   SET @lcVB=REPLACE(@lcVB,CHAR(@lii),  '''+CHAR('+CAST(@lii AS VARCHAR(3))+')+'''   )
   SET @lii=@lii+1
 END
 RETURN REPLACE(@lcVB,'+''''','')
END --XXXXX_SafetyString
GO

IF NOT OBJECT_ID('dbo.XXXXX_VBToHex') IS NULL DROP FUNCTION dbo.XXXXX_VBToHex
GO
CREATE FUNCTION [dbo].[XXXXX_VBToHex](@lcVB VARBINARY(MAX))
RETURNS VARCHAR(MAX) AS  
BEGIN
 DECLARE @lcRET VARCHAR(MAX),@liLEN INT, @lii INT
 SET @liLEN=LEN(@lcVB)
 SET @lcRET='0x'
 SET @lii=1

 WHILE @lii <= @liLEN BEGIN
   SET @lcRET=@lcRET+SUBSTRING(master.dbo.fn_varbintohexstr(SUBSTRING(@lcVB,@lii,1000)),3,2000)
   SET @lii=@lii+1000
 END

 RETURN @lcRET
END --XXXXX_VBToHex


IF NOT OBJECT_ID('dbo.XXXXX_InsertTableScriptORA') IS NULL DROP PROCEDURE dbo.XXXXX_InsertTableScriptORA
GO
CREATE PROCEDURE dbo.XXXXX_InsertTableScriptORA(@lcTABLE NVARCHAR(128), @lcSQLINSDEF VARCHAR(MAX) OUT)
AS
BEGIN
 DECLARE @lorsData CURSOR
 DECLARE @lcSQLIV NVARCHAR(MAX), @lcSQL NVARCHAR(MAX),@lcFIELD NVARCHAR(MAX), @lcTOKEN NVARCHAR(128), @liDROPGTS INT
 DECLARE @loData_name NVARCHAR(128), @loData_xtype int, @loData_xname NVARCHAR(128), @lodata_xprec INT, @loData_xscale INT, @loData_isnullable INT

 IF CHARINDEX('.',@lcTABLE)>0 BEGIN
    SET @lcTOKEN=REPLACE(REPLACE(REPLACE(LEFT(@lcTABLE,CHARINDEX('.',@lcTABLE)-1),'[',''),']',''),'"','')
    IF EXISTS(SELECT 1 FROM sys.servers WHERE NAME= @lcTOKEN) BEGIN
       SET @lcSQL='SELECT TOP 1 * INTO ##GTS FROM '+@lcTABLE+''
       EXEC sp_executesql @lcSQL
       SET @lcTABLE='tempdb.dbo.##GTS'
    END
 END

 SET @lcSQLIV=''

 -- načti seznam položek tabulky
 SET @lorsData=CURSOR LOCAL READ_ONLY FOR
   SELECT AA.name, AA.xtype, AA.typename, AA.prec, AA.scale, AA.isnullable 
     FROM XXXXX_GetTableStructure(@lcTABLE) AA
     WHERE AA.xtype<>189

 OPEN @lorsData
 WHILE 0 = 0 BEGIN
   FETCH NEXT FROM @lorsData INTO @loData_name, @loData_xtype, @loData_xname, @lodata_xprec, @loData_xscale, @loData_isnullable
   IF @@FETCH_STATUS <> 0 BREAK

   IF @loData_isnullable=1 BEGIN
      SET @lodata_xprec=CASE WHEN @lodata_xprec<4 THEN 4 ELSE @lodata_xprec END
   END

   SET @lcFIELD=CASE WHEN @loData_xtype = 34 THEN 'dbo.XXXXX_VBToHex(CAST(['+@loData_name+'] AS VARBINARY(MAX)))'
                     WHEN @loData_xtype IN (346,98) THEN 'CAST(['+@loData_name+'] AS TEXT)'
                     WHEN @loData_xtype IN (165,173) THEN 'master.dbo.fn_varbintohexstr(['+@loData_name+'])'
                     WHEN @loData_xtype = 36 THEN 'CAST(['+@loData_name+'] AS VARCHAR('+CAST(@lodata_xprec AS VARCHAR(38))+'))'
                     WHEN @loData_xtype IN (189) THEN 'master.dbo.fn_varbintohexstr(CAST(['+@loData_name+'] AS VARBINARY(8)))'
                     WHEN @loData_xtype IN (59,60,62,104,106,108,122) THEN 'CAST(['+@loData_name+'] AS VARCHAR('+CAST(@lodata_xprec AS VARCHAR(38))+'))'
                     WHEN @loData_xtype IN (48,52,56,127) THEN 'CAST(['+@loData_name+'] AS VARCHAR('+CAST(@lodata_xprec+1 AS VARCHAR(38))+'))'
                     WHEN @loData_xtype IN (40,41,42,43,58,61) THEN 'CONVERT(VARCHAR(19),['+@loData_name+'],121)'
                     ELSE 'dbo.XXXXX_SafetyString(['+@loData_name+'])' END

   SET @lcFIELD=CASE WHEN @loData_isnullable=1 THEN 'ISNULL(' ELSE '' END+
                CASE WHEN @loData_xtype IN (40,41,42,43,58,61) THEN '''''''''+' ELSE '' END+
                @lcFIELD+
                CASE WHEN @loData_xtype IN (40,41,42,43,58,61) THEN '+''''''''' ELSE '' END+
                CASE WHEN @loData_isnullable=1 THEN ',''NULL'')' ELSE '' END+'+'',''+'


   SET @lcSQLIV=@lcSQLIV+@lcFIELD
 END
 CLOSE @lorsData 
 DEALLOCATE @lorsData 

 SET @lcSQLINSDEF=LEFT(@lcSQLIV,LEN(@lcSQLIV)-5)

 IF NOT OBJECT_ID('tempdb.dbo.##GTS') IS NULL DROP TABLE ##GTS

END --XXXXX_InsertTableScriptORA
GO


IF NOT OBJECT_ID('dbo.XXXXX_InsertTableScript') IS NULL DROP PROCEDURE dbo.XXXXX_InsertTableScript
GO
CREATE PROCEDURE dbo.XXXXX_InsertTableScript(@lcTYPE VARCHAR(30), @lcTABLE NVARCHAR(128), @lcWHERE VARCHAR(MAX), @lcSQLINS VARCHAR(MAX) OUT)
AS
BEGIN
 SET NOCOUNT ON
 DECLARE @lcList NVARCHAR(MAX), @lcSQLI VARCHAR(MAX), @lcSQLINSDEF VARCHAR(MAX), @lcSHORTTABLE VARCHAR(128)

 IF NOT OBJECT_ID('tempdb.dbo.#XXT000') IS NULL DROP TABLE #XXT000
 CREATE TABLE #XXT000 (XX000 VARCHAR(MAX))

 SET @lcSHORTTABLE='X'+CASE WHEN CHARINDEX('.',@lcTABLE)=0 THEN @lcTABLE ELSE
                                REPLACE(REVERSE(LEFT(REVERSE(@lcTABLE),CHARINDEX('.',REVERSE(@lcTABLE))-1)),'#','') END


 IF @lcTYPE='MS' BEGIN
    EXEC dbo.XXXXX_InsertTableScriptMS @lcTABLE, @lcSQLINSDEF OUT
 END

 IF @lcTYPE='ORA' BEGIN
    EXEC dbo.XXXXX_InsertTableScriptORA @lcTABLE, @lcSQLINSDEF OUT
 END

 SET @lcSQLI='INSERT INTO "'+@lcSHORTTABLE+'"'

 SET @lcTABLE=CASE WHEN CHARINDEX('.',@lcTABLE)=0 THEN @lcTABLE ELSE
                        REVERSE(LEFT(REVERSE(@lcTABLE),CHARINDEX('.',REVERSE(@lcTABLE))-1)) END
 SET @lcList=N'INSERT INTO #XXT000 (XX000) SELECT '''+@lcSQLI+' VALUES (''+'+@lcSQLINSDEF+'+'');''  FROM "'+@lcTABLE+'" WITH (NOLOCK) '+CASE WHEN @lcWHERE IS NULL OR LEN(@lcWHERE)=0 THEN '' ELSE 'WHERE '+@lcWHERE END
 EXEC (@lcList)

 IF @lcTYPE='MS' BEGIN
    SET @lcSQLINS=REPLACE(REPLACE(REPLACE(REPLACE((SELECT XX000+CHAR(13) FROM #XXT000 FOR XML PATH('')),'
',CHAR(13)),'&','&'),'<','<'),'>','>')
 END
 IF @lcTYPE='ORA' BEGIN
    SET @lcSQLINS=
'DECLARE'+CHAR(13)+
'BEGIN'+CHAR(13)+
'EXECUTE IMMEDIATE ''ALTER SESSION SET NLS_DATE_FORMAT = ''''YYYY-MM-DD HH24:MI:SS'''''';'+CHAR(13)
    SET @lcSQLINS=@lcSQLINS+REPLACE(REPLACE(REPLACE(REPLACE((SELECT XX000+CHAR(13) FROM #XXT000 FOR XML PATH('')),'
',CHAR(13)),'&','''||''&''||'''),'<','<'),'>','>')
 END


 IF @lcTYPE='ORA' BEGIN
    SET @lcSQLINS=+@lcSQLINS+
'COMMIT;'+CHAR(13)+
'END;'+CHAR(13)+
'/'+CHAR(13)
 END

 IF NOT OBJECT_ID('tempdb.dbo.#XXT000') IS NULL DROP TABLE #XXT000

END -XXXXX_InsertTableScript
GO

>Hi Martina,
>
>My DML has to be scripted to hand over to execute in a production system. I cannot make those changes that way in this system. Once I create a dml script, I test execute this in the stage environment and validate before handing the script over to change management to run in production.
>
>Thanks
>
>>Hi,
>>
>>Why you dont' use SPT with bind parameters?
>>
>>

>>?SQLEXEC(MSSQLHandle,"SELECt * FROM sourcetable","xxc")
>>
>>SELE xxc
>>SCAN ALL
>>?SQLEXEC(ORACLEHandle,"INSERT INTO desttable (Unit_ID, Description, QuantityFK, Factor, Multiplier, Last_Update) VALUES (?xxc.Unit_ID, ?xxc.Description, ?xxc.QuantityFK, ?xxc.Factor, ?xxc.Multiplier, ?xxc.Last_Update)")
>>ENDSCAN
>>

>>
>>MartinaJ
>>
>>>I am trying to create insert statements from a table of data from SQL Server to execute against an Oracle table of similar structure. I am struggling with a couple of float columns that seem to be converting to scientific notation. I do not want the values converted. SSMS query displays these values straight up so why can't I get them straight up?
>>>
>>>This is my query to get my data out as Insert Statements but with no convert or cast causes an error.
>>>

>>>SELECT 'INSERT INTO UCS_UNIT (Unit_ID, Description, QuantityFK, Factor, Multiplier, Last_Update) VALUES(''' +
>>>	REPLACE(CAST(UnitID AS VARCHAR(36)),'-', '') + ''', ''' + Description + ''', ''' + REPLACE(CAST(QuantityFK AS VARCHAR(36)),'-', '') +
>>>	''', ''' + Factor + ''', ''' + Multiplier +
>>>	 ''', TO_DATE(''' + CONVERT(VARCHAR(19), LastUpdate, 121) +  ''', ''YYYY-MM-DD HH24:MI:SS''));' AS UCS_Unit
>>>FROM uom.Unit WHERE Active = 1;
>>>
>>>

>>>
>>>Trying to do some Cast and Convert comparisons gives me varying output but none are what I need.
>>>

>>>select UnitID, Factor, CONVERT(VARCHAR(50), Factor, 128) As ConvertFactor,
>>>CAST(Factor AS VARCHAR(50)) As CastFactor,
>>>CAST(CAST(Factor AS Float) AS VARCHAR(50))AS CompountFactor
>>> from uom.Unit where QuantityFK = 'D9930486-53C3-41BD-9444-010E931EF061';
>>>
>>>

>>>
>>>

>>>2551443.86974022	           2.55144386974022E6	      2.55144e+006	    2.55144e+006
>>>86400	                          86400	                              86400	            86400
>>>3153600000.3744	          3.1536000003744E9	      3.1536e+009	    3.1536e+009
>>>157679960.138506	          1.57679960138506E8	      1.5768e+008	    1.5768e+008
>>>2629822.96584	          2.62982296584E6	              2.62982e+006	    2.62982e+006
>>>
>>>

>>>
>>>This is a one time data load that I won't be doing again. I wanted something down and dirty.
>>>Only between 3,000 and 4000 rows.