1

Given a specific SELECT statement, how can I (using SQL Server 2008 as a minimum) return 2 identical rows for each row in the SELECT?

I need to create an export CSV file for my client, who will use it to import data into another system. That other system requires 2 rows of identical data for each of the rows of data in my database... don't ask why, I don't know, it just does.

The SELECT is pretty complex, and returns nearly 100 columns of information.

The easiest solution would be to replicate the entire SELECT with a UNION ALL, but that is messy and could potentially result in faults if somebody updates one of the selects and forgets to update the other one (which should never happen, however we're all human).

Is there an easy INNER JOIN technique that could be used to do this, or am I looking at temporary tables/CTEs?

freefaller
  • 19,368
  • 7
  • 57
  • 87
  • 1
    union all with the same select comes to mind as a cross join with a two rows table – jean Dec 17 '18 at 17:20
  • 1
    Thanks @jean - you'll find it also comes to my mind... that's why I put an entire paragraph in the question about it – freefaller Dec 17 '18 at 17:20
  • 1
    fact is: this is a lame requirement I can handle in the app wrtting the file if I can – jean Dec 17 '18 at 17:23

3 Answers3

3

Try this:

CROSS APPLY (SELECT 1 AS Y UNION SELECT 2) X

In use:

SELECT T.*
FROM SYS.TABLES T
CROSS APPLY (SELECT 1 AS Y UNION SELECT 2) X
ORDER BY T.NAME
Dave C
  • 7,272
  • 1
  • 19
  • 30
  • 1
    Ah, nice use of `CROSS APPLY` that hadn't occured to me. Very similar principle of the answer I figured out shortly after submitting the question. Thanks – freefaller Dec 17 '18 at 17:27
1

In typical fashion, shortly after writing the question I figure out a way of doing it...

SELECT *
FROM [MyTable] MT
  INNER JOIN (
    SELECT 1 AS ROWNUM
    UNION ALL
    SELECT 2 AS ROWNUM
  ) SUB
    ON MY.[PrimaryId] = MY.[PrimaryID]
freefaller
  • 19,368
  • 7
  • 57
  • 87
1

Just add CROSS JOIN with a table with only two records like the below example:

declare @Twice as table
(
   Id smallint not null
)

insert into @Twice values (1),(2)

select so.name as [Table], sc.name as [Column]
from @twice t
cross join sys.sysobjects so
join sys.syscolumns sc on sc.id = so.id
where so.xtype = N'U'

Advantage of this technique is you don't need to mess a lot with your query and if you needs more copies just add more rows to the "multiplier" table.

jean
  • 4,159
  • 4
  • 31
  • 52
  • 1
    Thanks @jean, but [Dave's answer](https://stackoverflow.com/a/53820188/930393) implements the `CROSS APPLY` in a much smaller/neater way – freefaller Dec 17 '18 at 17:34