asked on Extract queried tables from SQL to Access
I need to extract some data from our SQL server to an access mdb. However, it needs to be only the stuff that was edited today. I have the queries for getting the info, and it all works in a normal SQL query, but when I put it into the export wizard as a custom script, it only does the first table. What am I doing wrong? Below is the script that I'm using.
Thanks
BA
SELECT cIns.* FROM Install AS Ins inner join cInstall AS cins ON Ins.UID = cins.MasterUID Where (Ins.Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Ins.Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
SELECT cIss.* FROM Issue AS Iss inner join cIssue AS cIss ON Iss.UID = cIss.MasterUID Where (Iss.Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Iss.Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Classes Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Code Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
SELECT cSer.* FROM Service AS Ser inner join cService AS cSer ON Ser.UID = cSer.MasterUID Where (Ser.Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Ser.Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Cust Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from CustCont Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from CustEQTrans Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from CustEquip Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Defaults Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Install Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from InvenDetail Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from InvenGroup Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from InvenSnap Where (Snapshotdate >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Snapshotdate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Inventory;
select * from InvenTrans Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Invoice Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Issue Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from IssueStatus Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Location Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Maint Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from MaintEQ Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from MatDetail Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from OrderDetail Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from OrderMat Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from OrderStatus Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Paydetail Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Payment Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from PayMethod Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from PayTypes;
select * from Service Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from ServiceItem Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from ShipMeth Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SLACategory Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SMPLink Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SMPLinkSetup Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SMPSystem Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SMPUser Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Terms Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Vehicle Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from VendItem Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Vendor Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from VFuel Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from VMaint Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Zones Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
Thanks
BA
SELECT cIns.* FROM Install AS Ins inner join cInstall AS cins ON Ins.UID = cins.MasterUID Where (Ins.Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Ins.Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
SELECT cIss.* FROM Issue AS Iss inner join cIssue AS cIss ON Iss.UID = cIss.MasterUID Where (Iss.Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Iss.Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Classes Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Code Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
SELECT cSer.* FROM Service AS Ser inner join cService AS cSer ON Ser.UID = cSer.MasterUID Where (Ser.Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Ser.Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Cust Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from CustCont Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from CustEQTrans Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from CustEquip Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Defaults Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Install Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from InvenDetail Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from InvenGroup Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from InvenSnap Where (Snapshotdate >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Snapshotdate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Inventory;
select * from InvenTrans Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Invoice Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Issue Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from IssueStatus Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Location Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Maint Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from MaintEQ Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from MatDetail Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from OrderDetail Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from OrderMat Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from OrderStatus Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Paydetail Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Payment Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from PayMethod Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from PayTypes;
select * from Service Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from ServiceItem Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from ShipMeth Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SLACategory Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SMPLink Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SMPLinkSetup Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SMPSystem Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from SMPUser Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Terms Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Vehicle Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from VendItem Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Vendor Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from VFuel Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from VMaint Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
select * from Zones Where (Editdatetime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND (Editdatetime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1));
Microsoft SQL Server 2008Microsoft Access
Last Comment
bachopper
ASKER
It's from the same database, different tables. And I am using SSIS, but I can't seem to find in the wizard where to customize the data I want, other than the "Use a custom script" section.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Not the answer I was looking for, but I guess it'll have to do.
Is all the data the same just from different sources? If so you can do UNION on all the statements to get them all at once.
Or create a seperate export for each query.
Use a View do combine them all then export the view results
Populate a temp/working table then just export that table
If you have ability to use SSIS (or DTSX) you can use that (a more advanced version of Export Wizard) and actually export wizard will generate 1 of the 2 files (SSIS or DTSX) and let you save it.