CODEWITHSUNDEEP
sqlsql-serverselectinsertunion
Bodz
Bodz

Reputation: 37

Insert with select for multiple records

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.

Upvotes: 1

Views: 59

Answers (2)

ttallierchio
ttallierchio

Reputation: 460

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.

Upvotes: 3

HoneyBadger
HoneyBadger

Reputation: 15140

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.

Upvotes: 1

Related Questions