August 18, 2008 at 1:38 am
I am working in a team building a DataWarehouse. Because the solution needs to be independed of the source there is a need for creating files. When you are digging into this subject there are a couple of methods:
- BCP
- OSQL
- Filesystem object
The file must have a header and a footer and in between the file needs to be filled with detail records.
Originally this solution was allready created in Oracle. The translation of Oracle into SQL Server resulted in use of the file system object.
After implementing this solution and monitoring the performance this was really terrible 600.000 rows in 15 min. I wanted to use the bcp utility for creating files. While using the bcp utility i noticed that every time you write some data to the file the file is overwritten. I tried to put the header and detail and footer in a table var but that didn't work.
Now i created a temp table and write all te records here and use bcp to export to a file. The same 600.000 records take about 1.2 minutes.
:hehe:
August 18, 2008 at 3:27 am
I'm not sure if you use SQL 2000 or SQL 2005.
If you work with SQL 2005 you should explore the SSIS.
August 18, 2008 at 3:30 am
Hi there,
I am using SQL2005. The customer wants it to be in T-SQL. So the SSIS was not an option. Thanx for the reply.
:hehe:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply