using sql to create a 1700 update stmts

• Geri

  SSC Rookie

  Points: 39

  More actions

  September 21, 2006 at 7:15 pm

  #192377

  Help - i am a newbie and need to update a table and change passwords for over 1700 clients.  I want to select the user_id from our master table and create the update statement from the select.  In Oracle I would parse the statement similiar to this:

  SELECT 'ALTER USER '||' IDENTIFIED BY VALUES '||CHR(39)||PASSWORD||CHR(39)

  FROM DBA_USERS WHERE USERNAME = 'USER';

  this then creates the alter statement.  I want to do something like this in SQL Server.  HELP?!?!?

  geri

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  September 22, 2006 at 10:43 am

  #662021

  Try this:

  DECLARE @DBA_USERS TABLE( USERNAME varchar(10), 

                                                 [PASSWORD] varchar(10))

  INSERT INTO @DBA_USERS

  SELECT 'FIRST', '123' UNION ALL

  SELECT 'SECOND', '345' UNION ALL

  SELECT 'THIRD', '678' UNION ALL

  SELECT 'FOURTH', '910'

  SELECT 'UPDATE TableName SET PASSWORD =  ' + CHAR(39) + PASSWORD + CHAR(39) + ' WHERE USERNAME = ' + CHAR(39) + USERNAME + CHAR(39)

  FROM @DBA_USERS --WHERE USERNAME = 'USER'

  I wasn't born stupid - I had to study.