June 17, 2008 at 12:25 pm
I have a unique situation compared to most of you, I think. I am not a DBA, or even a developer, right now I'm a Data Analyst (read "report-writer and general grunt") and consequently I have read only rights in the dbs, and can't create anything except for Temp tables/procedures, etc.
I am trying to compare invoices from a vendor over the last two years to actual system data received. In order to do this, I need to load the weekly invoices from Excel into a form that I can compare it to our production system in SQL.
I dumped it all into Access in order to clean the data up more easily, then since I don't have BULK INSERT rights on TempDB, wrote a query that creates individual INSERT statements with the values from each row.
Then since I have to have a Temp table, I'm copy/pasting these statements from the Access query results into a script in QA. With 578k of these, QA tells me there isn't enough memory to run them all at once into the Temp table.
Current solution - create batches of 50k lines each, create sub-(temp) procedures that build each 50k chunk, call each sub procedure upon successful completion of the previous ones, and then run my analysis on the end result. Partially doing this so I only have to create the temp table once (each day), then run my summary numbers off of that.
This is a real pain in the :w00t:, so without BULK INSERT or actual table creation privileges, I'm wondering if I can do something else (link to that Access db that holds the data?) that might save me some time next time I have to do something like this.
Any help appreciated.
Thanks!
Jon:D
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 17, 2008 at 1:05 pm
If you have the data in Access, why not just load it into SQL Server from there? It seems to me to take a rather large step backwards to take the data you got into Access, back out of access, only to insert them into SQL Server.
One thought would be - download the SSMA (SQL Server Migration Assistant), which is a tool to move access data and applications into SQL Server. It does a lot of thing you don't much care about, but the one thing you WOULD care about is that it would move the data (that's being stored in an Access data file) into a table or tables in SQL Server.
One shot, one kill, not 600K individual inserts.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 17, 2008 at 1:20 pm
I agree, it's not a step backwards, it's sliding backwards down a greased slope with spikes at the bottom. Just call me Indy.
Thanks for the recommendation - since I can only create tables in Tempdb, I'll be able to migrate the data there with no special rights needed?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 17, 2008 at 1:27 pm
Tell it you want to put it in tempdb...should do the trick. Then you can figure out where to put the stuff.
The other option might just be the import/export wizard (the DTS wizard). You should be able to pick the Access table as a source, and your destination table in SQL server, match up the fields, click "run" and voila.... If you don't have the old SQL 2000 tools available, there is something similar in the SSIS functionality through BIDS (visual Studio).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 17, 2008 at 1:34 pm
🙂 Thanks Matt
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 17, 2008 at 1:35 pm
Just a thought. When I was working with SQL Server 2000 at my last emploer, I had SQL Server 2000 Personal Edition loaded on my desktop system. It had all the features of SQL Server 2000 Standard Edition, and if you had a valid license (CAL) to access SQL Server 2000 on a server, you had a license to run SQL Server 2000 Personal Editon on your desktop (no added cost).
I would see if you could do that, then it is a matter of loading data into tables on your desk top from Access and the server, then doing your data analysis locally.
You could also setup processes that would update your local tables during off hours saving you time.
😎
June 17, 2008 at 1:40 pm
My manager has been fighting with our corporate folks to try and get us a dedicated environment where we can build our own tables and test suites, this may help us do just that.
Thanks!!
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 17, 2008 at 1:41 pm
Can you just use Access to link to the SQL Server tables you need and run the compare in Access? It would probably be a pretty slow operation, but it might be substantially simpler.
June 17, 2008 at 1:46 pm
David Webb (6/17/2008)
Can you just use Access to link to the SQL Server tables you need and run the compare in Access? It would probably be a pretty slow operation, but it might be substantially simpler.
Could, and thought about that, but I needed to be able to do some analysis of where the issue lies, to see if we just aren't getting the data, or if someone on our end has it hung up somewhere; so I would need to link not only to my Access data repository, but three other dbs on the server (staging tables, pre-processor, production environment) in order to figure that out.
Since I only have 37.5 more years until I retire, I decided to work in QA 😛
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 17, 2008 at 1:56 pm
David Webb (6/17/2008)
Can you just use Access to link to the SQL Server tables you need and run the compare in Access? It would probably be a pretty slow operation, but it might be substantially simpler.
I'd stay away from this as well. There is a power user at my last employer who as an Access database he uses for a variety of reporting. He is no programmer or DBA, and you can tell when he was hitting our AIS server. He'd do joins between Access tables and SQL tables causing a lot of network traffic from our server, and others just trying to run simple ad hoc reports would complain about slow response times. Ans this was not a production database, but just a reporting database. The production server was on Solaris running ICOBOL with ISAM database files. We downloaded data nightly for reporting purposes.
😎
June 17, 2008 at 8:14 pm
jcrawf02 (6/17/2008)
My manager has been fighting with our corporate folks to try and get us a dedicated environment where we can build our own tables and test suites, this may help us do just that.Thanks!!
Jon
Don't necessarily need your own environment although that would be the best way to go....
You could ask the DBA for a "Sandbox" database to work in where you and some of the others have DBO privs.
You could also spring between 40 and 60 bucks a copy for the Developers Edition of SQL Server. I believe that every serious SQL Developer should have it on their desktop. Cool things about it are it IS the Enterprise Edition and it DOESN'T need to run on a Windows Server... just about any desktop box will do.
Heh... of course, I had them drop a couple of 300GB drives into my dual CPU 2 G Ram "desktop"... it smokes everything except the production server.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply