Updating multiple tables sql server


15-Oct-2017 18:21

Can you do this type of statement when creating a view? Card Gunner Originally posted by jimf Found this trivia on the net a while ago DECLARE @Table A TABLE (i INT)DECLARE @Table B TABLE (i INT)SELECT * FROM @Table ASELECT * FROM @Table BINSERT @Table AOUTPUT inserted.i INTO @Table BSELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3SELECT * FROM @Table ASELECT * FROM @Table BOriginally posted by cardgunner I was afraid of that.

In any case I did cahnge @unit with '71054' and scratched the declare. Worked perfectly Tried to update the t_cmnf and got an error. UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed ideas?

id Data, -- PK of main table tab Data fi SL, fi Model, fi Claim Status -- FK to dim Claim Status FROM tab Data AS d INNER JOIN loc SL AS sl ON SL = SL INNER JOIN loc GSP AS gsp ON GSP = GSP INNER JOIN loc Country AS c ON Country = Country INNER JOIN loc Market Unit AS mu ON Market Unit = Market Unit INNER JOIN mod Model AS m ON Model = Model INNER JOIN dim Claim Status AS s ON Claim Status = Claim Status INNER JOIN tdef Product Type ON Product Type = tdef Product Product Type LEFT OUTER JOIN tdef Service Level ON d.fimax Service Level = tdef Service Service Level LEFT OUTER JOIN tdef Action Code AS ac ON d.fimax Action Code = Action Code UPDATE tab Data SET fi Claim Status = (SELECT id Claim Status FROM dim Claim Status WHERE Claim Status Name = 'Awaiting auth.') WHERE fi Claim Status=(SELECT id Claim Status FROM dim Claim Status WHERE Claim Status Name = 'Approved') updatable in practice, due to limitations of the query processor's reasoning.

It is possible, however, to manually create an updategram that will update multiple tables.

I tried to update the tables in that view and I get the error. All the records in the view was of unit 71054 I tried the update without the where clause.update tmp_mfg_unit_2set t_cmnf='OTH'and still the same error UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.

Card Gunner create view tmp_mfg_unit_2 as select t_cmnf, t_clot from ttscfg200120 --objects where t_clot='23332' union all select t_cmnf, t_clot from terext401120 --rental contract lines where t_clot='23332' update tmp_mfg_unit_2 set t_cmnf='OTH' Server: Msg 4416, Level 16, State 5, Line 1 UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.

you're not breaking the rules for updateable views, then you should be okay. So not using a view for updates seems to be the safest approach.

Typically, it does a good job with this, so the update view plan may look very similar to a plan for a simple update to the single affected base table. Then read this blog I wrote recently to see how it could be affected. But actually it didn't return records because the referenced table has changed by the update.Microsoft is conducting an online survey to understand your opinion of the Msdn Web site.



Facts About Africa: Black is beautiful and this is reflected in the streets of Accra, Lagos, Johannesburg and many more.… continue reading »


Read more

Reasons to join Scottish - FREE to Search - FREE to Join- Scotland's #1 Dating Site - Thousands of Members- UK based Customer Care Line - All Profiles Checked for Authenticity - Make Your Own Video Profile Welcome to Scottish Dating.com, the best place to virtually meet someone from the Scottish borders! All you need to do is register and get ready to be swept off your feet!… continue reading »


Read more

Hot xxx webcams in america live show Black american live sex chat. Live sex cam american express American big titts hidden cam sex. Watch Live Cam Girls and Meet Free Online Girls on Cam.… continue reading »


Read more

Each block is formed in latex molds taken from natural rock.… continue reading »


Read more