Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dwjongbloed
Starting Member
9 Posts |
 Posted - 2007-11-01 : 16:02:12
|
| I'm new to the SQL environment and am looking for some help. I have a client that has a CSV file that we need to import to a table but we need to make this part of an existing job. I'm wondering if anyone has any scripts for importing a CSV file into a DB. I've looked at the Import Wizard but don't think that is going to work the way I want it to.ThanksDamon |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-01 : 16:18:14
|
| Can you clarify "Import" please:Daily (or hourly etc.) at a fixed timeorOn demandand:Replaces existing tableorMerges with existing table?Kristen |
 |
|
|
dwjongbloed
Starting Member
9 Posts |
Posted - 2007-11-01 : 16:36:03
|
| on demand. it will be part of a stored procedure. Replace existing table. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-02 : 05:34:53
|
There are a number of ways to do this. However ...If you are confident that the file will be carefully prepared - i.e. have all the fields it should, no rogue characters, no lines with more/less columns that normal - then BCP is probably your best route. This will "suck" a text file into a table, and is exceptionally quick.Before doing the BCP you canTRUNCATE TABLE MyTableto remove any pre-existing data.It is advisable to drop all indexes [excluding Clustered Index - which is often used for the Primary Key] first, import, then recreate any indexes.If your application will be unhappy with the data "dissapeparing and reappearing" you can BCP into a brand new Temp Table, then drop the original table and rename the Temp Table in its place (not a bad idea anyway in case the import fails for some reason - you will still have the "old" data)If the Import file will be pre-sorted in the same order as the Clustered Index you can provide a Hint to BCP which will save it some work.There is a BULK IMPORT command as well, which is much the same thing (depends whether you want to drive it via SQL or, say, an external Batch file / command)There is also an OPENROWSET command which basically lets you do something like:INSERT INTO MyTable (Col1, Col2, ...)SELECT SomeCol1, SomeOtherCol2, ...FROMOPENROWSET('Remote connection string', 'SELECT RemoteCol1, RemoteCol2, ... FROM RemoteTable')The Remote Connection String is anything acceptable to OLEDB/ADO/etc. So can "connect" to a text file, another database, a spreadsheet, and so on.It isn't as fast as BCP, but its a lot easier to debug, and for smallish volumes, or for import on a server that isn't too busy, or "out of hours", it is a good choice.You could use the Import Wizard, which will basically provide you with a step-by-step point-and-click route, and you can then save that as an SSIS job. You can then run that job on-demand / on a schedule. However, they don't float-my-boat  except for one-off imports. Others who use them all the time will no doubt have more experience and be more comfortable with them. I think that when they fail they are hard to diagnose and you may wind up spending the first hour of every day making sure they all worked, rather than getting alerts as to what didn't work (yes, SSIS can send alerts, but that doesn't seem to stop the folk I know who use them a lot from spending way too much time "tuning" them; YMMV of course!)If that isn't overload! some back with any follow-up questions.Kristen |
|
|
|
|
|
|
|
|
Import CSV file to DB