Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Answered: Iterate Field In Table

    I have a table that houses fully qualified table names, roughly 15. How can I iterate that table and create a view ? For example, something like this
    Code:
    Create Table tocreateviewfrom (dbname varchar(100))
    Insert Into tocreateviewfrom Values (foxfire.dbo.abcd),  (foxfire.dbo.abcde),  (foxfire.dbo.abcdf),  (foxfire.dbo.abcdg),  (foxfire.dbo.abcde),  (foxfire.dbo.abcdl)
    
    --Then somehow iterate each value in the field dbname to create a view something like
    Create View viewcreatedfromtable As
    Select * from foxfire.dbo.abcd
    Union All
    Select * from foxfire.dbo.abcde
    UNION ALL
    Select * from foxfire.dbo.abcdf
    Union all
    Select * from foxfire.dbo.abcdg
    union all
    select * from foxfire.dbo.abcde
    union all
    select * from foxfire.dbo.abcdl
    I tried this, but it is only printing the last result returned not the entire result set
    Code:
    Declare @database varchar(max), @sql varchar(max)
    
    Declare c1 Cursor For
    
    Select Program 
    from tocreateviewfrom 
    
    Open c1
    
    Fetch Next From c1 Into @database
    
    While @@Fetch_Status = 0
    Begin
    	Set @sql = 'Select * ' +
    			   +' FROM '+@database+' '
    
    	Set @sql += 'UNION ALL '
    			   
    	FETCH NEXT FROM c1 INTO @database
    END
    PRINT @SQL
    
    Close c1
    DEALLOCATE c1
    Last edited by jo15765; 07-14-15 at 21:39.

  2. Best Answer
    Posted by Pat Phelan

    "Create a SQL Agent job with a Transact-SQL statement as the job step. Make the code in that job step be:
    Code:
    DECLARE @c NVARCHAR(MAX)
    
    WITH c1 AS (
    SELECT DISTINCT [Program] AS table_name
       FROM [master].[dbo].[tocreateviewfrom]
    ), c2 AS (
    SELECT Row_Number() OVER (ORDER BY table_name) AS r
    ,  Row_Number() OVER (ORDER BY table_name DESC) AS rr, table_name
       FROM c1
    ), c3 AS (
    SELECT r, rr, Cast('CREATE VIEW viewcreatedfromtable AS
       SELECT * FROM ' + table_name AS VARCHAR(MAX)) w
       FROM c2
       WHERE  1 = r
    UNION ALL
    SELECT c2.r, c2.rr, c3.w + '
       UNION ALL SELECT * FROM ' + c2.table_name
       FROM c3
       JOIN c2
          ON (1 + c3.r = c2.r)
    )
    SELECT @c = w
       FROM c3
       WHERE 1 = rr
    
    PRINT @c
    EXECUTE (@c)
    In your trigger, launch the SQL Agent Job using sp_start_job.

    Be sure that you always have good backups, especially of the master database!

    -PatP"


  3. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    With dynamic SQL and INFORMATION_SCHEMA.COLUMNS.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #3
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Thrasymachus View Post
    With dynamic SQL
    How would I append to my
    Code:
     @sql
    statement to get all the names from the table? That is the element I am stuck on.

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is NZDF, but try:
    Code:
    WITH c1 AS (
    SELECT DISTINCT *
       FROM (VALUES
          ('foxfire.dbo.abcd'), ('foxfire.dbo.abcde'), ('foxfire.dbo.abcdf')
    ,     ('foxfire.dbo.abcdg'), ('foxfire.dbo.abcde'), ('foxfire.dbo.abcdl')) AS z(table_name)
    ), c2 AS (
    SELECT Row_Number() OVER (ORDER BY table_name) AS r, table_name
       FROM c1
    ), c3 AS (
    SELECT r, 'SELECT * FROM ' + table_name AS w
       FROM c2
       WHERE  1 = r
    UNION ALL
    SELECT r, ' UNION ALL SELECT * FROM ' + table_name
       FROM c2
       WHERE 1 <> r
    ), c4 AS (
    SELECT 0 AS r, 'CREATE VIEW viewcreatedfromtable AS' AS w
    UNION ALL SELECT * FROM c3
    )
    SELECT w
       FROM c4
       ORDER BY r
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #5
    Join Date
    Feb 2012
    Posts
    188
    @Pat Phelan -- thank you for that syntax. I am getting a few errors on the above syntax. My version is (if this matters) ---- Microsoft SQL Server 2008 R2 (SP1) ----- I actually ran your exact syntax above which prints it to the output window, but does not actually create the view?

    These are the errors I am receiving
    Code:
    Msg 321, Level 15, State 1, Line 8
    "table_name" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.
    Msg 102, Level 15, State 1, Line 12
    Incorrect syntax near ','.
    Msg 102, Level 15, State 1, Line 20
    Incorrect syntax near ','.
    And this is the syntax that I used (please forgive me if I altered it incorrectly)
    Code:
    WITH c1 AS (
    SELECT DISTINCT *
       FROM [Master].[dbo].[tocreateviewfrom ] AS z(table_name)
    ), c2 AS (
    SELECT Row_Number() OVER (ORDER BY table_name) AS r, table_name
       FROM c1
    ), c3 AS (
    SELECT r, 'SELECT * FROM ' + table_name AS w
       FROM c2
       WHERE  1 = r
    UNION ALL
    SELECT r, ' UNION ALL SELECT * FROM ' + table_name
       FROM c2
       WHERE 1 <> r
    ), c4 AS (
    SELECT 0 AS r, 'CREATE VIEW viewcreatedfromtable  AS' AS w
    UNION ALL SELECT * FROM c3
    )
    SELECT w
       FROM c4
       ORDER BY r
    Last edited by jo15765; 07-14-15 at 21:38.

  7. #6
    Join Date
    Feb 2012
    Posts
    188
    I would like to actually create the view from the syntax as my long-term goal is to set the syntax to an "AFTER INSERT" trigger on a table...

  8. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just because you can do something, it may not be a wise idea to actually do it!

    Your sample code breaks a lot of "best practice" rules. I'm pretty sure that you need to re-think what you're trying to accomplish and how you are going about that. The big problems that I see first are:

    1. Building objects in master database is almost always a very bad idea.
    2. I can't think of any case where building objects in a trigger is a good idea.
    3. The database compatibility level is set to SQL 2005, if you can't fix that then you need a completely different approach.

    I may be able to help you, but we'll need to step back more than one level to get to the business problem that you're trying to solve instead of focusing on the SQL that you are using to solve that problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #8
    Join Date
    Feb 2012
    Posts
    188
    The entire process is bad practice to begin with, and unfortunately completely re-vamping the process is not an option at this point.
    Their are multiple databases that show sale information, and when a specific database is identified as not meeting the sale standard they are then placed into this "trouble" table for further review.
    Someone will periodically check this table for any additions, and if a new database has been added a view is updated to display certain metrics for the "trouble" database.
    I was trying to remove the manual process of updating the view by just firing off the creation with a trigger after the insert is performed.

    I know it is a terrible way of going about things, but things are already set in motion and unfortunately I am unable to start from scratch.

  10. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you build the view in some other database instead of master? This is a problem in many ways and it nearly always leads to lost sleep. Using a different database will reduce the risk quite a bit.

    Can you use a SQL Agent Job instead of a trigger? You can start the job from the trigger if you really have to, but you need to decouple the DDL from common DML if you can.

    Can you fix the current SQL 2005 compatibility setting and set it to SQL 2008 R2? That would help a lot too.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #10
    Join Date
    Feb 2012
    Posts
    188
    Yes, a diff database could be used.

    Could the SQL Job be run after insert or would I just need to schedule that to execute at intervals throughout the day?

    What measures should I take to fix the compatibility settings? When I check the compatibility settings for the database it is set at 100 (SQL Server 2008)

  12. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Create a SQL Agent job with a Transact-SQL statement as the job step. Make the code in that job step be:
    Code:
    DECLARE @c NVARCHAR(MAX)
    
    WITH c1 AS (
    SELECT DISTINCT [Program] AS table_name
       FROM [master].[dbo].[tocreateviewfrom]
    ), c2 AS (
    SELECT Row_Number() OVER (ORDER BY table_name) AS r
    ,  Row_Number() OVER (ORDER BY table_name DESC) AS rr, table_name
       FROM c1
    ), c3 AS (
    SELECT r, rr, Cast('CREATE VIEW viewcreatedfromtable AS
       SELECT * FROM ' + table_name AS VARCHAR(MAX)) w
       FROM c2
       WHERE  1 = r
    UNION ALL
    SELECT c2.r, c2.rr, c3.w + '
       UNION ALL SELECT * FROM ' + c2.table_name
       FROM c3
       JOIN c2
          ON (1 + c3.r = c2.r)
    )
    SELECT @c = w
       FROM c3
       WHERE 1 = rr
    
    PRINT @c
    EXECUTE (@c)
    In your trigger, launch the SQL Agent Job using sp_start_job.

    Be sure that you always have good backups, especially of the master database!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #12
    Join Date
    Feb 2012
    Posts
    188
    Thank you for the assistance and valuable insight! It is working as desired now!
    Last edited by jo15765; 07-16-15 at 09:19.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •