CODEWITHSUNDEEP
sqlsql-serversql-update
Kuba
Kuba

Reputation: 21

The best method to update every row in SQL Server

I have code like this to create random table: (http://dba.fyicenter.com/faq/sql_server/Creating_a_Large_Table_with_Random_Data_for_Indexes.html) 

-- Create a table with primary key
CREATE TABLE fyi_random 
(
    id INT,
    rand_integer INT,
    rand_number numeric(18,9),
    rand_datetime DATETIME,
    rand_string VARCHAR(80)
);

-- Insert rows with random values
DECLARE @row INT;
DECLARE @string VARCHAR(80), @length INT, @code INT;
SET @row = 0;

WHILE @row < 100000 
BEGIN
    SET @row = @row + 1;

    -- Build the random string
    SET @length = ROUND(80*RAND(),0);
    SET @string = '';

    WHILE @length > 0 
    BEGIN
        SET @length = @length - 1;
        SET @code = ROUND(32*RAND(),0) - 6;

        IF @code BETWEEN 1 AND 26 
           SET @string = @string + CHAR(ASCII('a')+@code-1);
        ELSE
           SET @string = @string + ' ';
    END 

    -- Ready for the record
    SET NOCOUNT ON;

    INSERT INTO fyi_random 
    VALUES (@row, 
            ROUND(2000000*RAND()-1000000,0),
            ROUND(2000000*RAND()-1000000,9),
            CONVERT(DATETIME, ROUND(60000*RAND() - 30000, 9) ),
            @string)
END

PRINT 'Rows inserted: '+CONVERT(VARCHAR(20),@row);
GO

I want to update every row (for example update column rand_string with some new random string). What is the best method? When I'm trying to do this by while loop performance decreases with increasing rows:

...
SET NOCOUNT ON;

UPDATE random_data 
SET rand_string = @string 
WHERE id = @row;

I also tried to use cursor statement and it's better but why while loop is so slow? And is there another way to do this better?

Upvotes: 0

Views: 467

Answers (1)

Andy G
Andy G

Reputation: 19367

If you want to update every row in a table then just omit the where clause in the update statement.

Upvotes: 1

Related Questions