How to make two Tables in sync

Question

Post reply

How to make two Tables in sync

jain_abhishek

Mr or Mrs. 500

Points: 535
More actions
June 11, 2009 at 12:37 am

#103759

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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eswin SSCrazy Eights Points: 8320 More actions · Answer 1

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 😀

jain_abhishek Mr or Mrs. 500 Points: 535 More actions · Answer 2

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.

arun.sas SSChampion Points: 11831 More actions · Answer 3

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

WayneS SSC Guru Points: 95461 More actions · Answer 4

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 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

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

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2