Advice - compare with where clause

chilberto

I am comparing two environments (Env1, Env2) that have had entities entered in manually so I am looking for differences across a collection of tables (Table1, Table2, Table3, ...). I am currently filtering the table comparison to a single row or collection of rows that all belong to the same entity (EntityA) by a uniqueidentifier. The uniqueidentifier is different in both environments.

First question, is it possible to define a parameter or variable so that my where clauses look something like "rowid = @Env1EntityId" for Env1 table and "rowid = @Env2EnitityId" for Env2 table. Otherwise I have to update a fiftty or so where clauses for each entity I want to compare without making a mistake. Any tips on this?

Once I have my project working for one entity, I could create a copy and then open in a text editor and search and replace the variables. That is my workaround at the moment.

Tianjiao_Li

Hi @chilberto

I'm afraid it's not possible to define a parameter or variable in a where clause. Please kindly leave it on the user voice forum. 

What do you mean by 'entity'? If you can elaborate a bit more what you are trying to achieve, we'll try to see if there is any workaround we can suggest. 

chilberto

Entity refers to the logical object being stored. For example, a Car might be composed of a record from the Cars, Wheels, CarWheels, Chasis, etc tables.

So in the Cars table there might be a uniqueidentifier for the id and a name for a Car enitty and in the Wheels table is an id and a name for a Wheel entity. In the CarWheels would be the linkage between the Car entity and the Wheels.

So at the moment I am building a SQL Data Compare project that has a where clauses that limit the project to a specific Car with name "MyCarInUAT" in the first environment and "MyCar" in the second environment across all tables related to a Car entity.

Tianjiao_Li

Hi @chilberto

Thanks for your explanation.

It looks like the workaround that you are currently doing is probably the best one! The positive side is once this is all set up in the Project file, you won't need to update it too often.