Adding a default unqiue value into a table

• David Loosley

  SSC Enthusiast

  Points: 121

  More actions

  November 4, 2004 at 11:59 am

  #60130

  I'm tring to import data from one table to another.

  INSERT INTO Table2

     SELECT NULL AS Username,

        t1.Foreame,

        t1.Surname,

     FROM Table1 AS t1

  But I want a UDF or Trigger to create the default value for the new field (username). The rule for creating this default value is to be:

  First charcater of the forename + the surname (max 20 chars) and if the username is not unique then append a sequential number to the end. Example:

  jsmith

  jsmith1

  jsmith2

  Can anyone help?

   

  TIA

  David

• November 8, 2004 at 8:00 am

  This was removed by the editor as SPAM

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  November 8, 2004 at 9:48 am

  #529344

  Hi,

  May be You could try like this.

  DECLARE @defaultString varchar(25)

  DECLARE @countValue int

  -- you need to keep this inside a loop.

  -- I do not know how to keep it.

  SELECT @defaultString=left(forename+surname,20) from table1

  SELECT @countValue=Count(*) from table1 where defaultColumnValue=@defaultString

  if @countValue = 0 then

  insert into table1(default_columnname)

  values(@defaultString)

  else

  SET @countValue=@countValue+1

  insert into table1(default_columnname)

  values(@defaultString+@countValue)

  SET @countValue=0

  Thanks,

  Sridhar!!