Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2012
    Posts
    4

    Unanswered: Have 2 custom columns from same column in a temp table

    Here is my situation...

    There is an 'IncidentID' column in table Incident.

    There is a 'CodeDescription' column in table Codes that contains company names and injury type data.

    The column is filtered by the column 'CodeType' also in table Codes. The code type can be company or injurytype (you get the picture).

    I'm trying to create a temp table that will contain a column for company names and a column for injury type. Both these columns describe the incident so there shouldn't be any NULLS.

    Create table #temptable
    IncidentID,
    CompanyName,
    InjuryType

    Insert into #temptable
    Select
    i.IncidentID,
    c.CodeDescription as CompanyName,
    c.CodeDescription as InjuryType


    From incident i
    Inner join Codes c on i.CompanyName = c.Code or i.InjuryType = c.Code

    The above gives me the two columns (CompanyName and InjuryType) but both with company names and injury type data.

    I've looked into Case statements and I end up with nulls in one column or the other. There should not be any nulls.

    Thanks in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Stunshaw View Post
    I'm trying to create a temp table that will contain a column for company names and a column for injury type.
    why?

    i mean, why do you want to create a temporary table, when you can simply query out the data that you need?

    what's the temp table going to be used for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2012
    Posts
    4
    All this code is being placed inside a stored procedure which will create my temp table with all the data that I will need to use in my application. There are many more columns involved that belong to many different tables. I just didn't want to mention it for less confusion. In short, I need it in a temp table for better convenience for me and less strain on my app.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Stunshaw View Post
    I just didn't want to mention it for less confusion.
    you achieved exactly the opposite effect

    by the way, creating a temp table is one of the most expensive things you can do -- it's more strain on your app, not less
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2012
    Posts
    4
    Quote Originally Posted by r937 View Post
    you achieved exactly the opposite effect

    by the way, creating a temp table is one of the most expensive things you can do -- it's more strain on your app, not less
    Then I guess sorry you're confused?

    I think you'll find that most db developers use temp tables, especially when working with stored procedures. They can improve your code's performance and maintain ability if used correctly. But let's get back on track shall we?

  6. #6
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    I think you'll find that most db developers use temp tables,
    Yes, too many do - because they know no better . . .

    They can improve your code's performance and maintain ability if used correctly
    Be interested to see this published by a reputable/acceptable authority . . .

    But let's get back on track shall we?
    By all means, but i suggest that the replies from R97 Are on track.

  7. #7
    Join Date
    Oct 2012
    Posts
    4
    Then I am curious. So you are saying that most db developers are wrong for using temporary tables? Are there not any situations where they are useful? I just find that hard to believe. This link talks about temp tables and their usefulness. So you're saying this is bad practice? I just want to make this clear.

    Quick Overview: Temporary Tables in SQL Server 2005 - CodeProject

  8. #8
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Are there not any situations where they are useful?
    Well, this is 180 degrees in the other direction<g>.

    As with so many "things" there is no one right way for every situation. What is happening way to much is that developers are looking for an "easy way" that will usually require less thought/work, but may perform well.

    What continues to happen with db development is that once a "way" is found, it is propogated as much as possible. It reminds me of when i was working on our houses and my children all wanted to help when they were quite small. First thing i let them do was to pull some nails from wood with a claw hammer. Then i showed them how to pound a small nail into wood and then to nail 2 small pieces of wood together. This took some weeks or months depending on how much we were "working on". Of course, they decided that every "to do" around the house was a nail, and they were cool with the hammer. The 2 that continued to be interested became quite handy with all of the common tools.

    As (db) developers grow, they will usually learn there are multiple "good" ways to address an issue.

  9. #9
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Temporary tables can give performance boosts ONLY if used in the right situation. Unfortunately there many developers that use temporary tables for all situations whether they are applicable or not.

    If you look at what goes on during a temporary table creation you will see that once you exceed a certain threshold of data the data gets put into MyISAM tables (INSERTS) which can be expensive. If there are indexes associated with the temporary table then this too will create more I/O and slow down the overall app. However, on the flip side if the temporary table and its contents are going to be accessed many times then it might be better to have them in the end. The definition of many times is open to interpretation but I would say 100's of queries against a temporary table.

    There are other ways of organizing the data in the database tables using partitioninig with each partition viewed as its own separate table. If you use a good partitioning scheme this might result in better performance without the need of temporary tables.

    Finally if you are developing an application that there are MySQL parameters which provide a connection pool of connections to the database. Rather than closing the connection and reopening it this remains open - the result of this is that the temporary table are not removed as some people believe and they keep adding to the temporary table. This can lead to misleading results.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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