CODEWITHSUNDEEP
c#sqlsql-server
Halcyon
Halcyon

Reputation: 14971

Is It Possible To Generate a SQL Update Script For One Row Of Data?

I have a single row of data in a SQL Server table that I'm going to update for testing purposes. Before I update it, I'd like to save the existing values into a .SQL file so that I can undo my changes once I verify my code is working properly (rollbacks are not a viable option in this scenario and I want to skip the primary key since it's seeded).

Here is my table structure:

CREATE TABLE [User]
    [ID]        [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Name]      [varchar](255) NOT NULL,
    [Address]   [varchar](255) NOT NULL,
    [City]      [varchar](100) NOT NULL,
    [State]     [varchar](100) NOT NULL,
    [ZipCode]   [varchar](100) NOT NULL,
PRIMARY KEY CLUSTERED WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Given that structure, my insert looks like this:

UPDATE [User] SET
    [Name] = 'John Doe',
    [Address] = '132 Test Road',
    [City] = 'Anytown',
    [State] = 'TS',
    [ZipCode] = '12345'

As you can see, I can run my C# code and overwrite the John Doe data and then restore it's original values once I'm done verifying that my code works. I had to manually type in the previous update statement. This is very tedious for tables with a lot of data and to do it for each row I want to save. Does anyone know if it's possible to automate this process so that a row's data can be converted into an update statement?

Upvotes: 1

Views: 725

Answers (3)

Tevo D
Tevo D

Reputation: 3381

You could create a second user table for auditing purposes, perhaps UserAudit, similar to the User table. The audit table could also contain additional columns for the date/time edited, user, etc. as needed.

You could then create update and delete triggers write to a copy of the original data plus the additional audit fields (user, operation, date/time) to the audit table before performing the change.

This would work automatically in the background, not requiring separate selects to be done or manual intervention.

Upvotes: 0

to StackOverflow
to StackOverflow

Reputation: 124706

You could write a SELECT query to generate the UPDATE statement you want:

SELECT
   'UPDATE [User] SET [Name] = ' +
   '''' + REPLACE(Name, '''', '''''') + '''' + 
   ', [Address] = ' + 
   '''' + REPLACE(Address, '''', '''''') + '''' + 
   ... etc with remaining columns
FROM [User]
WHERE [User].ID = ...

Note the use of REPLACE to escape any single quotes in your VARCHAR columns.

Upvotes: 0

Curtis
Curtis

Reputation: 103348

I don't think the functionality for updating a single row like this exists. Technically your query is updating all rows in the database, and I don't see why SQL Server Management Studio would implement a tool like this.

However, you can go to Database > Tasks > Generate Scripts and create a query to insert the record again.

Upvotes: 1

Related Questions