Post reply

How to get the duplicate count

  • March 5, 2010 at 5:41 am

    #217492

    Hai To all

    this is a query

    WITH A

    AS

    (

    begin

    declare @tblname varchar(8000)

    declare @qry varchar(Max)

    begin

    select @tblname= SUBSTRING(a.Tablename,5,12) From (

    select DISTINCT 'TableName'=convert(char(25),t.TABLE_NAME)

    from sysindexes i, INFORMATION_SCHEMA.TABLES t

    where t.TABLE_NAME = object_name(i.id)

    and t.TABLE_TYPE = 'BASE TABLE'

    and convert(char(25),t.TABLE_NAME) not in ('ASMASTER')) as a

    set @qry= 'SELECT ROW_NUMBER() OVER (PARTITION BY ASID,PAGEID ORDER BY PAGEID) [DUP],asid,pageid FROM'+space(1)+'DVS14D09A.dbo.ASPM' + @tblname +''

    end

    EXEC (@qry)

    end

    )

    SELECT * FROM A

    WHERE DUP>1

    This is totally 500 tables some of the duplicate record in these any one table; i have to pass the tablename dynamically inner loop was exeucted but common table expression is used to remove the duplicate entry; to through the error

    like as

    Msg 156, Level 15, State 1, Line 2

    Incorrect syntax near the keyword 'begin'.

    Msg 102, Level 15, State 1, Line 16

    Incorrect syntax near ')'.

    pls any one to help this

    Thanks;

    Rahman

  • March 5, 2010 at 6:06 am

    #1129106

    A CTE is a single query, not a whole set of statements, and it must be a single select, no exec.

    If you want dynamic SQL and exec, you'll need to use a temp table and insert the executed statement into that, then select from the temp 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

  • March 7, 2010 at 6:41 pm

    #1129815

    I think you can do other ways better

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

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