sqoop-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Keegan Witt (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (SQOOP-1403) Upsert export for SQL Server
Date Tue, 05 Aug 2014 14:42:13 GMT

    [ 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)

Mime
View raw message