Professional Web Applications Themes

Msg 8163: image data type cannot be selected as DISTINCT - Microsoft SQL / MS SQL Server

I have an 'image' data type column at the end of this table 'TBL_Seller'. It is not even being mentioned as one of the primary keys (cuz its not) in this query: insert [Mink].[dbo].[TBL_Seller] select distinct * from [Mink].[dbo].[TBL_Seller_stage] s where not exists(select * from [Mink].[dbo].[TBL_Seller] c where c.[ClientID]=s.[ClientID] and c.[SellerID]=s.[SellerID] and c.[GroupID]=s.[GroupID]) is there a work around for this problem...those three primary keys have to me 'select distinct'??? thanks, Trint .Net programmer com *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...

  1. #1

    Default Msg 8163: image data type cannot be selected as DISTINCT

    I have an 'image' data type column at the end of this table
    'TBL_Seller'. It is not even being mentioned as one of the primary keys
    (cuz its not) in this query:

    insert [Mink].[dbo].[TBL_Seller] select distinct * from
    [Mink].[dbo].[TBL_Seller_stage] s where not exists(select * from
    [Mink].[dbo].[TBL_Seller] c where c.[ClientID]=s.[ClientID] and
    c.[SellerID]=s.[SellerID] and c.[GroupID]=s.[GroupID])

    is there a work around for this problem...those three primary keys have
    to me 'select distinct'???
    thanks,
    Trint
    .Net programmer
    com

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Trint Guest

  2. #2

    Default Re: Msg 8163: image data type cannot be selected as DISTINCT

    > insert [Mink].[dbo].[TBL_Seller] select distinct * from

    Use SELECT DISTINCT column1, column2, column3 instead of SELECT *.

    Also, if all the columns you're asking for (again, in a named list, not
    using *) are in the PRIMARY KEY, then by definition, they're DISTINCT -- so
    you don't even need that keyword. Unless I'm misunderstanding something.



    Aaron Guest

  3. #3

    Default Re: Msg 8163: image data type cannot be selected as DISTINCT

    Aaron,
    what is the correct syntax for this...i tried it like you have it and it
    didn't work:
    Use SELECT DISTINCT column1, column2, column3 instead of SELECT *.
    thanks,
    Trint
    .Net programmer
    com

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Trint Guest

  4. #4

    Default Re: Msg 8163: image data type cannot be selected as DISTINCT

    I think his is tricky - you have a staging table with no
    primary key, and it may have duplicate rows. SQL
    Server provides no single-query way I know of to
    select the distinct rows of a table with an image column.

    An aside: your query suggests that you want (ClientID,
    SellerID, GroupID) to be unique within the Seller table,
    but select distinct won't guarantee that. I'm assuming you
    know select distinct will be what you need, which means
    that either those three columns don't need to be distinct
    in the Seller table, or that if they match in the staging
    table, all the other columns will match.

    I would try hard to find a way to prevent duplicate
    rows from getting into the staging table in the first place,
    but I can think of a few possibilities:

    1. Add an identity column called pk_staging to the staging
    table, then

    insert into Seller
    select * from staging_table S1
    where not exists (
    <the condition you have already>
    )
    and not exists (
    select * from staging_table S2
    where S2.ClientID = S1.ClientID
    and S2.SellerID = S1.SellerID
    and .. for just GroupID or for
    -- all the other nonimage columns,
    -- depending on what is enough
    and S2.pk_staging > S1.pk_staging
    )

    or

    2. If the number of rows you are inserting is small

    declare rowsAdded int
    set rowsAdded = -1

    while rowsAdded <> 0 begin
    insert into Seller
    select top 1 * from staging_table
    where <your not exists condition>
    set rowsAdded = rowcount
    end

    3. First insert the non-image columns only

    insert into Seller (<list non-image columns>)
    select <non-image columns>
    where <what you have>

    update Seller set
    imageCol = staging.imageCol
    from staging
    where ClientID = staging.ClientID
    and ... all non-image columns match

    I'm not entirely sure if all these work, since I may be
    unaware of some other limitations on image data.

    -- Steve Kass
    -- Drew University
    -- Ref: 7ED318E7-FBA2-42EA-B908-EADA99749ED6


    Trint Smith wrote:
     

    Steve Guest

Similar Threads

  1. Replies: 3
    Last Post: October 18th, 06:56 AM
  2. Replies: 2
    Last Post: October 16th, 08:29 AM
  3. Question about 'image' data type in SQL Server
    By Nicholas Paldino [.NET/C# MVP] in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 14th, 08:01 PM
  4. distinct Domain-Type
    By Marius Müller in forum IBM DB2
    Replies: 1
    Last Post: July 6th, 07:48 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139