Creating a Collection Query From SQL : Collections Migration from One server to other

Creating a Collection Query From SQL : Source : http://myitforum.com/cs2/blogs/cnackers/archive/2009/04/22/creating-a-collection-query-from-sql.aspx

Credit to Dave Fuller for which this information is based upon.

Creating a collection query that is based upon SQL can be done.  It’s a little complicated, but not all that bad once you’ve done it a few times.  There are many times where you have a fantastic SQL query you would love to use, but cannot figure how to get that same query in WQL.  When you create a collection query in the administrative console, it’s created in WQL and translated to SQL.  It is possible to create a SQL query and simple update a collection with that new SQL statement to process the collection membership.

Things to understand:

What you see as the “CollectionID” in the admin console is actually the “SiteID” in the SQL database. 

The CollectionID in the SQL Database, is actually the number of the collection as it was created, so if the last collection you created was your 965th collection, it’s CollectionID in SQL would actually be 965, even though the CollectionID you see in the admin console is “ABC00012”

If you have it setup so that you can see the node information, you will see the “Store Name” ID for that collection that you can use as well.

This process involves a few steps:

1) Create a target collection with a blank query, aka don’t define anything in the query statement

2) Get the collection SiteID (as it’s referred to in SQL) otherwise known as the Collection ID in reports, or the Store Name in the node information tab in the admin console

3) Run the following query in SQL query analyzer to find the CollectionID as it’s listed in SQL

Select CollectionID, SiteID from Collections where SiteID= ‘CollectionID/StoreName as seen in the console’

This will return the CollectionID you need to update the SQL statement with

4) Run the following update command to insert your SQL statement in the SQL statement query portion of the Collection.

Update Collection_Rules_SQL
set SQL = ‘YOUR SQL STATEMENT’
where CollectionID = ‘X’

5) You can run the following query to identify that your SQL statement is in fact in the correct place now

select SQL, WQL
from Collection_Rules_SQL
where CollectionID = ‘X’

This will also show you the WQL statement for any collection you want to see.

6) Update your collection to see if you have gotten the intended results

7) I would highly recommend you make sure you SQL statement works before inserting it into the collection SQL statement

IMPORTANT NOTE: If you modify the query in the admin console, you will overwrite the SQL statement you created.  You will have to re-update the SQL statement through query analyzer if you accidentally modify the query through the console