0

I'm using this trick to load Access data into a temporary table without having to specify all the columns (SQL Server 2008).

SELECT * INTO #tempTable FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
                             'Data Source="d:\Data.MDB"' )...TableName

This works fine for the most part except it converts an Access date to a datetime field and not datetime2 which makes this not work dates before 1753.

Is there any trick to force the use of datetime2 without having to specify all the columns manually?

Charles
  • 50,943
  • 13
  • 104
  • 142
batkuip
  • 1,480
  • 3
  • 15
  • 25
  • Do you have any dates before 1753, if so why, if not does it really matter? – Matt Donnan Dec 08 '12 at 23:42
  • Yes some pre 1753 dates are causing trouble and I can't fix the source. Just bad user input, so happy to adjust the dates. Unfortunately OPENDATASOURCE converts to datetime regardless of the query and then dies because of it. – batkuip Dec 09 '12 at 05:37
  • You could try exporting your Access table to a text file, and then use the **Bulk Insert** routine on SQL Server to load the data. – Matt Donnan Dec 10 '12 at 08:33

0 Answers0