Post reply

Need advice on using multiple #temp tables with the same column names

  • February 7, 2012 at 12:42 pm

    #267961

    I am creating a new report using a stored procedure that was provided to me by a business partner. Using BIDS 2008 I am encountering the following error.

    The query contains more than one unnamed or duplicate field name.

    The sp utilizes many temp tables which seem to be causing the problem. There are too many temp tables in the procedure for me to blank out all the sensitive info but below are a few of these statements. The last query in the proc pulls data from these temp tables, along with some permanent tables into one massive result set. I'm guessing the similar names in the columns of these temp tables is what is causing the error? should i use table variables instead of these temp tables? what is the best approach here? i know that having all of these temp tables isn't the best practice anyway, but what is?

    SELECT

    code.acct_num,

    code.code1

    code.date_start

    INTO #tmp_tbl1

    FROM weis.dbo.eco_map_code1 code

    WHERE (((code.code1)='XX'))

    SELECT

    code.acct_num,

    code.code1

    code.date_start

    INTO #tmp_tbl2

    FROM weis.dbo.eco_map_code1 code

    WHERE (((code.code1)='YY'))

    SELECT

    code.acct_num,

    code.code1

    code.date_start

    INTO #tmp_tbl3

    FROM weis.dbo.eco_map_code1 code

    WHERE (((code.code1)='ZZ'))

  • February 7, 2012 at 2:31 pm

    #1444139

    justintime (2/7/2012)

    I am creating a new report using a stored procedure that was provided to me by a business partner. Using BIDS 2008 I am encountering the following error.

    The query contains more than one unnamed or duplicate field name.

    That's not complaining about the same name in multiple tables. It's complaining about using SELECT INTO with either something like: CASE WHEN abc IS 'a' THEN 1 ELSE 2 END and not putting in an AS column1 onto it, or duplicating the same field name in the select statement.

    So, look for something with a function in it as a column name, or the same column included twice, even if it's from two different tables. One of those will need to be aliased.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 2 posts - 1 through 1 (of 1 total)

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