A RayFlow project has a number of data fields that are of either the dropdown or multi-dropdown type, and will be used to reflect information that resides within a 3rd Party database.
Instead of manually adding each value to these data fields, the following process describes how to accomplish this via SQL queries that could be run from within a stored procedure or via a SQL Server Agent job associated with the RayFlow database:
--Locate ParamterListID for the ddl or ddm data field
SELECT * FROM ParameterList
--If 3rd Party database resides on same SQL instance as the RayFlow database, utilise this query
INSERT INTO ParameterValues (ParameterValueID,ParameterListID,ParameterValue,IsActive)
SELECT NEWID(),'ParameterListID_value',third_party_column_name,1 FROM third_party_database.schema_name.[object_name] --add a where clause if required
AND third_party_column_name NOT IN (SELECT DISTINCT ParameterValue FROM ParameterValues)
--If 3rd Party database resides on a different SQL instance than that of the RayFlow database,
--create a linked server from the RayFlow SQL instance to that SQL instance, and then utilise this query
INSERT INTO ParameterValues (ParameterValueID,ParameterListID,ParameterValue,IsActive)
SELECT * FROM OPENQUERY ([linked_server_name], 'SELECT NEWID(),''ParameterListID_value'',third_party_column_name,1 FROM third_party_database.schema_name.[object_name] --add a where clause if required') alias
WHERE alias.third_party_column_name NOT IN (SELECT DISTINCT ParameterValue FROM ParameterValues)
Comments