Updating two tables

19-Mar-2017 03:53

To illustrate, consider the following example source and target tables and data. Target(Employee ID int, Employee Name varchar(10), CONSTRAINT Target_PK PRIMARY KEY(Employee ID)); CREATE TABLE dbo.

Source(Employee ID int, Employee Name varchar(10), CONSTRAINT Source_PK PRIMARY KEY(Employee ID)); GO INSERT dbo.

Target(Employee ID, Employee Name) VALUES(100, 'Mary'); INSERT dbo.

Target(Employee ID, Employee Name) VALUES(101, 'Sara'); INSERT dbo.

If you cache the data in a temporary (or in-memory) table on -- Fetch data from the other database server SELECT * INTO #my Temp Table FROM OPENQUERY([DB2], 'SELECT * FROM [My Database On DB2].[dbo].[My Other Table]') -- Now I can join my temp table to see the data SELECT * FROM [My Database On DB1].[dbo].[My Table] tab1 INNER JOIN #my Temp Table tab2 ON tab1. ID Check out the documentation for OPENQUERY to see some more examples. I would definitely use the first method in this specific example, but the second option using A join of two tables is best done by a DBMS, so it should be done that way.

I've been trying to get this answer for a long time but I am not clear on this even after makin a lot of searches. Hello Nikhil, Here are the resolutions for your queries: ?

How can we update the individual Base tables by updating a View. Acoording to my knowledge individual views are formed for selection of reuired columns from different tables. Here is the syntax for UPDATE: UPDATE---- ( table-name/view-name) SET ------- WHERE conditions Hope this will help. This means we cannot UPDATE, INSERT or DELETE a row on the view, which have a join in its sub select.

For example, you can use the MERGE statement to perform the following operations: It is important to understand how the source and target data are merged into a single input stream and how additional search criteria can be used to correctly filter out unneeded rows.

Otherwise,you might specify the additional search criteria in a way that produces incorrect results.You can use a database link to update a remote object only if you are using Oracle Database distributed functionality.If you omit to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery.Where I wrote databasename, you have to define the name of the database.

General view updatability. The key part of the CREATE VIEW Transact-SQL documentation is Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table. Note that even if the view is technically updatable, it may not be actually updatable in practice, due to.… continue reading »

Read more

Create and run an update query. you can verify that you’re updating the records you. you might include ID fields from two tables to help ensure.… continue reading »

Read more