I have to merge(insert)data from source database into target database(over 600 tables).The good thing is the both source and target tables have exactly same structure and constrains but I have to transfer only data that are not existing in target database/table.Right now I am using this code but as I told you there are over 600 tables and took lots of time to do it manually.
insert into Emp (ID,SIN, Name, LName, Age , YearHierd) select ID,SIN, Name, LName, Age , YearHierd from Emp@SrcDatabase s where not exists (select 1 from Emp d where s.ID = d.ID and s.SIN = d.SIN); commit;
So, I thought I may make procedure to automate the transferring process but I am still challenging with it.
The very first thing is how I can list the columns name of a table from another database?
I use :
select column_name from cols where Table_name='Emp'
for the current database(target) but I need to get a list of coulmn name for same table in another database.I tried
select column_name from cols where Table_name='Emp'@SrcDatabase
which is not a correct syntax so can you please let me know how I can solve this?