update stored procedure

Question

question

technette asked Jun 24, '11

update stored procedure

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

update

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2011-06-28T15:59:43Z

WilliamD answered Jun 28, '11

@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.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

technette commented · Jun 28, 2011 at 04:03 PM

Ohh! I was trying to do two things at once... Find where the code in both tables don't match and populate the subtable with any new values. How will I populate the table with new values?

0 ·

technette commented · Jun 28, 2011 at 06:18 PM

William Thank you! I also added the following after the update to include new records: INSERT INTO StandardTable SELECT col1, col2, Col3, Col4 From Table2 where Table2.Col1='StdName' AND not exists (select * from Table2 where Table2.Col2 = Col2 )

0 ·

Answer 2 · 2011-06-24T14:27:06Z

SirSQL answered Jun 24, '11

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.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

technette commented · Jun 24, 2011 at 02:44 PM

SirSQL Thank you for responding. The first table is update using an old process. feeds come in weekly from an AS400 database into one table and then the other tables are updated. I was asked to create a stored procedure that could be process through a job.

0 ·

SirSQL commented · Jun 24, 2011 at 03:35 PM

What process performs the updates on the tables after the staging table has been loaded from the AS400, or is that what you are trying to accomplish?

0 ·

technette commented · Jun 28, 2011 at 03:50 PM

I would like to just Update the new table when the other process is complete. I don't know all that is involved in the major update process. I just want a job to run executing my update procedure on a weekly basis. so I created the following in an update stored procedure but when I execute. I get an entired database table of the second record. It updates the first only then duplicates all the rows with the same record. UPDATE StandTable SET Col1 = Table2.Col1, Col2 = Table2.Col2, Col3 = Table2.Col3 FROM Table2 INNER JOIN StandTable ON Table2.Col2 StanTable.Col2

0 ·

question