inserting with select distinct 1

[krisplus5]

Well, I've remedied this problem for the time being, but need a real solution:

How can you insert into a table that has a text data type field, from a table with a text data type field, where you must select DISTINCT?

I tried:

insert into table1 
(field1, field2, textfield1)
select DISTINCT field1, field2, convert(varchar(2000),textfield2) from table2

The select distinct phrase works fine on its own. But with the insert it fails with a "The text, ntext, or image data type cannot be selected as DISTINCT" error.

I tried changing the table2 textfield to varchar(2000), and still got the same failure message. It was not until I changed the table1 textfield to a varchar() that I was able to get the insertion to work. (Does this make sense?!?)

We really need to keep that textfield a text data-type. And often don't have control over the source data. Is there a solution?

Cheers,
Kris

 

[donutman]

I don't think there's a solution if the DISTINCT is supposed to consider the textfield, but if you can limit the DISTINCT to the other fields, then it may be possible to pick up the textfield for each row with a join.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[krisplus5]

Hi Donutman,

Yup, your solution works. The update statement will work, although it's not particularly efficient (a SET occurs for every row in the source [table2 in my example], even duplicates). But heh, you can't have everything!

Cheers,
Kris

 

[donutman]

Hmm. Are you saying that you run an Update after the Insert to add the textfield? That's not what I was suggesting. You may be able to insert everthing by a join with a derived table.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[krisplus5]

Hi Donutman,

Here's something representing what I did that works:

--this pulls all the non-text-type data fields 
insert into table1 
(id, field1, field2) 
select distinct eid, efield1, efield2 
from table2 

--this pulls the text-type data fields 
update table1 
set textfield1=table2.etextfield1 
from table2 
where table1.id=table2.eid

As you can imagine, the first script with the distinct is designed to get rid of duplicates--this is a case where the data is a flat-file w/ numerous contacts associated with the same item (a record for each item/contact combination).

The 2nd script updates table1 successfully without any convert() statements or loss of long-text data. But it is very inefficient in that it repeats for every iteration in table2.

So are you suggesting creating a temp table or using a self-join? Can you give me an example? I don't think I'm following what you mean.

Thanks,
Kris

 

[donutman]

[Blue]INSERT[/Blue] [Blue]INTO[/Blue] table1 
      [Gray]([/Gray]id[Gray],[/Gray] field1[Gray],[/Gray] field2[Gray],[/Gray] textfield1[Gray])[/Gray] 
   [Blue]SELECT[/Blue] DT.eid[Gray],[/Gray] DT.efield1[Gray],[/Gray] DT.efield2[Gray],[/Gray] textfield1
      [Blue]FROM[/Blue] [Gray]([/Gray][Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] eid[Gray],[/Gray] efield1[Gray],[/Gray] efield2 
               [Blue]FROM[/Blue] table2[Gray])[/Gray] DT
      [Blue]INNER[/Blue] [Blue]JOIN[/Blue] table2 T2
      [Blue]ON[/Blue] DT.eid[Gray]=[/Gray]T2.eid

-Karl



[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Oh wait. I think I see what you mean now. Table2 has multiple eid's. Let me cogitate on that!
-Karl

 

[donutman]

If table2 has a PK, then you could choose the MIN(PK) to do the join instead of eid. You would need to change the DT query to a Group By to do the work of the DISTINCT and MIN(PK). Are there multiple (should I say duplicate) eid's?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[krisplus5]

Yes, table2 has multiple eid's. For the purposes of this example, there is a category field which is the differentiator for each set of eid's.

I'm basically taking a flat-file and converting it to a related structure.

Cheers,
Kris

 

[krisplus5]

Okay, this one was a nice try (the insert from temp table w/ join), but the join still resulted in duplicates. Alas, it may just have to be inefficient. Thanks for the effort.

Cheers,
Kris

 

[donutman]

Kris,
Good news.

The 2nd script updates table1 successfully without any convert() statements or loss of long-text data. But it is very inefficient in that it repeats for every iteration in table2.

I've done some testing and I don't think that is true, at least as far as the "every iteration" goes. A row from table1 can only get updated once! I haven't been able to verify if the process is inefficient for some other reason. I verified it with code like this:
SET Table1Field=Table1Field+1
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]