Merge SQL scripts for row condition and table merging

Question

I have these following SQL codes to do clean-up tasks:

SELECT the first n-rows from the table that satisfies a condition and put them in a new table. Note that the [source].var='1' varies for different tables.

SELECT TOP n * INTO tablen
FROM source
WHERE [source].var='1';

# concrete example 
SELECT TOP n * INTO table1
FROM source
WHERE [source].var1='1';

SELECT TOP n * INTO table2
FROM source
WHERE [source].var2='1';        

SELECT TOP n * INTO table3
FROM source
WHERE [source].var3='1';

SELECT TOP n * INTO table4
FROM source
WHERE [source].var4='1';

SELECT TOP n * INTO table5
FROM source
WHERE [source].var5='1';

After making n-tables from the first step, I concatenate them using a query.

# code2
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
UNION ALL
SELECT * FROM table3
UNION ALL
SELECT * FROM table4
UNION ALL
SELECT * FROM table5

Finally I put the results of number two into a new table so I can use it.
```
SELECT * 
INTO dest
FROM code2 
```

Does anyone know how to put these set of tedious tasks into one SQL query so that I don't have to repeat 15 times?

Just to get the top _n_-rows from a table with separate criteria and putting them all together again. — , Dec 31 '13 at 17:13

score 0 · Accepted Answer · answered Dec 31 '13 at 17:31

Ill try to answer this, but this is my first answer so go easy on me. First I'm not sure if the source table is the same for each "TOP" row query. If it is then you dont need tables, 1-5. Unless of course you want the 5 temp tables for some reason...and I assume you know what you're doing with UNION ALL.

Just query the original table inline:

 select a.* into dest
 from (select TOP n *
       from source
       where source.var1='1'
       UNION ALL
       select TOP n *
       from source
       where source.var2='1'
       UNION ALL
       select TOP n *
       from source
       where source.var3='1'
       UNION ALL
       select TOP n *
       from source
       where source.var4='1'
       UNION ALL
       select TOP n *
       from source
       where source.var5='1'
      ) a

Happy New Year @riverdog! This is topnotch! – Jan 01 '14 at 01:02 — , Jan 01 '14 at 01:02

Merge SQL scripts for row condition and table merging

1 Answers1