error trap in sql server 2008 r2

dhenderson12
dhenderson12 used Ask the Experts™
on
I am importing data from another server into an archive database.  I'm doing it the "easy" way by executing a dynamic sql statement as such:

select @qst = 'SELECT * INTO ' + @schema + '.' + @table + ' FROM AS400.TMS.' + @schema + '.' + @table
	
execute(@qst)
	
select @err = @@ERROR

Open in new window



I'm doing this in a loop to move all the tables in a particular library (schema).  Here is the problem:

When the query fails it bombs out of the batch entirely, such as when the table has no columns.  I'm trying to catch the error and then continue on.  Any ideas on how to accomplish this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
Use BEGIN/TRY/CATCH new SQL functionality:

http://technet.microsoft.com/en-us/library/ms175976.aspx
Create a table to catch the error and use this:
create table ErrTable(ID int identity, table_name varchar(100), err varchar(8000))
GO

-- LOOP here
select @qst = 'SELECT * INTO ' + @schema + '.' + @table + ' FROM AS400.TMS.' + @schema + '.' + @table


BEGIN TRY	

	execute(@qst)

END TRY
BEGIN CATCH

	INSERT INTO ErrTable(table_name, err)
	select  @schema + '.' + @table, @@ERROR

END CATCH

Open in new window

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