November 8, 2011 at 11:05 am
In my dba position, i'm mostly tasked with data conversions from a variety of sources into sql 2008 databases. This position is new for me and i'm getting loads of great direction from the other dba's who have light years more experience in this than I. When i first started this, i was inclined to simply write my select/insert scripts to move the data from, say Excel sources into the SQL side. I've been encouraged to look into using the Import applet to move this data instead. In any case, what i do is create like tables in a new database and inject my data there. From that point is where i'm now using the import wizard to move the data, once i'm comfortable with it's layout, to it's final destination in the "real" dbs and tables. My question is, what are the advantages of using the import wizard as opposed to using a simple script such as:
INSERT INTO Final.dbo.Table1
SELECT * FROM Testdb.dbo.TestTable1 WITH (NOLOCK)
Just curious
Thanks
james
November 8, 2011 at 11:38 am
Well, I suppose it would be preference. I would say in your situation that the import/export wizard may be a faster method to get the data copied. If you are performing a straight copy with the exact table structure, then i would suggest using the import/export wizard. One reason, you can set all the tables to be copied via checkboxes versus having to type all those queries.
The other case for using import/export wizard is if you have "IDENTITY" columns in the tables and you have to keep the same values. This requires a simple checkbox selected ("Edit Mappings > Enable Identity Insert"). Otherwise, you will have to type out every field in the insert clause.
November 8, 2011 at 11:40 am
what about transaction logs? is the use of the import wizard not logged by SQL as opposed to the execution of that query?
November 8, 2011 at 11:49 am
There's really no difference in using the GUI over TSQL other than it writes the TSQL for you. For instance, with the GUI you can click a checkbox to "Use Table Lock" or set the maximum rows to commit and it just does it for you so you don't have to - http://msdn.microsoft.com/en-us/library/ms174335.aspx
If you use SSIS however, there are MANY more options - Is there any reason you haven't immediately started using SSIS for this? I mean, what you're describing is the purpose the technology was created for 🙂
INSERT INTO Final.dbo.Table1
SELECT * FROM Testdb.dbo.TestTable1 WITH (NOLOCK)
Note: Why would you use a locking hint (as I'm sure you're probably aware, you should be careful using that...)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply