Solved

UNION for text datatype

Posted on 2003-12-10
3
756 Views
Last Modified: 2012-08-14
how to join(UNION) 2 tables  having a column of dataType 'TEXT'

it is giving error
" text,ntext,image can not be selected as distinct "
0
Comment
Question by:singhhome
  • 3
3 Comments
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
Hi,

You could use UNION ALL instead (no distinct performed, but you have to be sure that the two queries union-ed are logically exclusive or you'll get duplicates

Hilaire
0
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
if union all is not an option,
you'll have to convert the text fields to varchar(8000) in the select statement

the bad thing is that it will truncate text that would be more than 8000 cars long

Note: max length for varchar must be lower than 8000 if you have SQL 7

Hilaire
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 50 total points
Comment Utility
The difference between UNION and UNION ALL
is that UNION tries to remove duplicates, UNION ALL doesn't

UNION is logically equivalent to select distinct on UNION ALL

That's why you get this error message

Both are equivalent if the two queries are logically exclusive
(if their intersection is null)

Hilaire
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now