Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    31

    Unanswered: Union with text fields

    I'm unable to run a Union query with text fields in it. I get the following error:

    'The text, ntext, or image data type cannot be selected at DISTINCT'

    Is there no way to run a UNion statement for two tables that have text fields in them??? Please help.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Try UNION ALL

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 text)
    CREATE TABLE myTable00(Col1 int IDENTITY(1,1), Col2 text)
    GO
    
    INSERT INTO myTable99(Col2)
    SELECT '01234' UNION ALL
    SELECT '56789'
    
    INSERT INTO myTable00(Col2)
    SELECT '01234' UNION ALL
    SELECT '56789'
    GO
    
    SELECT Col1, Col2 FROM myTable99 UNION ALL
    SELECT Col1, Col2 FROM myTable00
    GO
    
    SELECT Col1, Col2 FROM myTable99 UNION
    SELECT Col1, Col2 FROM myTable00
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    DROP TABLE myTable00
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2004
    Posts
    31
    Thanks. That works. I didn't even realized that Union ALL existed.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    UNION will give you all rows, which are not duplicated. This requires a Sort (well in this case a distinct?)

    UNION ALL is EVERYTHING
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good catch, Brett!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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