Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Does TRY/CATCH properly handle this?

BEGIN TRY
    INSERT dbo.localtable SELECT ... FROM linkedserver...table;
END TRY
BEGIN CATCH
    PRINT 'this table was skipped';
END CATCH
share|improve this answer
    
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? –  Aaron Bertrand 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. –  Aaron Bertrand 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.

share|improve this answer
    
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. –  Aaron Bertrand Jun 1 '12 at 18:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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