[
https://issues.apache.org/jira/browse/SQOOP-1403?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14086327#comment-14086327
]
Keegan Witt commented on SQOOP-1403:
------------------------------------
If this does end up indeed being a problem, one solution might be to use
variables to store the column values once, like
{code:sql}
DECLARE @v nvarchar(max) SET @v = ?
DECLARE @k nvarchar(max) SET @k = ?
MERGE INTO [testTable] USING ( k = @k AND v = @v )
WHEN MATCHED THEN
UPDATE SET v = @v
WHEN NOT MATCHED THEN
INSERT ( k, v ) VALUES ( @k, @v )
{code}
Interestingly, {{nvarchar(max)}} seems to work for any data type (I assume
because SQL Server is doing a cast). Thanks to [~pmazak] for figuring this
out. Can you think of a better approach?
[~pmazak] also worked out a way to offer upsert functionality for SQL Server
older than 2008. Here's an example of that query
{code:sql}
DECLARE @v nvarchar(max) SET @v = ?
DECLARE @k nvarchar(max) SET @k = ?
IF EXISTS (SELECT TOP 1 1 FROM [testTable] WHERE k = @k)
UPDATE [testTable] SET v = @v WHERE k = @k
ELSE
BEGIN INSERT INTO [testTable] (k, v) VALUES (@k, @v)
END
{code}
We were thinking we would add another extra arg, something like
{{--conditional-upsert}} that would use this method to do upserts, otherwise
defaulting to the new {{MERGE}} method. Would you be opposed to that idea?
> 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)
