I have a process that runs regular updates to tables. I had to create another table which is a subset of one of the tables and need to update that table when the first table is finished updating. How would I write the new update stored procedure? The first table does have a last updated column. UPDATE [Databse].[dbo].[Table] SET [Column1] = ,[Column2] = ,[Column3] = ,[SortFlag] = WHERE
@technette from what you just wrote in the comments to @SirSql's answer, you have made a mistake in the T-SQL update command. You have `Table2` which is your main table and `StandTable` which contains the subset of data. You have a bulk update of `Table2` each day from your AS/400 system. You then want the matching data in `StandTable` to be updated accordingly. You need to take your code and change the inequality to an equality: UPDATE StandTable SET Col1 = Table2.Col1, Col2 = Table2.Col2, Col3 = Table2.Col3 FROM Table2 INNER JOIN StandTable ON Table2.Col2 = StanTable.Col2 -- << here I changed the join to be an equality This will find all matches in the tables and update the table `StandTable` and all should be fine.
Is your first process a stored procedure? If so it would make most sense to handle both updates within a single transaction so that either both succeed or fail. BEGIN TRY BEGIN TRAN UPDATE TableA SET Col1 = x WHERE Val = y UPDATE TableB SET Col1 = x where Val = y COMMIT TRAN END TRY BEGIN CATCH SELECT ERROR_MESSAGE() ROLLBACK TRAN If you are performing large set based operations then maybe an update based upon a join with the last updated information. Finally (although I hate to say this) you could use a trigger which performs an update of the second table. Ensure that you have solid FK relationships either way.