Most tricky

• Jules Bonnot

  SSCertifiable

  Points: 5488

  More actions

  March 13, 2007 at 7:46 am

  #177123

  moved to sql 2000

   

  www.sql-library.com[/url]

• Julie Hargraves

  SSCarpal Tunnel

  Points: 4457

  More actions

  March 13, 2007 at 7:59 am

  #694686

  I would think you would just want to add an identity column and then specify the columns you want to insert as follows:

  create table tab (id INT IDENTITY(1,1), [values] varchar(100))

  insert into tab

   ( [values] )

  select 'val' union all

  select 'val' union all

  select 'val' union all

  select 'val' union all

  select 'val'

  SELECT * From tab

• Jason Selburg

  SSC-Insane

  Points: 24641

  More actions

  March 14, 2007 at 6:18 pm

  #695118

  CREATE TABLE #tmp (yourField varchar(100))

   

  INSERT INTO #tmp (yourField)

              select 'Val1'

    union all select 'Val1'

    union all select 'Val1'

    union all select 'Val1'

    union all select 'Val1'

    union all select 'Val2'

    union all select 'Val2'

    union all select 'Val2'

    union all select 'Val3'

    union all select 'Val4'

    union all select 'Val5'

    union all select 'Val5'

    union all select 'Val5'

   

  SELECT

    row_number() OVER (PARTITION BY yourField ORDER BY yourField) AS rn

    ,yourField

  FROM #tmp

   

  DROP TABLE #tmp

  ______________________________________________________________________

  Personal Motto: Why push the envelope when you can just open it?

  If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

  Jason L. Selburg