June 11, 2009 at 12:37 am
Hi I have a Table in 2 different databases.Table is same in schema in both databases.At any moment of time these table should have similar data.Now I come to know that table does not have similar data in both databases so how will I make them in sync using sql query ie how will I make both table having similar data using sql query.I just want to copy that data which is not present in table ,not all data from a table
eg I have Table "A" in databases DA and DB with column "Name".Now A in DA having "john" and "Mike" as data and table A in DB having "Maria" and "Mike" .Now I want to make both table having "john","Mike","Maria" using sql query but I want that only "John" should transfer from A in DA to A in DB and only "Maria" should transfer from table A in DB to table A in DA.
Please suggest me query.I will put this query in a job so at any point of time table will be in sync
June 11, 2009 at 12:59 am
Use import and export wizard........
In edit mapping Choose to append the data to the destination.
The new rows will be appended to the existing table.
Tanx 😀
June 11, 2009 at 1:05 am
Hi,
I can not use Import and Export Wizard.I have to do it through job.Job will run daily basis and if data is not same then It will make them same.
so please tell me solution which can be used using sql queries so that those queries can be used in a stored proc and that stored proc can be called through job.
June 11, 2009 at 1:25 am
Hi,
try this
create table #temp1
(
name1 varchar(10)
)
create table #temp2
(
name1 varchar(10)
)
insert into #temp1
select 'ABCD'
union all
select 'BCDA'
insert into #temp2
select 'CDAB'
union all
select 'DABC'
insert into #temp1
select name1 from (
select name1 from #temp1
union
select name1 from #temp2) as X
where name1 not in(select name1 from #temp1)
insert into #temp2
select name1 from (
select name1 from #temp1
union
select name1 from #temp2) as X
where name1 not in(select name1 from #temp2)
ARUN SAS
June 11, 2009 at 2:03 am
jain_abhishek (6/11/2009)
Hi I have a Table in 2 different databases.Table is same in schema in both databases.At any moment of time these table should have similar data.Now I come to know that table does not have similar data in both databases so how will I make them in sync using sql query ie how will I make both table having similar data using sql query.I just want to copy that data which is not present in table ,not all data from a tableeg I have Table "A" in databases DA and DB with column "Name".Now A in DA having "john" and "Mike" as data and table A in DB having "Maria" and "Mike" .Now I want to make both table having "john","Mike","Maria" using sql query but I want that only "John" should transfer from A in DA to A in DB and only "Maria" should transfer from table A in DB to table A in DA.
Please suggest me query.I will put this query in a job so at any point of time table will be in sync
try this out
-- get and insert into DA all data not in DA but is in DB
insert into DA.dbo.A (Name)
select Name from DB.dbo.A
EXCEPT
select Name from DA.dbo.A
-- get and insert into DB all data not in DA but is in DA
insert into DB.dbo.A (Name)
select Name from DA.dbo.A
EXCEPT
select Name from DB.dbo.A
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply