asked
How to Use variable to name several database schema and table them put output together
I’m attempting to pull data out of several databases which are identical for the most part except for the db names. The following script works except for the output is in 12 separate results. Caveat is that this would have to be repeatable for all the tables and schemas in those databases Anyone has a workaround to solve this? -------------------------------------------------------------------------------------- DECLARE @DBMonth AS INT = 1 -- SET MONTH (January) DECLARE @DBYear AS INT = 2012 -- SET YEAR ***THE OLDEST YEAR AVAILABLE*** DECLARE @DB AS VARCHAR(255) = 'TranHist1' -- DATABASE NAME DECLARE @DBSchema AS VARCHAR(255) = '.dbo' -- SCHEMA DECLARE @DBTable AS VARCHAR(255) = '.Tabl_1' -- TABLE DECLARE @Sql VARCHAR(250) -- HOLDS THE SQL STATMENT TO BE EXCUITED WHILE @DBMonth BETWEEN 01 AND 12 AND @DBYear = 2012 BEGIN SELECT @Sql = 'select top 10 * from ' + @DB + right('00' + ltrim(str(@DBMonth)), 2) + CONVERT(NVARCHAR(4), @DByear) + @DBSchema + @DBTable BEGIN EXEC (@Sql) SET @DBMonth += 1; --INCREMENT MONTH COUNTER IF ( @DBMonth NOT BETWEEN 1 AND 12 OR @DBYear NOT BETWEEN 2012 AND 2013 ) BREAK ELSE CONTINUE END END
Instead of building a single string and then executing it over and over, build a very large string. Use UNION ALL to combine all the commands into a single statement with a single result set. Alternatively, create a temporary table and insert each query into that table and then as a final step, select the data from the table.
Then don't use T-SQL. It's not meant for work of this type. Write an SSIS package or use some PowerShell so you can easily walk the structure of the database rather than trying to build dynamic T-SQL.
15 People are following this question.
