How Can I: Save Query Results To A Table In Another Database?

Question

Post reply

How Can I: Save Query Results To A Table In Another Database?

EdA ROC

SSCrazy

Points: 2734
More actions
February 3, 2011 at 7:39 am

#233726

I have another post asking how to export query results to a flat file...
Our 3rd party software vendor just came in the door and wants to change the design:
He now wants the data in a SQL table and he will ODBC to access it.
The source data is in our ERP database which is a 3rd party system. I do NOT want to add tables, or make any changes, to that database (ERPDB). We use Access for reporting and have created another SQL database ERPPLUSDB where we add our own tables and this is where I want the table to reside.
Want to create a job to execute each night at 12:01 AM to run a query on ERPDB and save the results in a table, NCMROrders, in ERPPLUSDB. I'm a newbie working in SQL Server. The 'brute force' solution would be 2 jobs; the first exports the data to a flat file and the second one executes well after and imports the flat file into the table. I think I could tinker a bit and figure out the details of that solution; I still need to learn how to export to a flat file - which is the question in the other post.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

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

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

456789psw SSCarpal Tunnel Points: 4728 More actions · Answer 1

select *

into ERPPLUSDB.dbo.NCMROrders

from ERPDB.dbo.TABLEA

this will create the new table and load it

if you want to clear it each night then you need to run

TRUNCATE TABLE customer

mazzz SSCertifiable Points: 6139 More actions · Answer 2

EdA

You could either use T-SQL or SSIS for this (ie, a T-SQL type jobstep, or an Integration Services type job step.)

T-SQL would be something like:

insert into ERPPLUSDB.dbo.NCMROrders

select col1, col2, ..., colN from ERPDB.dbo.TABLEA

EDIT: as the previous poster said you would have to precede this jobstep with another that removes the existing data from the ERPPLUSDB table

If you think you might be doing a few of these things, it is definitely worth looking into SSIS packages - if you google:

ssis data flow transformations

you should see a lot of results

EDIT 2: you can copy the date straight into a table as you see above, with no need for an intermediate step to copy the data to a flat file first

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

EdA ROC SSCrazy Points: 2734 More actions · Answer 3

That was easy! I didn't realize that tables in different databases could be joined, because I had never seen anything to join them. (Just did a search and read that the only stipulation is that the databases need to be on the same SQL Server).

I didn't use your 'table' approach, no problem. Here's the solution, which will be set up as a job.

Thanks so much! I'm smiling. This was so easy.

INSERT into ERPPlusDB.dbo.QACAOrders
(OrderNum, JobNum, FormNum, CustName, CustPartNum, ItemIdent, QtyOrdered, FullFactoryCost)
SELECT
ORDERS.ORDER_NO AS OrderNum,
ORDERS.JOB_NUMBER AS JobNum,
ORDERS.FORM_NO AS FormNum,
CUSTOMER.CSNAME AS CustName,
ISNULL(SPECS.CUSTOMER_PART_NO,'') AS CustPartNum,
ISNULL(SPECS.CUST_IDENT,'') AS ItemIdent,
ORDERS.QTY_ORDERED AS QtyOrdered,
0 AS FullFactoryCost
FROM ORDERS
LEFT OUTER JOIN JOBS ON ORDERS.JOB_NUMBER = JOBS.JOB_NUMBER
LEFT OUTER JOIN CUSTOMER ON ORDERS.CSCODE = CUSTOMER.CSCODE
LEFT OUTER JOIN SPECS ON ORDERS.SPEC_NO = SPECS.SPEC_NO
WHERE
(ORDERS.PLT_NO = 1) AND (ORDERS.JOB_NUMBER IS NOT NULL)
AND (ORDERS.COMPLETION_FLG <> 'X')
AND (ISNULL(JOBS.JOB_CLOSE_DATE, GETDATE()) > DATEADD(month, - 3, GETDATE()))
ORDER BY OrderNum, JobNum, FormNum;

[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]