Maybe you should post more details about how the data must be moved so we have a better idea of the task at hand.

Yup union will add a DISTINCT in the final query. union all doensn't. But I agree that splitting this data in 4 tables seems like a bad idea.

They always start small .

Wouldn't it be faster to just do a full outer join where either id is null??

Alright. Always go with sps .

No it's crystal clear... but I still think that the design is faulty at the moment...

Found the problem.. was sending a string of 1060 characters. Might want to bump it up to 8K if you don't want that problem to come up later on...

Brackets are a special character... look up quotename('some string') and see what the function returns .

That's you problem... The only solution I know to that problem is to recreate the table (without the text columns, but EVERYTHING else), reship the data to the new table...

ids, foreign keys should ALWAYS be indexed.

You have a clustered index on every table, right???

Poor bastard is the one who has to make the difference between the col with uddt and the 3/4 part naming of objects .

Select objectproperty(object_id('ObjectName'), 'IsQuotedIdentOn') as QuotedIdentifier, objectproperty(object_id('ObjectName'), 'IsAnsiNullsOn') as AnsiNulls

It's a statistic. When you do a select with a where condition with no index, sql server will sometimes (if now always), create or read statistics to speed up...

Open the profiler and check wha statement is sent on the server. That'll give you a better idea of the problem. Don't forget that you need to have...

That gave me an idea...

Select * into NewTable from Openrowset (linkedserverinfo, 'Exec procedure...')

Now I would literally call this nasty, cheating, wrong... but that would work