Post reply

Implementing INSERT INTO with condition

  • June 22, 2010 at 4:48 am

    #224120

    I have a Destination Table having columns :

    UserId,Drivecode,Intrested,DriveID

    I am running a stored procedure which is having tabletype parameter.

    I am using Insert query in Stored procedure as following to insert the records:

    INSERT INTO DestinationTable(UserId,DriveCode,Intrested,DriveId)

    SELECT userid,DriveCode,0,DriveID

    FROM @paramtertable

    Now i want if A drivecode and UserID value in @parametertable is already there in destination table then this query should not insert the records in destination table from @parametertable

    Please suggest how to modify the query to use condition to check for the existence of DriveCode and USerID before existence.

  • June 22, 2010 at 4:56 am

    #1183220

    Add

    WHERE NOT EXISTS (<check for matching rows in destination table>)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 22, 2010 at 4:58 am

    #1183222

    try:

    INSERT INTO DestinationTable(UserId,DriveCode,Intrested,DriveId)

    SELECT p.userid,p.DriveCode,p.0,p.DriveID

    FROM @paramtertable p

    LEFT JOIN DestinationTable d

    ON d.UserId = p.UserId

    AND d.DriveCode = p.DriveCode

    WHERE d.UserId IS NULL

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

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

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