CODEWITHSUNDEEP
sql
TDMarz22
TDMarz22

Reputation: 11

How can I add a constant row to an SQL result set?

I am new to SQL but basically I am trying to get customer data and everything works except I want to add an email address to the final results every time this is causing the problems.

This for a volusion based website.

Not sure what I am doing wrong here is my code

SELECT 
    Orders.OrderID,
    Orders.OrderDate,
    Orders.ShipFirstName, 
    Customers.EmailAddress
FROM 
    Orders
JOIN 
    Customers ON customers.customerid = orders.customerid
WHERE
    Orders.OrderStatus = 'Shipped'
    AND Orders.ShipDate >= DATEADD(DAY, 20, GETDATE())
    AND Orders.ShipDate <= DATEADD(DAY, -5, GETDATE())

UNION ALL

INSERT INTO Customers (EmailAddress)
VALUES ('[email protected]');
ORDER BY Orders.OrderID desc

what I am trying to do is this, everytime I run this, add my email address at the end, because we send out order updates to all the people whose information is exported, and I want to make sure that I get a copy of that email, without needing to CC my self on each email.

I don't actually want to add anything to the real table, just to my results

For example right now when I run this I get this result

3104    10/9/2014 12:54:00 PM   Jeanine [email protected]
3065    10/6/2014 1:01:00 PM    Annamarie   [email protected]

But I want this

3104    10/9/2014 12:54:00 PM   Jeanine [email protected]
3065    10/6/2014 1:01:00 PM    Annamarie   [email protected]
null        nulll               null           [email protected]

[email protected] will be changed to my personal email

Upvotes: 0

Views: 1198

Answers (2)

TYY
TYY

Reputation: 2716

INSERT INTO #TempCustomerTable
SELECT 
    Orders.OrderID,
    Orders.OrderDate,
    Orders.ShipFirstName, 
    Customers.EmailAddress
FROM 
    Orders
JOIN 
    Customers ON customers.customerid = orders.customerid
WHERE
    Orders.OrderStatus = 'Shipped'
    AND Orders.ShipDate >= DATEADD(DAY, 20, GETDATE())
    AND Orders.ShipDate <= DATEADD(DAY, -5, GETDATE())
ORDER BY Orders.OrderID desc


-- If you are trying to update all users  email address

UPDATE #TempCustomerTable
SET EmailAddress = '[email protected]'

-- If you are trying to insert new record with just email assuming no null constraints
INSERT INTO #TempCustomerTable
VALUES(NULL, NULL,NULL, '[email protected]')

or 


-- If you are trying to update all users  email address
SELECT 
    Orders.OrderID,
    Orders.OrderDate,
    Orders.ShipFirstName, 
    '[email protected]' AS EmailAddress
FROM 
    Orders
JOIN 
    Customers ON customers.customerid = orders.customerid
WHERE
    Orders.OrderStatus = 'Shipped'
    AND Orders.ShipDate >= DATEADD(DAY, 20, GETDATE())
    AND Orders.ShipDate <= DATEADD(DAY, -5, GETDATE())
ORDER BY Orders.OrderID desc

Upvotes: 1

D Stanley
D Stanley

Reputation: 152644

I'm assuming you don't really want to add the same data to Customers every time you run the query, but just add a synthesized record to the results:

SELECT 
    Orders.OrderID,
    Orders.OrderDate,
    Orders.ShipFirstName, 
    Customers.EmailAddress
FROM 
    Orders
JOIN 
    Customers ON customers.customerid = orders.customerid
WHERE
    Orders.OrderStatus = 'Shipped'
    AND Orders.ShipDate >= DATEADD(DAY, 20, GETDATE())
    AND Orders.ShipDate <= DATEADD(DAY, -5, GETDATE())

UNION ALL

SELECT NULL, NULL, NULL, '[email protected]'

Upvotes: 3

Related Questions