SELECT 'api_'+
CASE
WHEN CHARINDEX('/', REVERSE(apiaction)) > 0 THEN
REVERSE(SUBSTRING(REVERSE(apiaction), 1, CHARINDEX('/', REVERSE(apiaction)) - 1))
ELSE apiaction
END as tablename into #s
FROM XControl..ImportSqlConfig
select * from #s where tablename in(
SELECT TABLE_NAME
FROM bpm.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
and TABLE_NAME like 'api%'
)
drop table #s