Insert with select for multiple records

Question

I have an insert statements for which I want to make 2 inserts. I have the following code:

    INSERT INTO [dbo].[Licemb]
        ([Lic_Id],
        [LicEmb_EmbTS],
        [LicEmb_EmbOffset])

            SELECT TOP 1 
            Lic_ID,
            '00:00:00',
            -7 
            FROM dbo.Lics
            WHERE Org_ID = 2
            ORDER BY NP_ID DESC

            UNION ALL

            SELECT TOP 1 
            Lic_ID,
            '00:00:00',
            -7 
            FROM dbo.Lics
            WHERE Org_ID = 4
            ORDER BY NP_ID DESC

however I keep getting syntax errors and I can't find a work around after searching for a while.

Error:

Incorrect syntax near the keyword 'UNION'.

How can I modify this code so that I can use a single statement to make 2 inserts with selects?

Any help would be much appreciated.

Tag the dbms you're using. That code contains product specific functionality. — jarlh, Sep 20 '17 at 12:23
yes, I have added INSERT INTO [dbName].[dbo].[Licemb] but still errors — Bodz, Sep 20 '17 at 12:25

ttallierchio · Accepted Answer · 2017-09-20T12:56:14.850

3

you can only have one order by for your entire union statement.

if you need to order each select you will need to run a sub query and union them

so

INSERT INTO [dbo].[Licemb]
            ([Lic_Id],
            [LicEmb_EmbTS],
            [LicEmb_EmbOffset])

            select id,daytime,embargo from (
            SELECT TOP 1 
            Lic_ID      AS id,
            '00:00:00'  AS daytime,
            -7          AS embargo
            FROM [NLASQL].dbo.Lics
            WHERE Org_ID = 2
            ORDER BY NP_ID DESC) 

            UNION ALL

            select id,daytime,embargo from (
            SELECT TOP 1 
            Lic_ID      AS id,
            '00:00:00'  AS daytime,
            -7          AS embargo
            FROM [NLASQL].dbo.Lics
            WHERE Org_ID = 4
            ORDER BY NP_ID DESC)

this is not an ideal solution and would ask why you need to order each set of data and then approach the problem from that angle.

edited Sep 20 '17 at 12:56

answered Sep 20 '17 at 12:30

ttallierchio

460
7
17

sql server doesn't allow `order by` in a sub query. – HoneyBadger Sep 20 '17 at 12:31
only if top is not specfied – ttallierchio Sep 20 '17 at 12:32
Very true, only just noticed that bit. Sorry for confusion. – HoneyBadger Sep 20 '17 at 12:33
not a problem I had to do a double take myself as well. – ttallierchio Sep 20 '17 at 12:33
I've added my changes above and I get the following errors: Incorrect syntax near the keyword 'UNION'. Incorrect syntax near the keyword 'SET'. – Bodz Sep 20 '17 at 12:55
we don't have a `set` anywhere in the code provided. my assumption would be this is only a fraction of the code you have. with that said we would be unable to determine what is and isn't missing if we do not have a complete sample of the code. are you using a `set` in your script anywhere else? – ttallierchio Sep 20 '17 at 12:59
ok i've anded the code in this answer. This solution works. Any feedback? – Bodz Sep 20 '17 at 13:08

score 1 · Answer 2 · answered Sep 20 '17 at 12:24

1

If you use a union (all), there can only be one order by, namely after the last unioned query. This order by is applied over all queries in the union.

answered Sep 20 '17 at 12:24

HoneyBadger

14,750
3
34
48

ok but I need the order by for each select, what do I do then? – Bodz Sep 20 '17 at 12:26
In the query you posted you have the same `order by` for both queries, so there is no issue. If you want query 1 to be sorted in a different way than query2, you'll have to think of something clever. For example, you could `select 1 as selection` and `select 2 as selection` for the respective queries. And than `order by selection`. It all depends on how you want thing sorted. – HoneyBadger Sep 20 '17 at 12:28
Maybe this could help you https://stackoverflow.com/questions/5551064/combining-order-by-and-union-in-sql-server. You put select quieries in subqueries – YanetP1988 Sep 20 '17 at 12:29
A bit late, but I only just noticed you use `top 1` as well, in that case you *can* use subqueries with `order by` – HoneyBadger Sep 20 '17 at 12:32

Insert with select for multiple records

2 Answers2