1. Specify column names when selecting and inserting data. This way you
know PRECISELY what is being transferred. Over reliance on the data
dictionary can lead to a severe case of LAZY DBA syndrome....
2. Use data conversion function to EXPLICITLY state the data type for
what is READ as well as what is INSERTED.
MSSQL conversion functions are CAST and CONVERT.
From Books on Line:
CAST and CONVERT
Explicitly converts an expression of one data type to another. CAST and
CONVERT provide similar functionality.
Syntax
Using CAST:
CAST ( expression AS data_type )
Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Arguments
expression
Is any valid Microsoft(r) SQL Server(tm) expression. For more
information, see Expressions.
data_type
Is the target system-supplied data type, including bigint and
sql_variant. User-defined data types cannot be used. For more
information about available data types, see Data Types.
length
Is an optional parameter of nchar, nvarchar, char, varchar, binary, or
varbinary data types.
style
Is the style of date format used to convert datetime or smalldatetime
data to character data (nchar, nvarchar, char, varchar, nchar, or
nvarchar data types), or the string format when converting float, real,
money, or smallmoney data to character data (nchar, nvarchar, char,
varchar, nchar, or nvarchar data types).
-----Original Message-----
From: Russell Fleming
[mailto:oracledba-ezmlmshield-x37935954.[Email address protected]
Sent: Tuesday, January 02, 2007 3:47 PM
To: LazyDBA Discussion
Subject: Copying data to SQLS Server from 10g
Nothing but stumbling blocks trying to do this simple task. I have
identical
tables in Oracle and MSS. I need to copy the data over to the MSS from
the
Oracle table. Database link to MSS is created and working properly. I
have
tried the following...
This does not work due to transaction errors.
INSERT INTO [Email Address Removed] FROM oracle_table
And the following does not work due to this error.
Warning(73,17): PLW-07202: bind type would result in conversion away
from
column type
DataRow oracle_table%ROWTYPE ;
CURSOR C1 IS SELECT * FROM oracle_table ;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO DataRow.col1, DataRow.col2, ....
INSERT INTO [Email Address Removed] (col1, col2, ...)
VALUES (DataRow.col1, DataRow.col2, .... )
END LOOP;
END;
I've tried using TO_CHAR and TO_NUMBER conversions of my record where
appropriate and it still complains about datatypes. Why is this so hard?
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page