Query a Database Based on Result of Query from Another Database

Query a database based on result of query from another database

There is 3 Methods to achieve this:

1st method - Using Lookup Transformation

First you have to add a Lookup Transformation like @TheEsisia answered but there are more requirements:

• In the Lookup you Have to write the query that contains the ID list (ex: SELECT ID From MyFirstDB WHERE ...)

• At least you have to select one column from the lookup table

• These will not filter rows , but this will add values from the second table

To filter rows WHERE ID IN ({list of IDs from MyFirstDB}) you have to do some work in the look up error output Error case there are 2 ways:

1. set Error handling to Ignore Row so the added columns (from lookup) values will be null , so you have to add a Conditional split that filter rows having values equal NULL.

Assuming that you have chosen col1 as lookup column so you have to use a similar expression

ISNULL([col1]) == False

2. Or you can set Error handling to Redirect Row, so all rows will be sent to the error output row, which may not be used, so data will be filtered

The disadvantage of this method is that all data is loaded and filtered during execution.

Also if working on network filtering is done on local machine (2nd method on server) after all data is loaded is memory.

2nd method - Using Script Task

To avoid loading all data, you can do a workaround, You can achieve this using a Script Task: (answer writen in VB.NET)

Assuming that the connection manager name is TestAdo and "Select [ID] FROM dbo.MyTable" is the query to get the list of id's , and User::MyVariableList is the variable you want to store the list of id's

Note: This code will read the connection from the connection manager

    Public Sub Main()

        Dim lst As New Collections.Generic.List(Of String)


        Dim myADONETConnection As SqlClient.SqlConnection  
    myADONETConnection = _  
        DirectCast(Dts.Connections("TestAdo").AcquireConnection(Dts.Transaction), _  
        SqlClient.SqlConnection)

        If myADONETConnection.State = ConnectionState.Closed Then
        myADONETConnection.Open()
        End If

        Dim myADONETCommand As New SqlClient.SqlCommand("Select [ID] FROM dbo.MyTable", myADONETConnection)

        Dim dr As SqlClient.SqlDataReader

        dr = myADONETCommand.ExecuteReader

        While dr.Read

            lst.Add(dr(0).ToString)

        End While


        Dts.Variables.Item("User::MyVariableList").Value = "SELECT ... FROM ... WHERE ID IN(" &  String.Join(",", lst) & ")"

        Dts.TaskResult = ScriptResults.Success
    End Sub

And the User::MyVariableList should be used as source (Sql command in a variable)

3rd method - Using Execute Sql Task

Similar to the second method but this will build the IN clause using an Execute SQL Task then using the whole query as OLEDB Source,

1. Just add an Execute SQL Task before the DataFlow Task
2. Set ResultSet property to single
3. Select User::MyVariableList as Result Set

4. Use the following SQL command

   DECLARE @str AS VARCHAR(4000)
   
   SET @str = ''
   
   SELECT @str = @str + CAST([ID] AS VARCHAR(255)) + ','
   FROM dbo.MyTable 
   
   SET @str = 'SELECT * FROM  MySecondDB WHERE ID IN (' + SUBSTRING(@str,1,LEN(@str) - 1) + ')'
   
   SELECT @str
   

If the column has string data type you should add quotation before and after values as below:

SELECT @str = @str + '''' + CAST([ID] AS VARCHAR(255)) + ''','
    FROM dbo.MyTable

Make sure that you have set the DataFlow Task Delay Validation property to True

Importing query results from another database

Use the Linked Table Manager to link the tables in anotherDatabase to myDatabase. Then you can have the query for anotherDatabase in myDatabase and just use that.

In the External Data tab (in myDatabase), click the button that says Import From Access. Except, instead of importing the table, click the radio button that says "Link to the data source by creating a linked table". Just follow the wizard and you should be all set.

You only need to link the tables that you require from your query. Another option would be to write some vba code in myDatabase that instantiates a connection to anotherDatabase and queries it, but I think that just linking the tables is a better solution with less hassle

Use a select query result in an other query

If you can, I recommend sub-query :

select Name
from DB1.client
where Name not in not exists (select name from DB2.client)

If you want reuse a query (not the result), see @Thkas answer.

It isn't possible to reuse the result in SQL Server, because SQL Server release the memory when the result is read. The trick is to insert the query's result in a temporary table, then you read this temporary table as many times as necessary :

--Insert into temporary table
insert into #tmpResult
select Name from DB1.client

--First read
select Name from #tmpResult

--Second read from sub-query
select Name
from #tmpResult
where Name not in not exists (select name from DB2.client)

Performing a query on a result from another query?

Usually you can plug a Query's result (which is basically a table) as the FROM clause source
of another query, so something like this will be written:

SELECT COUNT(*), SUM(SUBQUERY.AGE) from
(
  SELECT availables.bookdate AS Date, DATEDIFF(now(),availables.updated_at) as Age
  FROM availables
  INNER JOIN rooms
  ON availables.room_id=rooms.id
  WHERE availables.bookdate BETWEEN '2009-06-25' AND date_add('2009-06-25', INTERVAL 4 DAY) AND rooms.hostel_id = 5094
  GROUP BY availables.bookdate
) AS SUBQUERY

SQL combine 2 queries to one where 2 queries are from different database

Are you looking for 3-part naming? If so, this will probably work:

select (select COUNT(DISTINCT BaseVehicleID)
        from BaseVehicle
       ) as Old,
       (Select COUNT(DISTINCT BaseVehicleID)
        from [EnhancedStandard_VCDB_Exported_PRD_3006].dbo.BaseVehicle
       ) New

Use output of one SQL query in another SQL query in Python

Here is a working example on how to do what you are looking to do. I didn't look up the schemes for the tablelist, but you can simply substitute the SQL code to do so. I just 'faked it' by unioning a statement of 2 tables. There are plenty of other answer on that SQL code and I don't want to clutter this answer:
How do I get list of all tables in a database using TSQL?

It looks like the key part you may have been missing was the join step to build the second SQL statement. This should be enough of a starting point to craft exactly what you are looking for.

import pypyodbc

def main():
    table_list = get_table_list()
    for table in table_list:
        print_table(table)

def print_table(table):
    thesql = " ".join(["SELECT TOP 10 businessentityid FROM", table])
    connection = get_connection()
    cursor = connection.cursor()
    cursor.execute(thesql)
    for row in cursor:
        print (row["businessentityid"])
    cursor.close()
    connection.close()

def get_table_list():
    table_list = []
    thesql = ("""
    SELECT 'Sales.SalesPerson' AS thetable
    UNION
    SELECT 'Person.BusinessEntity' thetable
    """)
    connection = get_connection()
    cursor = connection.cursor()
    cursor.execute(thesql)
    for row in cursor:
        table_list.append(row["thetable"])
    cursor.close()
    connection.close()
    return table_list


def get_connection():
    '''setup connection depending on which db we are going to write to in which environment'''
    connection = pypyodbc.connect(
        "Driver={SQL Server};"
        "Server=YOURSERVER;"
        "Database=AdventureWorks2014;"
        "Trusted_Connection=yes"
        )
    return connection

main ()

Query across multiple databases on same server

It's not going to be the cleanest solution ever, but you could define a view on a "Master database" (if your individual databases are not going to stay constant) that includes the data from the individual databases, and allows you to execute queries on a single source.

For example...

CREATE VIEW vCombinedRecords AS
SELECT * FROM DB1.dbo.MyTable
UNION ALL
SELECT * FROM DB2.dbo.MyTable

Which allows you to do...

SELECT * FROM vCombinedRecords WHERE....

When your databases change, you just update the view definition to include the new tables.

---

Related Topics