The "table1" located in another database is updated daily with transactions from the last day.I just want to update "table2" with the new values added into "table1". Rows are not going to be deleted from "table1", It's a historical record of transactions which I want to replay into "table2" because "table2" (in the other database in the same server) will be used for SSAS, for BI. This is what I tried: USE BDID_BI; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE Transaction Update AS insert [BDID_BI].[dbo].[Transaction] * SELECT * FROM [BDID].[dbo].[hl_transaction] as B WHERE ( SELECT * FROM [BDID_BI].[dbo].[Transaction] WHERE date Another comment, this time by Kenneth Fisher: The above will work but you are making an important assumption, that all of your data was loaded for that date.Hi Tom, Due to migration to new system we have to change all our account numbers. June 28, 2005 - pm UTC yes, the predicate in the join for the fictional question without a sample table and data was wrong (everytime I wing it, try to answer WITHOUT TESTING, it comes out wrong. test cases are relevant :) Hi Tom, Thanks for answering my question. ( Client number is consist of branch, Number, Sub Number Currency Code ) We have one big transaction table around 1 million records, having many columns, and at many places client numbers are stored from account master under various columns 1. You told me to use update ( select old.old_account_number, new.new_account_number from old_table old, mapping_table new where old.old_account_number = new.old_account_number ) set old_account_number = new_account_number; But I think my questions still reamin unanswered 1. In your answer only one number say client number will get updated. Microsoft is conducting an online survey to understand your opinion of the Msdn Web site.If you choose to participate, the online survey will be presented to you when you leave the Msdn Web site. etc I have a mapping table where "ALL" old account number and new account numbers are present.

Something like this: INSERT INTO [BDID_BI].[dbo].[Transaction] SELECT B.* FROM [BDID].[dbo].[hl_transaction] as B LEFT JOIN [BDID_BI].[dbo].[Transaction] AS B_BI ON B.primary Key = B_BI.primary Key WHERE B_BI.primary Key IS NULL; , what is the type of the column?I put a primary key on B(id) because if there is NO SUCH constraint -- the update is totally ambigous -- if there is more then 1 value of B1 in B for a given ID -- we cannot tell what row in B should be used to update the row in A. REGISTRATION where BIN NOT IN (select BIN from VIS. Its working very nicely.i will greateful to u forever.i was suffering by this problem for a long i am using the following / insert into VISTEMP. i could envision problems with only a second of granularity on a clock -- you could miss a record or two due to that. d) I don't program forms -- you can try otn.- Thank u very much for ur nice reply. I plan to use RTRIM function in the WHERE clause to get specific set records and then after fetching, I’ll insert with a RTRIM on each column.I have to update "table2" from another "table1" located in another database. I want to write SQL ( not PL/SQL ) to update transaction table from mapping table at one stroke. regards & thanks pjp update ( select old.old_account_number, new.new_account_number from old_table old, mapping_table new where old.old_account_number = new.new_account_number ) set old_account_number = new_account_number; disabling foreign keys during the operation and enabling them afterward. update ( select old.old_account_number, new.new_account_number from old_table old, mapping_table new where old.old_account_number = new.old_account_number ) set old_account_number = new_account_number; (ie in the subquery, match the old account numbers not try matching the old in the old table to the new in the mapping table) Of course if the original posting had the CREATEs/INSERTS etc, it would have a quick and east test. I have to update a transaction table where mapping is existing with old account no and new account number.