November 26, 2008 at 1:20 pm
Hi i want to create a table and load the result set from the following query and also it should refresh evryday .
SELECT O.ORDERID, c.FIRSTNAME, c.LASTNAME, '' AS CREDITCARDNUMBER, '' AS COUNTRY, 'Soundex' AS GROUPTYPE
FROM ORDERS O JOIN CUSTOMER C ON O.CUSTOMERID=C.CUSTOMERID
WHERE ORDERDATE BETWEEN '01-08-2001' AND '11-23-2008'
AND O.CUSTOMERID IN
(SELECT INT1 FROM BRADTEMP WHERE CHAR1 IN
(SELECT CHAR1
FROM BRADTEMP
GROUP BY CHAR1
HAVING COUNT(INT2) > 3))
UNION
SELECT O.ORDERID, c.FIRSTNAME, c.LASTNAME, CREDITCARDNUMBER, '' AS COUNTRY, 'Credit Card' AS GROUPTYPE
FROM CREDITCARDPAYMENT CCP LEFT JOIN
ORDERS O ON O.ORDERID=CCP.ORDERID LEFT JOIN
CUSTOMER C ON O.CUSTOMERID=C.CUSTOMERID
WHERE O.ORDERDATE BETWEEN '01-08-2001' AND '11-23-2008'
AND LEFT(REPLACE(CCP.CREDITCARDNUMBER,' ',''),12) IN
(SELECT LEFT(REPLACE(CREDITCARDNUMBER,' ',''),12) AS CCNUMBER
FROM CREDITCARDPAYMENT CCP LEFT JOIN
ORDERS O ON O.ORDERID=CCP.ORDERID
WHERE LTRIM(RTRIM(ISNULL(CREDITCARDNUMBER,'')))<>''
AND O.ORDERDATE BETWEEN '01-08-2001' AND '11-23-2008'
GROUP BY LEFT(REPLACE(CREDITCARDNUMBER,' ',''),12)
HAVING COUNT(DISTINCT CCP.ORDERID) > 2)
UNION
SELECT ORDERID, c.FIRSTNAME, c.LASTNAME, '' AS CREDITCARDNUMBER, COUNTRY, 'Country' AS GROUPTYPE
FROM CUSTOMER C LEFT JOIN
CUSTOMERADDRESS CA ON C.CUSTOMERID=CA.CUSTOMERID LEFT JOIN
ADDRESS A ON CA.ADDRESSID=A.ADDRESSID JOIN
ORDERS O ON C.CUSTOMERID=O.CUSTOMERID
WHERE O.ORDERDATE BETWEEN '01-08-2001' AND '11-23-2008'
AND (A.COUNTRY IN ('IRAN','GHANA', 'NIGERIA', 'MAURITANIA', 'MALI', 'BENIN', 'GUINEA', 'SENEGAL') OR
A.COUNTRY LIKE 'COTE D%')
ORDER BY GROUPTYPE, O.ORDERID
help appreciated.
November 26, 2008 at 2:01 pm
Create the table, then do an insert into... select ... with that query as the select. If you want it to refresh ever day, then you'll need to set up a job (SQL Agent) to run daily to delete the rows in the table and reload.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 2:13 pm
thanks gail can you supply the query for me plz
November 26, 2008 at 2:21 pm
You already have the query. All you need to do is create the table to match and then convert the select to an insert. All that required is adding the INSERT INTO above the select
INSERT INTO < Table Name > (< Column list here > )
SELECT ...
< Put your entire select statement here >
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 5:27 pm
If you're looking for an easy way to create the destination table, you could pull out the first SELECT statement (before the first UNION) and do a SELECT...INTO to create the table, then do the INSERT...SELECT as Gail suggested.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply