[
https://issues.apache.org/jira/browse/SQOOP-1403?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14086305#comment-14086305
]
Keegan Witt commented on SQOOP-1403:
------------------------------------
Thanks for that suggestion. The syntaxes do appear very similar. One question
I've been wrestling with for a couple days now; are we sure the Oracle upsert
functionality is working?
I'm testing with a table like
*testTable*
||Column||Type||Props||
|k|int|PRIMARY KEY NOT NULL|
|col1|int|NOT NULL|
My statement would be built like
{code:sql}
MERGE INTO [testTable] USING ( k = ? )
WHEN MATCHED THEN
UPDATE SET v = ?
WHEN NOT MATCHED THEN
INSERT ( k, v ) VALUES ( ?, ? )
{code}
This is very similar to Oracle's syntax which would be
{code:sql}
MERGE INTO testTable USING dual ON ( k = ? )
WHEN MATCHED THEN
UPDATE SET v = ?
WHEN NOT MATCHED THEN
INSERT ( k, v ) VALUES ( ?, ? )
{code}
As you can see, in both cases the column names need to be inserted twice by
{{getPreparedStatement}}. The problem is, using the default (as Oracle
currently does) only inserts it once. Here's the relevant section of the
current implementation (from
{{org.apache.sqoop.mapreduce.UpdateOutputFormat.UpdateRecordWriter}})
{code:java}
for (SqoopRecord record : userRecords) {
record.write(stmt, 0);
stmt.addBatch();
}
{code}
In my case, this leads to an error like
{{com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the
parameter number 3.}}. I've looked and looked at this, and I don't see how
Oracle wouldn't have a similar error since the implementations are so similar
(but I don't have an Oracle instance to test against). Hopefully I'm missing
something here. Has anyone reported successfully using upsert with Oracle?
> Upsert export for SQL Server
> ----------------------------
>
> Key: SQOOP-1403
> URL: https://issues.apache.org/jira/browse/SQOOP-1403
> Project: Sqoop
> Issue Type: New Feature
> Components: connectors/sqlserver
> Reporter: Keegan Witt
> Assignee: Keegan Witt
>
> Same as has been done in SQOOP-327 and SQOOP-621 (and requested in
> SQOOP-1270), but for SQL Server.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
