how to create a table and load the resultset of another sql query

Question

Post reply

how to create a table and load the resultset of another sql query

srathna77

SSCommitted

Points: 1982
More actions
November 26, 2008 at 1:20 pm

#194414

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

srathna77 SSCommitted Points: 1982 More actions · Answer 2

srathna77

SSCommitted

Points: 1982

November 26, 2008 at 2:13 pm

#904034

thanks gail can you supply the query for me plz

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Tim Mitchell SSCoach Points: 15671 More actions · Answer 4

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