How Do You Import/Export Data?
-
Its been quite simple and easy when I needed to import/export data in SQL 2000. We have couple lookup tables that we often update to keep the insurance rates current. In SQL 2000 I just used Import/Export tool and it worked perfectly for me. However in SQL 2005 its drives me crazy. I really wanted to know how you guys do in my case. For example I have a lookup table named A and 5 tables: A1, A1, A3, A4, A5. Table A has an identity column being referred by a FK in table A1, A2, A3, A4, and A5. To update table A I must
1. Manually drop the FK in table A1, A2, A3, A4, A5
2. Export the data for table A using SQL Server Studio with option to delete all existing data at destination table first.
3. Manually recreate the FK in table A1, A2, A3, A4, A5.
This is very time consuming and not productive. However I have not figured out another way to make it easier. If you can complete the task in different faster way please share with me.
Thanks.
-
what creating an SSIS package? you can put all the commmands in there and do it in a few steps
-
but in the SSIS package I still need to create the script to drop the FKs, import/export, then script to recreate the FKs?
My goal is to import the whole table with updated date from QA server to a production server and maintain all PK, FKs. To do this I must first drop the FKs in child table A1, A2, A3, A4, A5 then truncate table A, import whole table from QA server, then recreate the FKs. Without deleting the FKs in child tables first I can't replace existing data with updated data. When I need to import 5 tables I must deal with 25 FKs. Its a nightmare.
In the SQL 2000 import/export wizard I didn't have to do that much of steps.
Is there anything I miss in SQL 2005?
-
I am not sure but I think there is an option somewhere to disable foreign keys.... try researching that route...
-
Importing / Exporting data, especially to / from text files, was vastly superior in SQL Server 2000. Since this is probably 90% of what I use DTS packages for, I have found SQL Server 2005 to be a huge disappointment. Once again Microsoft has taken an excellent tool and bastardized it solely for the benefit of .Net programmers.
-
I cannot agree with you more. There are quite a few basic DBA tasks in 2000 that were very easy, quick and simple and in 2005 are time consuming and not straight forward. Try using Mgt. Studio to grant an ID select access to 20 tables easily. There is a long list of things in 2005 I am upset about how they were redone when in 2000 things flowed so well and were so easy. I have to say in 2005 there are some nice features but personally I think they took 5 steps back for 2 steps forward. I am not pushing to upgrade any of our SQL 2000 installs. I used to be able to tell our Oracle DBA SQL server is sooo easy to manage. I can no longer say that with 2005.
-
Funny you cannot please everyone but DTS and SSIS are not the same thing one is a data transfer tool which got extended by users for other things, SSIS is an full ETL(extraction transformation and loading) tool which is considered better than its compititor one million per enterprise deployment Informatica. I understand your feelings about using SSIS for OLTP operation which is what DTS was created for not SSIS, SSIS even include in memory prediction modeling that is a big deal in data mining it moved SQL Server to the top of BI tools in the market because it is free.
Kind regards,
Gift Peddie -
It seems that there is no other way to do less work in order to import/export data in SQL 2005. I agreed that the BI tools in SQL 2005 is good and free but to do a pure DBA job I don't need the fancy tools. I have the option to right click on the db name and select import data/export data without using SSIS so why MS cannot go a bit further to carry the PK and FKs along with import data/export data?
-
You are right about that ETL developer is a separate profession with a 38 point steps developed and published by Ralph Kimbal one of the two people who implemented the Calculus end of the Relational Model.
Kind regards,
Gift Peddie -
You made me nerve
because one of his books is in the list of my next purchase books. I bought one SSIS book written by a man from MS who was in SSIS team. I was totally lost following the book even it was highly rated by professionals at Amazon.com. Maybe I am not at that professional level but I've worked with SQL since 1999 and writing reports using MS SQL RS. I gave up and came back to read the book Delevering BI with MS SQL 2005 by Mr. Larson. I have his another book for SQL RS. Both are well written and easy to follow and pratice.
-
One thing I have learned in software experts like Ralph Kimbal explains things in a few words in plain English, he wrote a book on ETL take a look at it but it was before 2005.
http://www.kimballgroup.com/html/books.html
Kind regards,
Gift Peddie -
Definitely next book must be by Kimball group.
Thanks.
-
That's all well and good, however, by replacing DTS with SSIS Microsoft destroyed the most important feature of any database; the ability to quickly and easily get data into and out of a database. Try mapping a fixed length data file with 150 columns and no headers to a table and you'll understand what I'm talking about. What literally took 5 seconds to do with DTS now takes forever with SSIS. I agree SSIS allows you to do more than what you could with DTS, but if a tool can not easily perform the most fundamental task required of it, it can't be considered an improvement. The Vegematic could slice, dice, and cut fruits and vegetables a hundred different ways, but it wasn't quicker or easier than using a knife.
-
I am sorry I am trying to help but a 150 columns to one table does not qualify for the OLTP end of the relational model because I don't see how you can calculate upper and lower bound cardinality to determine relationship with other tables as required by the ER model. The 2000 developer edition is less than $20 online you can still use it.
Ya know I tried to hold free trainings but Microsoft assumed wizards will fix it now they know better.
Kind regards,
Gift Peddie -
Well this doesn't work in my company. I have overpaid sr. developers that import data straight from excel tables in sql creating 100 plus column tables. The term ER model or normalization is just nothing to them as long as the upper management like them.
Back to the topic I still wish the data import/export feature was there with SQL 2005 as it was with SQL 2000. MS developed the tool so its up to them to drop the feature. However I don't need the amazing SSIS tool to simply import/export. I can use a hammer to put a nail into a piece of 2 x 4. I can also use a brad nailer to do the same thing by doing the steps: plug the compressor into the wall outlet, hookup the air hose into the compressor and the nailer, run the compressor to build up pressure, load the nails into nailer, adjust the pressure so the nailer drives the nail to desired depth, put the safety glasses on, aim and pull the trigger, then clean up. Come on, I just need a hammer.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply