question

unkl_e avatar image
unkl_e 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
databasecteunionvariablewhile
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Very large is right imagine this for each table of each schema of each database. No thanks! Temp tables is not an option since you can’t append past the 1st insert into
0 Likes 0 ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.