Data sync - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Data sync

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-24 : 02:13:34

I have two database one is itsupport247db and another is itspatchdb
itsupport247db is main database and we sync the data in itspatchdb

on live database its fine but on local database some discripency is there
so we are not sync with these two database

i have three tables mstmember mstsite and regmain

in all these three tables one column have primary key constraint on memberid, siteid, regid respectively
i need to insert or update the data from itsupport247db to itspatchdb

if membercode and membername is same then i want to update memberid with memberid
but there are already some of memberid are there so primary voilation is occuring

please help me out how to do that

Vaibhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-25 : 03:35:35

update target
set memberid = source.memberid
from itsupport247db as source inner join itspatchdb as target
on source.membercode=source.membercode and source.membername =target.membername

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-25 : 03:55:20

if membercode and membername is same then i want to update memberid with memberid
but there are already some of memberid are there so primary voilation is occuring

that query i tried
please see my post again...

Vaibhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-25 : 04:57:03

update target
set memberid =
case when target.memberid =source.memberid then target.memberid else source.memberid end
from itsupport247db as source inner join itspatchdb as target
on source.membercode=source.membercode and source.membername =target.membername

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-25 : 05:15:18

Sorry madhi you are not getting what i want.
for example :
itssupport247db.mstMember data
1 aaa xxx
2 bbb yyy
3 ccc zzz

and itspatch.mstMember data
4 aaa xxx
2 ddd qqq
1 eee sss

now if i using any of your query when i will update itspatch.mstmember
then it should update memberid 4 with 1 as membername and membercode are same
but 1 is already in mstmember so i am getting primary key violation error.

Vaibhav T

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-25 : 05:18:33

<<so i am getting primary key violation error>>

IF YOU REALLY WANT TO UPDATE THE PK COLUMN.I CAN THINK OF ONE WAY IS
"Turn off this identity column when updating"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-25 : 05:22:03

quote:
Originally posted by vaibhavktiwari83

Sorry madhi you are not getting what i want.
for example :
itssupport247db.mstMember data
1 aaa xxx
2 bbb yyy
3 ccc zzz

and itspatch.mstMember data
4 aaa xxx
2 ddd qqq
1 eee sss

now if i using any of your query when i will update itspatch.mstmember
then it should update memberid 4 with 1 as membername and membercode are same
but 1 is already in mstmember so i am getting primary key violation error.

Vaibhav T

Ok. What do you want to do in that case?
Do you want to skip the updation?

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-25 : 05:28:17

Ummmm
well i want to update but before that i want to update memberid to set 0 or any other value
which are already there so that later i can delele those memberids

Vaibhav T

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-25 : 05:30:02

quote:
Originally posted by haroon2k9

<<so i am getting primary key violation error>>

IF YOU REALLY WANT TO UPDATE THE PK COLUMN.I CAN THINK OF ONE WAY IS
"Turn off this identity column when updating"

well i m not getting your mean by red part.
you want me to identity insert off or what ?
becasue this is the issue of primary key not the identity column.

Vaibhav T

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-25 : 05:38:47

<<Turn off identity on your column>>
goto property on pk column and make it as is identity -> Off

2.
Do something like this.modified of madhi code.
update target
set memberid =
case when target.memberid =source.memberid then 0 else source.memberid end
from itsupport247db as source inner join itspatchdb as target
on source.membercode=source.membercode and source.membername =target.membername

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-25 : 05:42:01

quote:
Originally posted by vaibhavktiwari83

Ummmm
well i want to update but before that i want to update memberid to set 0 or any other value
which are already there so that later i can delele those memberids

Vaibhav T

This will update them to zero
(Make sure you have backup of tables before trying this)

update target
set memberid = 0
from itssupport247 as source inner join itspatchdb as target
on source.membercode=source.membercode and source.membername =target.membername
and exists(select * from itspatchdb where membercode=source.membercode and membername=source.membername)

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-25 : 05:44:44

<<primary key constraint on memberid,>>

hope,will get PK viloaton error,When updates to 0 Madhi.please correct me if iam wrong.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-25 : 05:58:34

Okey thanks i got it.

Vaibhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-25 : 06:00:08

quote:
Originally posted by haroon2k9

<<primary key constraint on memberid,>>

hope,will get PK viloaton error,When updates to 0 Madhi.please correct me if iam wrong.

You are correct. Again I forgot that

We need more informations on what to do in this case

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-25 : 06:04:05

quote:
Originally posted by madhivanan

quote:
Originally posted by haroon2k9

<<primary key constraint on memberid,>>

hope,will get PK viloaton error,When updates to 0 Madhi.please correct me if iam wrong.

You are correct. Again I forgot that

We need more informations on what to do in this case

Madhivanan

Failing to plan is Planning to fail

yeah.True

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-25 : 06:08:12

quote:
Originally posted by haroon2k9

quote:
Originally posted by madhivanan

quote:
Originally posted by haroon2k9

<<primary key constraint on memberid,>>

hope,will get PK viloaton error,When updates to 0 Madhi.please correct me if iam wrong.

You are correct. Again I forgot that

We need more informations on what to do in this case

Madhivanan

Failing to plan is Planning to fail

yeah.True

Yeah i also forget

problem is not solved yet
what information you want.

Vaibhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-25 : 06:18:59

Why do you want to update a primary key column?

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-25 : 06:30:25

quote:
Originally posted by madhivanan

Why do you want to update a primary key column?

Good question. The requirement to change a primary key is a good indication that your table structure is inadequate.

There are 10 types of people in the world, those that understand binary, and those that don't.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-26 : 03:02:09

Response for this?

quote:

Why do you want to update a primary key column?

Madhivanan

Any progress on this vaibhavktiwari83?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources