Post reply

Copying rows from AS400 table into SQL table based on a sequence field.

  • December 10, 2017 at 1:55 pm

    #330419

    Hello,

     

    I am using SQL Server 2014 I have created a table, which I am going to have data copied from AS400on a nightly basis.  I have done thissuccessfully for few tables.  My problemis the table I am copying from AS400 does not have any dates so I can’t queryand upload the latest data to SQL table using dates.  The only field I think I can use is asequence field called SEQNum.  What Iwant is to only copy data from the AS400 with the latest SEQNum not in SQLtable.    Below are table fields:

     

    Select SEQNum, Address1, Address2, City, State, ZipCode

    From HSNAM

     

    Any tips will be appreciated how I can copy the latest datafrom AS400 into SQL table.  Thank you.

  • December 11, 2017 at 5:20 am

    #1971519

    Are you using SSIS?
    If so, you can set up a variable to hold the last SEQNum found on the SQL side, say we call it LastNum.  Then set up another variable to hold the command to select the data from AS400.  This variable is populated using the Expression Builder (note the quotes):

    " SELECT SEQNum, Address1, Address2, City, State, ZipCode From HSNAM WHERE SEQNum > " + @[$User::LastNum]

    Then in your Data Flow OLD DB Source, you use Data Access Mode dropdown SQL command from variable and use the variable name you created with Expression Builder.

  • December 16, 2017 at 12:59 am

    #1972370

    Thanks for the tip I appreciate your help.  This will help me out.

  • December 16, 2017 at 3:50 am

    #1972374

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply