How to insert million records ?

[SamMagic]

Hello everybody.

I'm working with CFMX7 and SQL SERVER 2005 on WIN 2003 standard edition.

I 've to export thousands and thousands records from txt files (csv format) to a sql server 2005 DB.

I use CFHTTP to read the txt file and stored procedure to insert data to my table,
First problem: I put correct datatype for datetime but always get a error : "Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query. "

Second problem:t since each txt files content at least 80 000 records, it takes a very long time and i always get a 500 NULL message (I don't know what is it).

Is there a solution more efficient to import milion records ???

thank you

[Guest]

Your going to get a lot of responses telling you to leave CF out of the mix. Have you looked at using DTS packages within SQL Server?

[SamMagic]

Can I make so that there'll be schedule DTS task with defined csv files ?
is it possible ?
could you give me some links about how create DTS and manage them

thanks

[paross1]

SQL Server Books Online (BOL), which you can download, has extensive information about using DTS, so you might consider starting there. If you are using SQL Server as your database, I consider having access to BOL as a very good idea.

DTS Import/Export Wizard
Of all the Data Transformation Services (DTS) tools, the DTS Import/Export Wizard provides the simplest method of copying data between OLE DB data sources.

After connecting to the source and destination, you can select the data to import or export and apply transformations to the data being copied (for example, by selecting columns or using Microsoft® ActiveX® scripts). In many cases, you can automatically copy primary and foreign key constraints along with the source data.

Note You can copy data that results from an SQL query. SQL queries can include joins of multiple tables from the same database or distributed queries. As part of the process, the DTS Import/Export Wizard creates the destination table for you automatically if none exists.

Available Data Sources
With the DTS Import/Export Wizard, you can connect to the following data sources:

Most OLE DB and ODBC data sources, as well as user-specified OLE DB data sources.

Text files.

Other connections to one or more instances of Microsoft SQL Server™.

Oracle and Informix databases.
You must have the Oracle or Informix client software installed.

Microsoft Excel spreadsheets.

Microsoft Access and Microsoft FoxPro® databases.

dBase or Paradox databases.
For more information, see DTS Connections.

Transforming Data
In addition to copying data, you can transform column-level data with an ActiveX scripting language such as Microsoft Visual Basic® Scripting Edition (VBScript) or Microsoft JScript®. For more information, see DTS Transformations, Transform Data Task, and Using ActiveX Scripts in DTS.

Copying Database Objects
With the DTS Import/Export Wizard, you can transfer database objects such as indexes, views, roles, stored procedures, and referential integrity constraints. For more information, see Copy SQL Server Objects Task.

Saving DTS Packages
After you complete the DTS Import/Export Wizard, you can save the connections, transformations, and scheduling information as a DTS package. The package can be saved:

To the SQL Server msdb database.

To SQL Server 2000 Meta Data Services.

As a structured storage file (.dts file).

As a Visual Basic file.
You can run the package immediately or schedule it for later execution.

For more information, see Saving a DTS Package and Scheduling a DTS Package for Execution.

Editing Packages
If you create a package with the DTS Import/Export Wizard and then save it, you can edit it in DTS Designer. Using DTS Designer, you can customize the basic package you created in the DTS Import/Export Wizard, adding steps, tasks, transformations, event-driven logic, and configuring workflow. For more information, see DTS Designer.

Phil

[cf_dev2]

You might also look into BULK INSERT