I am am trying to load a SQL table from a flat file. The flat i am talking about is a comma separated file. This has all the data required to populate a table will each column separated by a comma ",". I need some way by which i can load this content into the table faster.
+5
A:
If you are using SQL Server, use BULK INSERT
If you are using Oracle, see my answer here
Galwegian
2008-09-29 10:53:37
this did work.... is there any similar command to get it back to file as well... right now i am doing a select * and saving the result.
Vinodtiru
2008-09-29 11:29:34
i am using SQL...
Vinodtiru
2008-09-29 11:52:02
For SQL server there is the bcp utility, that creates a file that you can read with bulk insert. There is no TSQL command to export data
Harald Scheirich
2008-09-29 12:42:46
+1
A:
Regardless of what database management system you are using, you could use a scripting language (such as perl or php) to set up a connection to your database, parse the file, and then insert the data into your database. Of course, you would have to know a scripting language...
Steven Oxley
2008-09-29 11:10:03
+1
A:
use mysqldump?
mysqldump -u username -p database_name < sql_file.sql
ninuhadida
2008-09-29 11:16:36
this will reload the table.. database_name is required so that mysqldump knows what database the table you're reloading belongs to.
ninuhadida
2008-09-29 13:23:04
A:
This sounds a little bit old-fashioned, but I use an editor which has the capability to record and replay macros for such works.
I use Textpad (www.textpad.com) for this (yes, I bought a license), you might also use UltraEdit (www.ultraedit.com) or something familiar. It's as simple as starting the maro recorder, edit the first line so that it is SQL compatible, go to the next line and stop the recorder. Then you let the editor repeat your macro to the end of the file.
The main advantage is: After you processed the file you can store it and get it into your version control. If done properly, it works for every database (or tool) that can execute files including SQL commands.
Georgi
2008-09-29 12:06:43
+1
A:
take a look at these speed comparisons and decide what suits you best: http://weblogs.sqlteam.com/mladenp/archive/2006/07/22/10742.aspx
Mladen
2008-09-29 12:51:47
A:
For SQL Server 2005, another option would be Integration Services (SSIS); Using SSIS you would be able to do a lot more work on the data during the import process (for example, looking up values in other tables, filtering out rows, importing multiple tables, etc).
Chris Shaffer
2008-09-29 16:53:39