1

Hello everyone,

A while back we converted from Access to SQL Server, and things have been running relatively smoothly and I have learned a great deal. However, I have about 600,000 new rows/records that I need to import into SQL Server from an Access database. I mostly followed this guide here: (however I didn’t create a new table, instead trying to import into an existing table)

https://help.avalara.com/Frequently_Asked_Questions/Miscellaneous/How_do_I_import_MS_Access_database_into_a_SQL_Server_database%3F

I had to save my .accdb into a .mdb first but it went mostly smooth, however after 95,000 records it stopped and gave the following error:

https://pastebin.com/AajYr1Pg

I also tried using Microsofts " AccessToSQL" program, which imported all the files, but it truncated the table first (all the millions of other records were removed - which is fine, I am doing this all on a development database before production).

What is the best way, for me to get these records into the SQL Server without truncating or editing any of the current records in SQL Server, just adding the new ones?

Should I convert to text first? Any help is apprciated greatly!

6 Spice ups
2

For Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.

I have witnessed a similar issue, which seemed to be related to re-using the same database connection for concurrent tasks. There maybe a few alternative solutions, but this issue was resolved by setting MaxConcurrentExecutables to 1.

See this link for MS SQL info .

For

  • Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available. Source: “Microsoft OLE DB Provider for SQL Server” Hresult: 0x80004005 Description: “The statement has been terminated.”.
    An OLE DB record is available. Source: “Microsoft OLE DB Provider for SQL Server” Hresult: 0x80004005 Description: “Violation of PRIMARY KEY constraint ‘MSTR-SENT$PrimaryKey’. Cannot insert duplicate key in object ‘dbo.DEVELOPMENT’. The duplicate key value is (20189999-BG999, customcology.com, 6375 DISCOVERY BLVD).”.

Cause

As you can read on above error message, it states a primary violation has been captured trying to insert a duplicate primary key on table dbo.DEVELOPMENT, even the duplicate key values are shown on the error message ( 20189999-BG999, customcology.com, 6375 DISCOVERY BLVD) .

Solution

A few possible solutions:

Make sure the data source does not have duplicate values on the fields that are part of the OLEDB Destination primary key. To remove duplicates that exist on the data source you can use the Sort Transformation Task (SSIS Toolbox → Common → Sort) and make use of the checkbox named “Remove rows with duplicate sort values”. You can find a practical example here . Alternatively, you can use the Aggregate Transformation task as explained here .You can use the Lookup Transformation within the Data Flow task to check for data that already exist on the destination table. Click here for more information.

Cheers,

Charlie

1 Spice up
3

When importing large amounts of data as a one-time operation, I normally import the data (from wherever it exists) into a SEPARATE sql database table first.

I then look at the imported results, and if ok, use TSQL to copy it over to the desired table.

If I have to change any data or resolve duplicates, or other, I can do it using TSQL in the copy operation.

It is much easier to work with two sets of data if they are both in the same database server.

3 Spice ups
4

Hello,

So, I copied the data from the Access database into it’s own SQL server table, and they all copied over. correctly! However, can you help me with the query to remove the duplicate records inside of table “UPDATE” in comparison to table “DEVELOPMENT” in the database “ADDEV”. Then, once I remove the duplicate records from the source table (UPDATE), what would the query be to copy all the data from “UPDATE” to “DEVELOPMENT”?

Thank you!

5

Do you know how to use TSQL ?

To view records in DEVELOPMENT table:
select * from DEVELOPMENT

To view records in UPDATE table:
select * from UPDATE

Is the table and field structure between UPDATE and DEVELOPMENT exactly the same, without any identity fields?

If yes, it is quite easy to copy all the info from one to another, no field names needed:
insert into DEVELOPMENT select * from UPDATE

Otherwise you will need fieldnames:
insert into DEVELOPMENT (field1,field2,field3) select field1,field2,field3 from UPDATE

Now as to removing duplicates that are already in DEVELOPMENT from a previous insert - that depends on what you call duplicates; we will need to know the field names.

1 Spice up
6

Hello, I know basic TSQL. I deleted my past comment because it’s no longer correct. There are no identitiy fields, however there is a “TimeStamp” field from Microsofts “Access to SQL” program.

Both tables have the same columns, in the same order. However, two of the columns might not match “[RTRN-STATE]” and “SSMA_TimeStamp”. The primary keys of DEVELOPMENT are “BATCH, STEXT, ADDRESS”.

I need to remove duplicate values before I can “insert”

Columns are as followed:

index, batch, website, stext, company, address, city, state, zip, industry, [new-ind], [mail-date], [rtrn-state], [order], [in-date], [chk-date], amount, phone, notes, verifier, cancelled, [batch-group], dnc, manager, ssma_timestamp

I keep attempting to insert, but it keeps finding duplicates as such:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'MSTR-SENT$PrimaryKey'. Cannot insert duplicate key in object 'dbo.DEVELOPMENT'. The duplicate key value is (20189999-BG999, myewecc2.org, 11157 CITY POND RD).
The statement has been terminated.

So yeah, based off maybe the primary keys I need the duplicates inside “IMPORT” to be removed. Before I can insert. I am alost not inserting the timestamp as that has issues.

7

Before doing the below, please backup/copy your DEVELOPMENT database table so you can restore it if needed.

To find duplicates, do an INNER JOIN using your primary key fields.

select d.[index],d.[batch],d.[website],d.[stext],d.[company],d.[address]
from [DEVELOPMENT] d
inner join [UPDATE] u
	on u.[batch]=d.[batch]
	and u.[stext]=d.[stext]
	and u.[address]=d.[address]

If you want to delete the same above records from DEVELOPMENT, change the select line to ‘delete d’

delete d
from [DEVELOPMENT] d
inner join [UPDATE] u
	on u.[batch]=d.[batch]
	and u.[stext]=d.[stext]
	and u.[address]=d.[address]

I put the field names and table names in since you have several names that coincide with SQL reserved words.

You can also delete records from DEVELOPMENT using the timestamp field.

delete from [DEVELOPMENT]
where ssma_timestamp between '2019-02-01 00:00:00' and '2019-02-01 15:23:35'

delete from [DEVELOPMENT] 
where ssma_timestamp between '2019-02-01' and '2019-02-15'
8

To insert rows from UPDATE into DEVELOPMENT:

insert into DEVELOPMENT
(index, batch, website, stext, company, address, city, state
, zip, industry, [new-ind], [mail-date], [rtrn-state], [order]
, [in-date], [chk-date], amount, phone, notes, verifier, cancelled
, [batch-group], dnc, manager, ssma_timestamp)
select
index, batch, website, stext, company, address, city, state
, zip, industry, [new-ind], [mail-date], [rtrn-state], [order]
, [in-date], [chk-date], amount, phone, notes, verifier, cancelled
, [batch-group], dnc, manager, getdate()
from UPDATE

You will note I am using getdate() for the last field to populate the ssma_timestamp field in DEVELOPMENT.

9

Thank you for all your help, you have been so incredibly generous with your time and knowledge. The insert is failing due to :

Msg 273, Level 16, State 1, Line 1
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

Any ideas, besides just removing it from the insert?

10

Looks like your timestamp field column is autogenerated. Just take it out.

insert into DEVELOPMENT
(index, batch, website, stext, company, address, city, state
, zip, industry, [new-ind], [mail-date], [rtrn-state], [order]
, [in-date], [chk-date], amount, phone, notes, verifier, cancelled
, [batch-group], dnc, manager)
select
index, batch, website, stext, company, address, city, state
, zip, industry, [new-ind], [mail-date], [rtrn-state], [order]
, [in-date], [chk-date], amount, phone, notes, verifier, cancelled
, [batch-group], dnc, manager
from UPDATE
11

I thought that was it, I just didn’t think it autogenerated due to AccessTo SQL generating it.

THANK YOU!

12

Glad to have been of help!