CODEWITHSUNDEEP
sqlsql-serverazure-data-factory
Martin Bailey
Martin Bailey

Reputation: 9

Processing SQL Updates

I have a table with Table name, Id, Column Name and Value I need to run these into a Azure SQL Database as updates.

The tables are all different with different columns for the update.

The only way I could think of is RBAR, but there has to be an easier way to process this, either SQL or maybe even data factory?

Example of the data is:

Table Name    Id    Column Name    Value
Table 1       1234  column1        1
Table 1       1234  column2        2022-01-02
Table 2       4321  column6        2144
Table 2       4321  column12       2022-01-02

The column name could be any column in the table with a update value.

As mentioned I have tried Row By Agonising Row but, as you would expect, WAY too painful as I have approx 161K rows that need processing.

Upvotes: 0

Views: 51

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

You'll need to build an SQL string for each row and run that string as it's own command.

One thing I would do as part of this is pull the table/column information from Information_Schema.Columns. This will give you the same text you already have, but it ensures (in a safe way) that the data in your source table really is a valid object in your database, and not an SQL injection attempt. I would also use sp_executesql for the final query, to include the new value in a safe way.

So the main query would like looks this:

SELECT d.ID, d.Value,
    'UPDATE ' + QUOTENAME(c.Table_Name) + ' SET ' + QUOTENAME(c.Column_Name) + ' = @new_value WHERE ID= @RowID' as SQL 
FROM [MyData] d
INNER JOIN Information_Schema.Columns c ON c.[Table Name] = d.Table_Name
   AND c.[Column Name] = d.Column_Name

And the loop would then take each row and do something like this:

-- @SQL, @ID, and @Value from the row

Execute sp_executeSql @SQL, '@new_value varchar(50), @id int', @new_value = @Value, @RowID = @ID

I skipped over the part where you loop through the rows because you have many options here, and which is best depends on your specific situation: do you want a cursor? Client code? Hard to know.

The trick here is we don't know the data type of the new value... and it certainly looks like you're mixing data types in there. You know that, for example, storing dates in varchar columns is really bad, right?

Upvotes: 1

Related Questions