Insert statement within IF Exists using other data

Question

I have a simple stored procedure that looks like so:

IF EXISTS (SELECT tag, campaignID as campaignID
       FROM   Tags_Campaign_Watchlist
       WHERE  tag IN (SELECT *
                      FROM   dbo.splitstring (@tags)))

This returns the output of:

enter image description here

My goal is that during this statement for any matches it finds in the table, insert the campaign ID into another table called TableB, Just not sure how to make this happen during the check of each tag in the string it loops through.

EDIT

At the bottom of the statement where I do the insert, I need to pass to it the @lastID from the very first insert up top. Is this possible?

If i pass @tags = 'house,car,truck and the last inserts where 1,2,3 it will add the campaignID for house as well as 1, then the campaignID for car and 2 as the ID.

            AS
BEGIN
    SET NOCOUNT ON;
    BEGIN
        INSERT INTO Tags_Accounts (data, dataType, dataLocation, tag, whoAdded, whenAdded)
        SELECT @dbData,
               @dbDataType,
               @location,
               Name,
               @ntid,
               GETDATE()
        FROM   dbo.splitstring (@tags);

        DECLARE @lastID int
        SET @lastID = SCOPE_IDENTITY()

    END
END


--If one of the tags added is part of a campaign watchlist, add it to the watchlist data table.
IF EXISTS (SELECT tag, campaignID as campaignID
           FROM   Tags_Campaign_Watchlist
           WHERE  tag IN (SELECT *
                          FROM   dbo.splitstring (@tags)))

        BEGIN

            INSERT INTO Tags_Campaign_Watchlist_Data (campaignID, tagID)
            SELECT campaignID
            FROM   Tags_Campaign_Watchlist
                   WHERE  tag IN (SELECT *
                                  FROM   dbo.splitstring (@tags))

        END

UPDATE 2

DECLARE @tags VARCHAR(MAX)
DECLARE @lastID int


SET @tags = 'bob,cat,carl,withdraw,awesome';
SET @lastID = '1';

BEGIN
IF EXISTS (SELECT tag, campaignID as campaignID
       FROM   Tags_Campaign_Watchlist
       WHERE  tag IN (SELECT *
                      FROM   dbo.splitstring (@tags)))

        INSERT INTO Tags_Campaign_Watchlist_Data (campaignID, tagID)
        (SELECT campaignID, @lastID
        FROM   Tags_Campaign_Watchlist
               WHERE  tag IN (SELECT *
                              FROM   dbo.splitstring (@tags)))

    END

This returns

Msg 515, Level 16, State 2, Line 14 Cannot insert the value NULL into column 'tag', table 'local.dbo.Tags_Campaign_Watchlist_Data'; column does not allow nulls. INSERT fails. The statement has been terminated.

I dont have the column TAG in watchlist data, jsut tagid and campaignID

M.Ali · Accepted Answer · 2013-11-26 19:23:41Z

1

INSERT INTO TableB (campaignID)
SELECT campaignID 
FROM   Tags_Campaign_Watchlist
       WHERE  tag IN (SELECT *
                      FROM   dbo.splitstring (@tags))

Update For Edit

--If one of the tags added is part of a campaign watchlist, add it to the watchlist data table.
    IF EXISTS (SELECT *
               FROM   Tags_Campaign_Watchlist
               WHERE  tag IN (SELECT *
                              FROM   dbo.splitstring (@tags)))

            BEGIN

                INSERT INTO Tags_Campaign_Watchlist_Data (campaignID, tagID)
                SELECT campaignID, TagID
                FROM   Tags_Campaign_Watchlist
                       WHERE  tag IN (SELECT *
                                      FROM   dbo.splitstring (@tags))

            END

edited Nov 26, 2013 at 19:23

answered Nov 26, 2013 at 17:34

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SBB Over a year ago

Made adjustment to original post to include the rest of the SP

M.Ali Over a year ago

@SBB in your Insert statement you only selected campaignID from table Tags_Campaign_Watchlist , all you needed was campaignID and also TagID in your select statement.

SBB Over a year ago

I tried that but it still didnt work .. Is it only getting the @lastID one time at the end or does it grab it for each insert?

M.Ali Over a year ago

The value you are assigning to your Tag variable, just do a simple select by passing that value to your IN operator with split function and see how many rows you get back.

M.Ali Over a year ago

@SBB you need to remove the Parenthesis around your SELECT statement, see my answer for that.

|

Collectives™ on Stack Overflow

Insert statement within IF Exists using other data

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related