Dynamic SQL to update table with random values from the same table

Question

I want to create a dynamic SQL in a stored procedure that takes a table name as a parameter and for each column in that table the values will be updated with a random value from the same column.
(I need this to create tables for testing purposes so that I don't use real data).
For example let's say I have the table 'students' below:

|ID| FName|  BirthDay |
-----------------------
|1 | Mike | 1993-07-24|
-----------------------
|2 | John | 1995-10-11|
-----------------------
|3 | Bob  | 1992-04-13|
-----------------------

After the update the values will be updated randomly using the same values from the table:

|ID| FName|  BirthDay |
-----------------------
|2 | Bob  | 1995-10-11|
-----------------------
|3 | John | 1995-10-11|
-----------------------
|3 | Mike | 1992-04-13|
-----------------------

For the random value I tried something like this:

 UPDATE students
   SET ID = SELECT TOP 1 ID FROM students ORDER BY NEWID()
   SET FName = SELECT TOP 1 FName FROM students ORDER BY NEWID()
   SET Birthday = SELECT TOP 1 Birthday FROM students ORDER BY NEWID()

Do you have any idea how to implement this as a stored procedure(SQL Server) so i can use it with any table given as a parameter?

Later edit: I have done this so far (it takes the table name as parameter and updates every column it finds in that table but the problem is that after update all the values in a column are the same).
Here is the code:

CREATE PROCEDURE [dbo].[RandomUpdate]
   @TableName NVARCHAR(100)
    AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX) = (
    SELECT ' UPDATE ' + @TableName--note the extra space at the beginning
        + ' SET ' + QUOTENAME(c.name) + ' = ' + '(SELECT TOP 1 ' + QUOTENAME(c.name) + ' FROM ' + @TableName + ' ORDER BY NEWID());' 
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID(@TableName)
    FOR XML PATH('')
)
    PRINT @sql;
    EXEC sp_executesql @sql


END

and the output is like this:

|ID| FName|  BirthDay |
-----------------------
|3 | Bob  | 1995-10-11|
-----------------------
|3 | Bob  | 1995-10-11|
-----------------------
|3 | Bob  | 1995-10-11|
-----------------------

Any ideas how to set different values?

You need to use dinamic query, for that check this: https://stackoverflow.com/questions/2838490/table-name-as-variable — User1899289003, Jan 13 '20 at 20:47
When I work with dynamic sql, first thing I do is get a query working (with hard coded values) - then rewrite for dynamic. That being said, what is your query that prints out (what is the value you get from print @sql) and fix that to work correctly. — LaraRaraBoBara, Jan 13 '20 at 23:58

score -1 · Answer 1 · answered Jan 13 '20 at 20:29

-1

Just add the following to to top of your SQL Statement CREATE PROCEDURE [nameofprocedure] AS

answered Jan 13 '20 at 20:29

Sola Oshinowo

519
4
13

Ok, i know the syntax to create a stored procedure, but i need my dynamic query inside the procedure to take a table name as parameter and dynamically go through each column from that table and apply the update query – mecnism Jan 13 '20 at 20:34

Dynamic SQL to update table with random values from the same table

1 Answers1