I'm creating a script to automaticly copy from multiple access databases into sql server databases.

My script currently creates a new sql database and creates all of the tables. It then trys to insert the data from an access database (using linked servers) table into the newly created sql database table.

The problem is not all of the databases have all of the tables. In these cases it is ok for the sql server table to remain empty. However, the insert throws an error because the table does not exist in the linked server.

Is there any way to tell sql server to ignore the table not found error so that it will continue on to the next insert statment?


Does TRY/CATCH properly handle this?

    INSERT dbo.localtable SELECT ... FROM linkedserver...table;
    PRINT 'this table was skipped';
  • This would need to be done around each individual insert. correct? I'm currently running 180 seperate inserts inside the begin try. So a single insert error cancels for the group.
    – Lumpy
    Jun 1 '12 at 16:21
  • Have you put GO in between each insert? Jun 1 '12 at 16:27
  • No each insert is being set as a string and then executed via sp_executesql as I'm dynamicly setting the database it needs to go to. The idea is to copy some 2800 access databases into 2800 sql databases.
    – Lumpy
    Jun 1 '12 at 16:32
  • @Lumpy if you want proper help and not make us guess, please post the code you're using. Jun 1 '12 at 16:35
  • I tried. It's 3200 lines of code. over 208,000 characters and this has a 30000 character limit. I think I'm going to go with your suggestion and put begin trys around each of the inserts. It should take care of the problem.
    – Lumpy
    Jun 1 '12 at 16:46

How about an If Exists on the table such as "If exists(select 1 from sys.talbes where name = tableName)" for each insert. While try..catch catches all errors if exists is only a single line per statement.

  • The source table (that might not exist) is in Access, so sys.tables doesn't help. He would need to check the Access database each time - MSysObjects I presume, but that's going to be about the same complexity (and slightly less efficient) than TRY/CATCH. Jun 1 '12 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.