Post reply

DTS/T-SQL or What

  • March 15, 2004 at 2:54 pm

    #161707

    I need to move one field from a table into another table, along with a date stamp.

    The source data has NO date information.  I record when it it loaded in a SystemLog table as yyyymmdd

    I use an sp (spGetMaxReportDate) to retrieve the most recent datastamp, then another sp (spGetNextReportDate) to calculate the new datestamp. 

    The problem comes when trying to get the output of spGetNextReportDate to be used in a T-SQL statement to load the Archive table.  There is about 100,000 rows of monthly data from which I need 1 field to be copied to an Archive table, along with my datestamp.  The rest will be discarded.

    The SP's:

    spGetMaxReportDate - returns 20040101 (Jan 2004)

    spGetNextReportDate - returns 20040201 (Feb 2004)

    Which is exactly what I expect.  How can I link/select the sp Output into the T-Sql for an insert operation?

    I appreciate your insight and suggestions

     

  • March 15, 2004 at 6:22 pm

    #498877

    T-SQL approach

    -- Assuming you

    -- CREATE PROCEDURE spGetNextReportDate

    --  @resultsring nvarchar(8) OUTPUT

    __ AS SELECT @resultstring = ...

    declare @nextreport nvarchar(8)

    execute spGetNextReportDate @nextreport OUTPUT

    insert (column1, column2)

    select ArchiveColumn, @nextreport from archivetable where ....

    go

    I havent tried getting DTS to extract from a query in a while, but I think this T-SQL can be used as the source of the data instaed of the TABLE/VIEW.

     

    Peter Evans (__PETER Peter_)

Viewing 2 posts - 1 through 1 (of 1 total)

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