1

I have 10 databases all in the same instance and schema with identical tables.

Looking to create a SELECT query which can use a variable of the database names and return all of the records into a single dataset.

I've been looking around and put the following together as a test (the real select statement is much larger with multiple joins)

This test does work but it returns two result sets one for each database.

Is there a way to combine the results into one set or am I using the wrong approach?

Thanks in advance

DECLARE @DB_NAME VARCHAR(6);

DECLARE CURSOR_ALLDB_NAMES CURSOR FOR 
    SELECT name
    FROM sys.databases
    WHERE name IN ('CN2DAT', 'AU1DAT') 

OPEN CURSOR_ALLDB_NAMES

FETCH NEXT FROM CURSOR_ALLDB_NAMES INTO @DB_NAME

WHILE @@Fetch_Status = 0
BEGIN
    EXEC ('SELECT * FROM ' + @DB_NAME + '.dbo.ICITEM')

    -- EXEC ('USE '+ @DB_NAME + ' SELECT * from dbo.ICITEM')

    FETCH NEXT FROM CURSOR_ALLDB_NAMES INTO @DB_NAME
END

CLOSE CURSOR_ALLDB_NAMES
DEALLOCATE CURSOR_ALLDB_NAMES
Improve this question
2

2 Answers 2

Reset to default
2

I'd create a view which combines the select statements. e.g.

CREATE VIEW v_ICITEM
AS
SELECT * FROM CN2DAT.dbo.ICITEM
UNION ALL 
SELECT * FROM AU1DAT.dbo.ICITEM
go;

You could include the source database as a column also:

CREATE VIEW v_ICITEM
AS
SELECT 'CN2DAT' AS Db, * FROM CN2DAT.dbo.ICITEM
UNION ALL 
SELECT 'AU1DAT', * FROM AU1DAT.dbo.ICITEM
go;
Improve this answer
5
  • I just tried the view approach with UNION ALL on the real query. It gives the desired results but is pretty big and would be a nightmare to maintain especially when including all 10 databases
    – ColinA
    Commented Jul 13, 2021 at 16:54
  • 2
    @ColinA unfortunately you have a bad situation and there is not going to be a good, easy to maintain T-SQL solution for this. Cross-database queries should be avoided in general, and if you ask me your issue is more of a design issue than anything. This solution is the simplest T-SQL solution. Otherwise you are left with dynamic SQL solutions (like the OP), which is an even worse solution. IMO if you need to query multiple databases then that work should be done in a separate application and not in a single T-SQL script.
    – trenton-ftw
    Commented Jul 13, 2021 at 18:51
  • 1
    What you can do is create scripts that generate the views. If you add a new database - regenerate the views. The real question is why do you need to do this?
    – Jeff
    Commented Jul 13, 2021 at 20:23
  • Hi All, Thanks for your comments. I agree the overall design is lacking. The purpose of the script is to gather sales data for our various entities for reporting purposes, which ideally we would have in a separate consolidated company database. Unfortunately proper consolidation is not an option at the moment. Back to the drawing board.
    – ColinA
    Commented Jul 14, 2021 at 8:49
  • Marking Zeph's answer as correct because I will do the following. Create views in each DB which will be identical then create a parent view to UNION all the subordinates. This works, but not ideal given the lack of consolidation/poor design. At least I can maintain one subordinate view and duplicate it
    – ColinA
    Commented Jul 14, 2021 at 16:04
1

Store the results into a temp table eg:

CREATE TABLE #TempICITEM 
    ([DbName] [varchar](6) NOT NULL,
    [ICITEMId] [uniqueidentifier] NOT NULL,
    [Name] [varchar](100) NOT NULL,
    [Description] [varchar](1000) NOT NULL)

Change your TSQL select to an Insert Into #TempTable Select eg:

EXEC ('INSERT INTO #TempICITEM ([DbName],[ICITEMId],[Name],[Description]) SELECT ''' + @DB_NAME + ''' DbName,[DbName],[ICITEMId],[Name],[Description] FROM ' + @DB_NAME + '.dbo.ICITEM')

Then Select from the combine results of the temp table eg:

SELECT * From #TempICITEM

And all together:

CREATE TABLE #TempICITEM 
    ([DbName] [varchar](6) NOT NULL,
    [ICITEMId] [uniqueidentifier] NOT NULL,
    [Name] [varchar](100) NOT NULL,
    [Description] [varchar](1000) NOT NULL)

DECLARE @DB_NAME VARCHAR(6);
DECLARE CURSOR_ALLDB_NAMES CURSOR FOR 
SELECT name
FROM sys.databases
WHERE name IN ('CN2DAT', 'AU1DAT') 

OPEN CURSOR_ALLDB_NAMES
FETCH NEXT FROM CURSOR_ALLDB_NAMES INTO @DB_NAME
WHILE @@Fetch_Status = 0
BEGIN
EXEC ('INSERT INTO #TempICITEM ([DbName],[ICITEMId],[Name],[Description]) SELECT ''' + @DB_NAME + ''' DbName,[DbName],[ICITEMId],[Name],[Description] FROM ' + @DB_NAME + '.dbo.ICITEM')
FETCH NEXT FROM CURSOR_ALLDB_NAMES INTO @DB_NAME
END

CLOSE CURSOR_ALLDB_NAMES
DEALLOCATE CURSOR_ALLDB_NAMES

SELECT * From #TempICITEM
Improve this answer
1
  • Note too that it doesn't have to be a temporary table. You could make it a permanent one too, just don't forget to add the Database Name as a column so you know the source database of each record.
    – Deano
    Commented Mar 26, 2022 at 2:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.