Copy data - Filter option

rel

Hi,

I'm trying to copy data from one database to another and I'm using the filter option but I'm unable to make it work. What I need it to do is to copy rows that don't exist in the destination

For example
Please note the databases are on different servers

This is the data I need to copy from source but only a selection of it
Source:DB1
Table: tPlugins 
Cols:
id, name, modId
1, Plugin A, 4C2F4360
2, Plugin B, 8CA8280F
3, Plugin C, 078DBFC9
4, Plugin D, 9D7C8954

This is the data I already have in the destination
Destination:DB2
Table: tPlugins
Cols:
id, name, modId
1, Plugin A, 4C2F4360
2, Plugin B, 8CA8280F

The table I want to copy data from is called tPlugins (above) but I'm don't know which rows need to be copied directly, I have to use another table (tContent) which will tell me which rows need to be copied

Both Source and destination have the same data
Table: tContent
contentId, modId, sitename
1, 4C2F4360, 'abc'
2, 8CA8280F, 'abc'
3, 078DBFC9, 'xyz'
4, 9D7C8954, 'abc'

I apply this in the left hand filter for the tPlugins table (see image below)
modid IN (SELECT modId FROM tContent WHERE sitename = 'abc' and type='plugin')

Using the option highlighted in the image "Use the same WHERE clause for both data sources"

• If ticked, it would attempt to copy records tPlugins.id.1,2,4 but fails because tPlugins.id 1,2 already exist in the destination
• If unticked, It would copy records tPlugins.id.4 but would delete 1,2 from the destination

What I would like to do is something like this 

modid IN (SELECT modId FROM tContent WHERE sitename = 'abc' and type='plugin') AND modId  NOT IN (SELECT modId FROM [destinationDatabase].[dbo].[tPlugins])

I've tried to look for information on how to use the filter but I've not found anything that would help me with this.




Thank you in advance.

Rel

Jon_Kirkwood

Hi @rel

 Thank you for reaching out on the Redgate forums regarding your SQL Data Compare inquiry.

 Are you able to share your data compare main screen when you have the where statement in place.

I've replicated your example in the message and I believe it is working as you expect. I only have the sitename field referenced as your example didn't have the type field.

modId in (SELECT modId from tContent where sitename = 'abc')

Presuming tContent is identical in both db's - I can have the WHERE statement either on the source database or on both databases and in both cases I am presented with 1 record to sync (id = 4)

 

 

 

 

Can you share what your compare screen looks like when selecting the tPlugins table as it doesn't sound right that the identical records would be dropped. They should be ignored.

Would you also be able to provide a copy of your deployment script to see if there are any drop statements in it.

rel

Hi Jon,

Thank you for taking the time out to reply and replicate.


tContent is identical in both db's 


I followed the same screenshot as yours unfortunately the outcome is different

Here is the data from source, target and the record identified from tcontent that needs to be copied to target.




In the filter i've added the following in tplugins and it's identified the correct one but also the other two which already exist in target. Sorry I've obscured some of the data but I have double/triple checked the ids and they are identical

modId in (SELECT modId from tContent where site = 'du')

As you can see here, 3 records have been identified to be inserted


And this is the scrip it creates, and when running this I get the following error

Violation of PRIMARY KEY constraint 'PK_tplugins_id'. Cannot insert duplicate key in object 'dbo,tplugins'. The duplicate value is (24). Which is expected but this should be getting filtered out.

rel

After all that I just realised tContent is NOT identical in both db's.

I'm very sorry about that, I totally messed up that piece of information.

I confused myself, it's NOT the same because at the same time the tplugins data is being copied so is the tcontent data.

I need to figure out how to sort that out now that you've highlighted the issue

Thank you so much for your time.

rel

Is there a way to query the destination database in the filter?

For example:
modid IN (SELECT modId FROM tContent WHERE sitename = 'abc' and type='plugin') AND modId  NOT IN (SELECT modId FROM [destinationDatabase].[dbo].[tPlugins])

Note, I can't create a linked server from the source database to the destination database

Jon_Kirkwood

So the tcontent and tplugins data exists in source and they are both syncing over to the target?
Does the behavior change when running the WHERE clause only on the target db vs running on both?