Export > 400000 records ?

[kermit01de]

Linked SQL-table
qry:

SELECT [dbo_GmbH$Sales Shipment Line].[Order Picker], [dbo_GmbH$Sales Shipment Line].Quantity, [dbo_GmbH$Sales Shipment Line].[Gross Weight], [dbo_GmbH$Sales Shipment Line].[Net Weight] INTO Kommi_1
FROM [dbo_GmbH$Sales Shipment Line]
WHERE ((([dbo_GmbH$Sales Shipment Line].[Shipment Date])>=#5/1/2013# And ([dbo_GmbH$Sales Shipment Line].[Shipment Date])<=#5/31/2013#));

This query returns more than 400K records. I need to export them to a new table in the current database. If I try to do so, I am getting an SQL Timeout.

My workaround for this month:
1. - run query
2. - mark ~65000 records
3. - copy
4. - paste into Excel
5. - repeat from 2. until all records are in Excel
6. - save the Excel
7. - Import Excel to new table
8. - create two more queries to sum up the values

As I would like to automate this, I ran out of ideas. Does anyone have a hint?

Thanks in advance

kind regards
Mirko

Kind regards
Mirko
--------------------------------------
>>>>>> ... I am sure, some supportissues are a matter of PEBKAC ... <<<<<

 

[Andrzejek]

You can either try to extent timeout, or do it in (let's say 3) 'chunks':

SELECT SSL.[Order Picker], SSL.Quantity, 
SSL.[Gross Weight], SSL.[Net Weight] INTO Kommi_1
FROM [dbo_GmbH$Sales Shipment Line] SSL
WHERE SSL.[Shipment Date] BETWEEN #5/[blue]1[/blue]/2013# AND #5/[blue]10[/blue]/2013#

SELECT SSL.[Order Picker], SSL.Quantity, 
SSL.[Gross Weight], SSL.[Net Weight] INTO Kommi_1
FROM [dbo_GmbH$Sales Shipment Line] SSL
WHERE SSL.[Shipment Date] BETWEEN #5/[blue]11[/blue]/2013# AND #5/[blue]20[/blue]/2013#

SELECT SSL.[Order Picker], SSL.Quantity, 
SSL.[Gross Weight], SSL.[Net Weight] INTO Kommi_1
FROM [dbo_GmbH$Sales Shipment Line] SSL
WHERE SSL.[Shipment Date] BETWEEN #5/[blue]21[/blue]/2013# AND #5/[blue]31[/blue]/2013#

Have fun.

---- Andy

 

[dhookom]

Have you attempted to use a pass-through query as the source?

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Setting your timeout to 0 means it will run until complete, it could just be a while before it completes.

 

[kermit01de]

Thanks Andy, that works perfectly.

Kind regards
Mirko
--------------------------------------
>>>>>> ... I am sure, some supportissues are a matter of PEBKAC ... <<<<<