Appending SQL Tables from MS Access SET IDENTITY_INSERT takes a long time why?

Rog D
Rog D used Ask the Experts™
on
MS Access append query to SQL Server table.  SQL Table is blank but does have identity column.

When I run the SET IDENTITY_INSERT [Table] ON;

I try to run the Append Query in MS Access, but get a Key Violation.  

If I wait X minutes then run the same Append Query again, it adds the records.

I am wanting to speed this process up as I have many tables to work on.

What is the best way to do this?

SQL Server 2012 R2

Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Just for kicks and giggles, explain in detail why you are attempting a SET IDENTITY_INSERT ON statement, as this is not always best practice.
Rog DSQL Developer / Web Development / Business Analysis

Author

Commented:
Basically I am refreshing a SQL relational database from MS Access.

The Constraints have be set to NO CHECK while I load so I can load the PK fields in the tables.

I run the SET IDENTITY_INSERT ON to allow this.

Once I do load the tables, I then set the SET IDENTITY_INSERT OFF; on the table.

I repeat this process for each table.

I have about 30 tables.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Just noting that  SET IDENTITY_INSERT ON / OFF is 'per session' and can only be in effect for on Table per session.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017
Commented:
This is one of the more annoying "features" of SQL Server.  However, I don't have any problem with it.  Here's the code I use
        'when tables are linked SQL, the identity insert must be set to on
        'to allow rows with existing autonumbers to be inserted and then off at the end.
        'We do this so that the test data always retains its original autonumbers.
        Set db = CurrentDb()
        Set qd = db.CreateQueryDef("")
            qd.Connect = "ODBC;" & strConnect
            qd.ReturnsRecords = False
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblAuditParms" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_110_AppendAuditParms"
            qd.SQL = "SET IDENTITY_INSERT " & "tblAuditParms" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblListValues" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_130_AppendListValues"
            qd.SQL = "SET IDENTITY_INSERT " & "tblListValues" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblDocuments" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_140_AppendDocuments"
            qd.SQL = "SET IDENTITY_INSERT " & "tblDocuments" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblMembers" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_210_AppendMembers"
            qd.SQL = "SET IDENTITY_INSERT " & "tblMembers" & " OFF"
            qd.Execute
''~~~~~~~~~~~~~~~~~~
            qd.SQL = "SET IDENTITY_INSERT " & "tblDependents" & " ON"
            qd.Execute
            DoCmd.OpenQuery "qRESET_220_AppendDependents"
            qd.SQL = "SET IDENTITY_INSERT " & "tblDependents" & " OFF"
            qd.Execute
            
            qd.Close
            db.Close

Open in new window

The simplest answer is that it was intentionally left to work that way. Microsoft wants us to license SQL Server... so they didn't put much effort into optimizing getting data out of Access and into SQL Server from the MS Access perspective/direction.

Your best bet will be to go the other direction; use SSIS to load the data with the Access DB as your source.

The alternative would be to write a simple script to run a bulk insert on each table. Please note that this option will give you the most flexibility.

Good Luck!
Rog DSQL Developer / Web Development / Business Analysis

Author

Commented:
I have more information to now share.

I have found that under Options -->  Client Settings --> Advanced Section

Default
     Checked Open Database by Using record-level locking  (I unchecked)

    ODBC Refresh Interval (SEC) was default to 1499   (I changed to 10)

    Refresh Interval (sec) 60 Default   (Changed to 10).

Now this didn't exactly resolve the issue.  

But now I can run SSMS SET IDENTITY_INSERT TableName ON;
Open access run query
close access
run SSMS SET IDENTITY_INSERT TableName OFF;

Repeat and it works every time.

HOWERVER:  I tried to wait and it doesn't seem to work without closing access and opening it back up.

I did the open close before changing the Advanced Items, but no luck.  So the advanced changes did have some affect.
Distinguished Expert 2017

Commented:
"close access" ?? Aren't you running this process from a code module in Access?

I posted code that works.  It is not air code.

Please post your entire procedure.
Rog DSQL Developer / Web Development / Business Analysis

Author

Commented:
Sorry,

I didn't write any code as this was supposed to be a run one or two times and be done and in SQL.

I will utilize your code and give it a try.  I do however think the session thing vs MS Access having it's own session may have something to do with this since they don't share the same session.  Maybe after sometime X is when Access realizes the Idnetiy is on and allows the insert.  Who knows.
Distinguished Expert 2017

Commented:
You included Access in the topics list.  Since the data is SQL Server, the only reason to include Access is because you want to run the code there.

We can't help if you don't post your code.  All we are doing is guessing.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"I will utilize your code and give it a try.  I do however think the session thing vs MS Access having it's own session may have something to do with this since they don't share the same session"
It does ... I've already been down this road.  I will post how I did this later tonight.
Commented:
Just a couple of notes of what works for me.  Three different things worked.

1) PatHartman's code above (Give Pat the points) plus add refresh Link of the table (See my code below that is basically Pat's code)
2) Close the database / Open it back up / Run a pass through query with SET IDENTITY_INSERT YourTblName ON / Append records
3) Wait 10 minutes or so from last append.

Please give Pat the points if this helps - I basically used his code.

Hope this helps

Public Function Export_to_SQL()

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim Tbl_df As TableDef


Dim strTableName As String: strTableName = "tbl_Menu_Items"
Dim strQry_Set_Identity As String: strQry_Set_Identity = "sql_05_qry_Set_Identity_Insert"
Dim strQry_Append_Records As String: strQry_Append_Records = "sql_65_qry_tbl_Menu_Items_11_Append"


'Set Query's SQL
   Set db = CurrentDb
   Set qdf = db.QueryDefs(strQry_Set_Identity)
   Set Tbl_df = db.TableDefs(strTableName)
   qdf.SQL = "SET IDENTITY_INSERT " & strTableName & " ON"
   qdf.Execute
   Debug.Print "Id ON"
   
    DoCmd.SetWarnings False
    DoCmd.OpenQuery strQry_Append_Records
    
    
    DoCmd.SetWarnings True
   
   Debug.Print "Qry Ran"
   
   qdf.SQL = "SET IDENTITY_INSERT " & strTableName & " OFF"
   qdf.Execute
   
   Tbl_df.RefreshLink
   
   Set db = Nothing
   Set qdf = Nothing
   Set Tbl_df = Nothing
   
   Debug.Print "Finish"
 
End Function

Open in new window

Rog DSQL Developer / Web Development / Business Analysis

Author

Commented:
Thanks to all that participated as this information surely can be used by future exchange users.
Distinguished Expert 2017

Commented:
Only you get to assign points.  You don't delegate the task.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial