Need Help insolving this Query

  • Hi need Help to solve Below Query ..

    i have a select statment which returns one or more than one record

    if it returns more than one record i need to insert into another table else i need to stop insert

    BEGIN

    CREATE TABLE #TempA

    (

    SOFTWRARE_ID varchar(12),

    LOGIN_ID varchar(50),

    )

    CREATE TABLE #TempB

    (

    SOFTWRARE_ID varchar(12),

    LOGIN_ID varchar(50),

    )

    INSERT INTO #TempA

    values('A','Login1')

    --INSERT INTO #TempA

    --values('A','Login2')

    --INSERT INTO #TempA

    --values('A','Login3')

    insert into #TempB

    select SOFTWRARE_ID,LOGIN_ID from #TempA

    where COUNT(LOGIN_ID)>1

    drop table #TempA

    drop table #TempB

    END

  • insert into #TempB

    select SOFTWRARE_ID,LOGIN_ID from #TempA

    GROUP BY SOFTWRARE_ID

    HAVING COUNT(LOGIN_ID)>1

    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
  • sorry.

    With above reply it Giving following Error Message

    Column '#TempA.LOGIN_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  • ramineni.suresh661 (7/26/2011)


    sorry.

    With above reply it Giving following Error Message

    Column '#TempA.LOGIN_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    What's unclear about that? Put that column in the group by and it'll work!

  • Ninja's_RGR'us (7/26/2011)


    ramineni.suresh661 (7/26/2011)


    sorry.

    With above reply it Giving following Error Message

    Column '#TempA.LOGIN_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    What's unclear about that? Put that column in the group by and it'll work!

    No it won't, because he wants a count by software and to insert the details if there are more than one login.

    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
  • Ok, but you'll leave him waiting like that for the real answer? 😉

  • Only while I load up Management Studio, write and test it.

    INSERT INTO #TempB (SOFTWRARE_ID, LOGIN_ID)

    SELECT SOFTWRARE_ID, LOGIN_ID

    FROM

    (SELECT SOFTWRARE_ID,LOGIN_ID, COUNT(*) OVER (PARTITION BY SOFTWRARE_ID) AS LoginCount FROM #TempA)sub

    WHERE LoginCount>1

    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

Viewing 7 posts - 1 through 6 (of 6 total)

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