Help needed to update field with Page Seq Nbr

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  February 21, 2007 at 2:04 pm

  #117752

  I have a table with the following columns:

  Page_Seq (int)    VendorName varchar(50)     VendorID varchar(6)

  The data is imported from a fixed field text file with just the VendorName and

  VendorID.  I need to populate the Page_Seq column with increments of 1 for

  every 13th row.

  Thus:

  Page_Seq          VendorName          VendorID

  1                      MyVendor             000001

  1                      MyVendor             000001

  1                      MyVendor             000001

  1                      MyVendor             000001

  1                      MyVendor             000001

  1                      MyVendor             000001

  1                      MyVendor             000001

  1                      MyVendor             000001

  1                      MyVendor             000001

  1                      MyVendor             000001

  1                      MyVendor             000001

  1                      MyVendor             000001

  1                      MyVendor             000001

  2                      MyVendor2           000002                     

  2                      MyVendor2           000002

  2                      MyVendor2           000002

  Etc.

  Is there a simple TSQL statement I can use for the update or is a cursor necessary?

   

• Terry-239890

  Ten Centuries

  Points: 1387

  More actions

  February 21, 2007 at 6:29 pm

  #691053

  Is this something you wanted? Then you need an identity.

  SET NOCOUNT ON

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

  drop table [dbo].[TestID]

  Create Table TestID (

   [ID] int IDENTITY(1,1),

   [Page_Seq] INT NULL,

   VendorName varchar(50) NULL,

   VendorID varchar(6) NULL,

   [Description] Varchar(50) NULL

  )

  -- populate data

  DECLARE @Finished INT

  DECLARE @VendorID INT

   SET @Finished=0

   WHILE @Finished < 1000

   Begin

    SET @Finished = @Finished + 1

    SET @VendorID = @Finished/100 + 1

    Insert Into TestID

    SELECT NULL, 'Company ' + Cast(@VendorID As Varchar(10)), Cast(@VendorID As Varchar(10)), cast(@Finished as varchar(10)) + ' ...so and so'

   End

  -- Update page_seq

  Update TestID

  Set Page_Seq = ([ID]-1)/13 + 1

  -- Finally get the result

  Select * From TestID