CODEWITHSUNDEEP
sqlsql-servercollation
Luis Garcia
Luis Garcia

Reputation: 1391

Collation issue in UNION Query

I am working on a project where I am tasked to create a series of Union queries to compile data from different databases with the same structure. Some of these queries are very wide with large number of columns. And nope, I cannot change the number of columns. When I tried to create the query it gives a collation error. I can solve that easily with this command added to the column:

COLLATE DATABASE_DEFAULT

Now, the issue is that I have to do 50+ queries each with UNIONs to 5 or 6 different databases and 200+ columns. Every time I add that command to the column I also have to add an alias to the column or I lose the name. Any ideas if there is a more efficient way to do this without having to add the COLLATE clause to every column?

Upvotes: 0

Views: 2287

Answers (1)

Peter B
Peter B

Reputation: 24245

You can take advantage of SSMS Column Editing mode. Here is how that would work.

  1. Put every table column that you want to select on a separate line, and align the dots.
  2. On the first SELECT line, type spaces until where I put the "cursor" (the pipe symbol) below, so that line becomes the longest of all the lines with column names:
SELECT P .AB    |
     , QR.CDEFG
     , S .HIJK
FROM P
JOIN QR ON ...
-- etc
  1. With the cursor in that location, press ALT-Shift-down until you reach the last of the SELECT lines, this gives you a tall multi-line cursor:
SELECT P .AB    |
     , QR.CDEFG |
     , S .HIJK  |
FROM P
JOIN QR ON ...
  1. Now type (or paste) COLLATE DATABASE_DEFAULT AS, this text will appear on all lines at once:
SELECT P .AB    COLLATE DATABASE_DEFAULT AS |
     , QR.CDEFG COLLATE DATABASE_DEFAULT AS |
     , S .HIJK  COLLATE DATABASE_DEFAULT AS |
FROM P
JOIN QR ON ...
  1. Now select exactly the text shown below, either using Alt-Shift-right/down, or using Alt while dragging the mouse:
AB   
CDEFG
HIJK
  1. Use Copy and then Paste the block of text at the end of the first line, with this result:
SELECT P .AB    COLLATE DATABASE_DEFAULT AS AB   
     , QR.CDEFG COLLATE DATABASE_DEFAULT AS CDEFG
     , S .HIJK  COLLATE DATABASE_DEFAULT AS HIJK 
FROM P
JOIN QR ON ...

For more on this, see e.g. https://blog.sqlauthority.com/2016/06/06/sql-server-vertical-select-mode-feature-sql-server-management-studio/

Upvotes: 3

Related Questions