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 |
|
hecraulop
Starting Member
2 Posts |
 Posted - 2004-02-20 : 12:13:00
|
| Im insert the information of a lot of different stores in one database. I receive the info in text files and i load this information into a recordset. I have 16 recordset for each store (16 files) and each recordset have aprox. 50000 records. Then i insert this information into the same database but it takes to much time to tranfer the information into the table, here is one of the querys i use:de.Connection.BeginTrans Query = "insert into factura_x_venta values (" & rsfactxventa!ventaid & "," & rsfactxventa!aperturaid & ",'" & rsfactxventa!serieid & "'," & rsfactxventa!facturaid & "," & _ "" & sucursal & ")" de.Connection.Execute Query de.Connection.CommitTransfor the conection i use a data environment, also before inserting the record i verify that is not already in the table. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-02-20 : 12:33:01
|
| How does the query perform in Query Analyzer? Why aren't you using stored procedures?Tara |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-20 : 14:54:14
|
| How does 1 row take too much time?Brett8-) |
|
|
|
hecraulop
Starting Member
2 Posts |
Posted - 2004-02-23 : 18:13:51
|
| Right now i inserted 284220 rows and it took aprox. 55 minutes. Its inserting 4513 rows per minute. Im gonna try the sp but if anyone knows a faster way, tell me please. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-02-23 : 18:16:31
|
| Can you say BCP or BULK Insert!It is probably worth the effort to create a CSV files of the values, and use a BULK load method...Scrap that.. it will be worth the effort consdering the volume.DavidM"SQL-3 is an abomination.." |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2004-02-23 : 18:17:28
|
| You'll do better to use DTS or the BULK INSERT statement to load the records. Inserting a single record at a time from the client is the slowest way to insert data.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
|
|
|
Faster insert query