Merging two dynamically generated tables into one

  • I have two dynamically generated tables that I need to merge into one table for export into a flat file and I'm having a damned hard time.

    Here's the setup:

    Windows Server 2000

    SQL Server 2000

    Both tables have only one common column

    Both tables can and will have different numbers of, name-unknown, columns

    When I try to "select * into " a temp table I get the following error: "Server: Msg 207, Level 16, State 3, Line 189 Invalid column name 'ContactUID'." ContactUID is the one common column between the tables. Since I don't know what the columns are named or how many are there until I execute the code I can't explicitly define the query, as much as I would like to. If I could find a way to select all but one column from a table with an unknown number of who-knows-what-named columns I'd be immensely happy but so far my Google-fu and SQL-fu are weak. Can anyone here help? Or am I on a senseless quest?

    Thanks,

    Mike

    Edit: Brain-Fart on my part. I have control over one of the queries that create the tables. I just needed to add an alias into the query for the common column and then could merge them both into one table with no problem.

  • I assumed you answered/fixed your own question, yes? 🙂

    SELECT * INTO

    creates actual table, and you cannot have duplicate column names in a table

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • Yup, I answered my question. I was just looking at my issue in the wrong spot and thus when I added my alias before, I wasn't changing it in the right place. That caused me to think the alias wasn't possible. Once I realized what I had done and put the alias in the right part of the query all was good.

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

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