Post reply

Input values in table with a storeprocedure

  • June 8, 2015 at 7:02 am

    #304729

    Hi SQL-Guru,

    I have the following Query.

    SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.PURCHLINE.DEL_INTERCOMPANYRETURNACTIONID' FROM SRC_AX.PURCHLINE WHERE DEL_INTERCOMPANYRETURNACTIONID IS NULL UNION

    SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.SALESLINE.DEL_INTERCOMPANYRETURNACTIONID' FROM SRC_AX.SALESLINE WHERE DEL_INTERCOMPANYRETURNACTIONID IS NULL UNION

    SELECT CAST(MULTISITEDEFAULTINVENTSITEID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS MULTISITEDEFAULTINVENTSITEID, 'SRC_AX.INVENTPARAMETERS.MULTISITEDEFAULTINVENTSITEID' FROM SRC_AX.INVENTPARAMETERS WHERE MULTISITEDEFAULTINVENTSITEID IS NULL

    The results from the query;

    DEL_INTERCOMPANYRETURNACTIONID (No column name)

    NULL SRC_FX.APBOXRESULTVOLUME.storeTemplateId

    NULL SRC_FX.APBOXTYPE.boxTemplateId

    My tabel is HST_MASTER.Control.

    I want to have this query in a stored procedure. What syntax stored procedure i need to make to fill my table.

    Gr

  • June 9, 2015 at 2:57 pm

    #1804137

    You'll need to substitute in the field names from the HST_MASTER.Control table that are relevant to the field values being inserted, as follows:

    INSERT INTO HST_MASTER.[Control] ([field name that corresponds to DEL_INTERCOMPANYRETURNACTIONID], [field name that corresponds to the text values])

    SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.PURCHLINE.DEL_INTERCOMPANYRETURNACTIONID'

    FROM SRC_AX.PURCHLINE

    WHERE DEL_INTERCOMPANYRETURNACTIONID IS NULL

    UNION

    SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.SALESLINE.DEL_INTERCOMPANYRETURNACTIONID'

    FROM SRC_AX.SALESLINE

    WHERE DEL_INTERCOMPANYRETURNACTIONID IS NULL

    UNION

    SELECT CAST(MULTISITEDEFAULTINVENTSITEID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS MULTISITEDEFAULTINVENTSITEID, 'SRC_AX.INVENTPARAMETERS.MULTISITEDEFAULTINVENTSITEID'

    FROM SRC_AX.INVENTPARAMETERS

    WHERE MULTISITEDEFAULTINVENTSITEID IS NULL;

    If this needs to be repeated over time, you may need to either TRUNCATE the table before each run, or do something to ensure that duplicate records aren't inserted due to the primary key. Seems like a reasonable challenge for you to figure out...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

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

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