SQL - importing a text file
Discussion
Ok, sound simple so far?
How can I get SQL Query Mangle-iser to only read in certain rows from the text file? Is it better to do a read of all the rows and then just drop the one's I don't need using an update table statement?
Also, the file is fixed width...and I'm going to do this regularly - is there a way I can just code this, rather than using GUI?
(Please don't suggest pressing F1...I know that if you got this far you know how to do it )
How can I get SQL Query Mangle-iser to only read in certain rows from the text file? Is it better to do a read of all the rows and then just drop the one's I don't need using an update table statement?
Also, the file is fixed width...and I'm going to do this regularly - is there a way I can just code this, rather than using GUI?
(Please don't suggest pressing F1...I know that if you got this far you know how to do it )
tigger1 said:
Ok, sound simple so far?
How can I get SQL Query Mangle-iser to only read in certain rows from the text file? Is it better to do a read of all the rows and then just drop the one's I don't need using an update table statement?
Also, the file is fixed width...and I'm going to do this regularly - is there a way I can just code this, rather than using GUI?
(Please don't suggest pressing F1...I know that if you got this far you know how to do it )
Either write a program in VB to do it (easy for a VB programmer) or buy an "Importer". Loads available. T'other easy thing to do is to read your file into Excel, and then cut and paste the data into the back end of an Access "linked table".How can I get SQL Query Mangle-iser to only read in certain rows from the text file? Is it better to do a read of all the rows and then just drop the one's I don't need using an update table statement?
Also, the file is fixed width...and I'm going to do this regularly - is there a way I can just code this, rather than using GUI?
(Please don't suggest pressing F1...I know that if you got this far you know how to do it )
I've never used SQL Query Analyser to import data...sure it will open a text file but what's the point in that? Can you update a table with it after???
Have you tried the DTS tool? Quite a powerful for getting data in/out of SQL Server, and you can schedule it to run as well.
You can define what bits of the input file to drag across and you can map and transform (using VBscript) input fields to output fields quite easily.
Loads of examples of what you can do here:
http://www.sqldts.com/
HTH
FG
You can define what bits of the input file to drag across and you can map and transform (using VBscript) input fields to output fields quite easily.
Loads of examples of what you can do here:
http://www.sqldts.com/
HTH
FG
DTS will do the job and you can embed logic in the import step... but if you want any logging of failure conditions / numbers of rows loaded / ignored / etc. then it's short of functionality.
Integration Services in 2005 is meant to be a LOT better in this regard.
Depending on where you want to keep your logic, it may be more consistent to take the approach suggested above and bulk insert all rows into a staging table and apply logic in a stored proc to which rows to use. If all your other DB data handling logic is in other SPs then having critical jobs hanging out on DTS packages is poor design IMO.
Integration Services in 2005 is meant to be a LOT better in this regard.
Depending on where you want to keep your logic, it may be more consistent to take the approach suggested above and bulk insert all rows into a staging table and apply logic in a stored proc to which rows to use. If all your other DB data handling logic is in other SPs then having critical jobs hanging out on DTS packages is poor design IMO.
cyberface said:
DTS will do the job and you can embed logic in the import step... but if you want any logging of failure conditions / numbers of rows loaded / ignored / etc. then it's short of functionality.
Integration Services in 2005 is meant to be a LOT better in this regard.
Depending on where you want to keep your logic, it may be more consistent to take the approach suggested above and bulk insert all rows into a staging table and apply logic in a stored proc to which rows to use. If all your other DB data handling logic is in other SPs then having critical jobs hanging out on DTS packages is poor design IMO.
Gone for a stored procedure that loads the file into a table, which is then chopped / changed / edited as required until it resembles the required data format. Bit of a bastardisation...but it appears to work, and has saved me the joy of learning how to use Integration Services 2005!Integration Services in 2005 is meant to be a LOT better in this regard.
Depending on where you want to keep your logic, it may be more consistent to take the approach suggested above and bulk insert all rows into a staging table and apply logic in a stored proc to which rows to use. If all your other DB data handling logic is in other SPs then having critical jobs hanging out on DTS packages is poor design IMO.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff