3

I have a database named DDOS_WAF_REPORT that has multiple tables with the name format dbo.DDOS_WAF_ACCOUNT_###### (a 5 or 6 digit ID number).

All the tables have the exact same structure (both column names and types). I want to create a view to UNION ALL them together. In addition there will be new tables of the same type that will be added later into the database, so I need the view to be able to 'run through' all existing tables in the database and add them (either adding or creating the view from scratch).

I've created a simple view with the tables name's hard coded but I'm having difficulty how to approach the issue of having the view created dynamically. I'm using SQL Server 2012.

The tables being added are the result of an outside script running against an API per account so I can't avoid having multiple tables in the database. I assumed I'll probably have to write some script code that would actually run and check for new table names but I wanted to see some example to figure out how to do that.

  • Is there any upper limit on the number of tables you are planning to UNION in this way? – Martin Smith Sep 11 '16 at 14:08
2

You can prepare the SQL statement using a cursor to build the SQL string, and execute it using sp_executesql.

DECLARE @TableName nvarchar(400)
DECLARE @DynSQL nvarchar(MAX)
Set @DynSQL = ''

DECLARE cursor1 CURSOR FOR 
    select name
    from sys.tables
    where name like 'DDOS_WAF_ACCOUNT_%'

OPEN cursor1

FETCH NEXT FROM cursor1 INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Add the select code.
    Set @DynSQL = @DynSQL + 'Select * from ' + @TableName
    FETCH NEXT FROM cursor1
    INTO @TableName

    -- If the loop continues, add the UNION ALL statement.
    If @@FETCH_STATUS = 0
    BEGIN
        Set @DynSQL = @DynSQL + ' UNION ALL '
    END

END

CLOSE cursor1
DEALLOCATE cursor1

Print @DynSQL
exec sp_executesql @DynSQL

You can use text mode and see the query that it is building, thanks to the print statement near the end.

  • Thank you very much, this seems to do the task. I did encountered an error executing this code saying: "The conversion of a varchar data type to a date-time data type resulted in an out-of-range value." I read a little bit about it and I think the reason is that I have a column with date values in the format of 'YYYY-MM-DD 00:00:00.000'. Now these dates are returned as such from the API, but I can, if need be, modify my script to rewrite them in a different format if it'll resolve this error. I wanted to ask if there is another solution for this? Maybe by modifying the SQL script? Thanks again – Ariel Hayoun Sep 11 '16 at 14:37
  • One other thing, if the tables do not have a column for the account number (because it's already in the table name), then you may want to provide one in your view definition. So, instead of just 'select * from ' + @TableName it should perhaps be something like 'select ' + replace(@TableName, 'DDOS_WAF_ACCOUNT_', '') + ' as AccountNumber, * from ' + @TableName. – Andriy M Sep 11 '16 at 17:14
  • 1
    Following up on the solution above, how can I combine this SP so that it executes as part of a VIEW? I need the query that is generated to save the results in a view which I can access by some software above it. – Ariel Hayoun Sep 13 '16 at 6:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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