Post reply

Using Column Values from Table to Query Another Table

  • October 13, 2008 at 1:09 pm

    #194555

    Can someone assist me in writing a script that does the following (I know syntax is wrong):

    [font="Courier New"]--Create a new table

    CREATE TABLE newTable

    (ntTableName Varchar(20), ntRowCount numeric)

    --Create a Work Table from the original Source

    Select TableName INTO #TempTbl from OriginalTable --"TableName" is a column that has names of the database tables

    --Use that Work Table to insert Rows into newTable

    WHILE #TempTbl.TableName IS NOT NULL

    BEGIN

    INSERT INTO newTable

    #TempTbl.TableName, --!!Bad value, but not sure why!!

    Select COUNT(*) FROM #TempTbl.TableName --Trying to query the table = from the value in the #TempTbl column

    WHERE CREATEDATE BETWEEN CONVERT(DATETIME, '2008-10-10 00:00:00', 102)

    AND CONVERT(DATETIME, '2008-10-12 23:59:59', 102)

    GROUP BY CREATEDATE

    CONTINUE

    END[/font]

    SQL doesn't like the value of #TempTbl.TableName. I've tried declaring variables but there's something wrong with how I'm doing that also.

    If I attempt to use variables It doesn't like doing a Select COUNT(*)

    Any suggestions are greatly appreciated!

  • October 13, 2008 at 3:22 pm

    #884344

    Couple of points:

    1) Cursors and WHILE Loops are a bad thing and you certainly do not need them here.

    2) You do not need a work table for this, just take the data straight from the original table.

    3) What you do need here is Dynamic SQL, because you can not use a variable as a table name otherwise.

    So, try it like this:

    Declare @sql varchar(max)

    Set @sql = 'INSERT INTO newTable '

    SELECT @sql = @sql

    + (Case When (Row_Number() Over (Order By TableName) > 1)

    Then ' UNION ' Else '' End)

    + ' SELECT '''+TableName+''',

    (Select count(*) From ['+TableName+']

    Where CREATEDATE BETWEEN CONVERT(DATETIME, ''2008-10-10 00:00:00'', 102)

    AND CONVERT(DATETIME, ''2008-10-12 23:59:59'', 102)

    GROUP BY CREATEDATE)

    '

    FROM (Select table_name as [TableName] from information_schema.tables) OriginalTable

    ORDER By TableName

    PRINT @sql -- just so we can see what the SQL command is

    EXEC @sql

    One additional thing: After using GROUP BY on CREATEDATE, it seems to me that you would want to include it in your output. (?)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 13, 2008 at 3:57 pm

    #884351

    Wow! Amazing stuff! Since I'm a SQL newbie it's gonna take awhile for me to digest how you did all that.

    I have a couple of issues still left to figure out; if I can't do it on my own I'll be back for clarification. Thanks again! 🙂

  • October 13, 2008 at 4:50 pm

    #884361

    Glad we could help.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 13, 2008 at 5:05 pm

    #884363

    Very sneaky way to case the union in there Barry. I like it!

    Gary Johnson
    Sr Database Engineer

  • October 13, 2008 at 5:29 pm

    #884366

    Thanks, Gary. "Sneaky" is what I strive for 😀

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 13, 2008 at 6:48 pm

    #884379

    Heh... well, let's get sneaky, then...

    DECLARE @Insert VARCHAR(MAX), @Select VARCHAR(MAX)

    SET @Insert = ' INSERT INTO NewTable '

    SELECT @Select = COALESCE(@Select + ' UNION ALL','') + '

    SELECT '''+TableName+''' AS TableName,

    (SELECT COUNT(*) AS RowsInTable

    FROM ['+TableName+']

    WHERE CreatedDate >= ''2008-10-10''

    AND CreatedDate < ''2008-10-13''

    GROUP BY CREATEDATE)

    '

    FROM (SELECT Table_Name AS [TableName] FROM Information_Schema.Tables WHERE Table_Type = 'Base Table') d

    ORDER By TableName

    PRINT @Insert+@Select -- just so we can see what the SQL command is

    EXEC (@Insert+@Select)

    ... this also includes the "missing second" near midnight.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 13, 2008 at 8:33 pm

    #884394

    Arghhh!!! I forgot the UNION ALL! :w00t:

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 13, 2008 at 8:53 pm

    #884395

    :hehe:

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply