Hello,

Yes, you can do that without dynamic SQL.

There are several ways, and everything (and much more) you can find at http://www.sommarskog.se/

Especially the article Dynamic Search Conditions will be of interest...

Vitali,

the solution depends on what data you can expect in the table.

Are there some rows that begin with other letters (not MC)? If yes, how should these be treated -...

You can't use CASE to determine whether the join will be LEFT JOIN or INNER JOIN if that's what you were asking.

You probably could use additional conditions in WHERE...

Gotta run now, so without explaining... I'll be back later, sorry

/*objects and test data*/

create table table1(name varchar(10), code int, last_code int)

insert into table1 values ('MC1',100,NULL)

insert into table1 values ('MC2',200,NULL)

insert into...

Fine, seems I got it right - so you already have my answer.

Did you try it? Were you successful?

If not, what was your problem?

You see, this is a very...

I'm not sure I got your question right, but I hope so... as I understand it:

You need to start from table2, and then join the table1 (the one with names)...

Hello,

we can only guess if you don't post table structure (as a CREATE TABLE statement, including CREATE for indexes), some sample data and the entire query.

The first obvious guess is...

I'm a little confused... you say "the only clustered id is the LocationNUM, a unique value". What do you mean by that? There is a clustered unique index on this...

Thanks for posting the results, Carl... very interesting. I knew that triangular joins don't perform well on large amounts of data, but anyway I was surprised about that much better...

It is nice to see examples of various features (like OVER (PARTITION BY NULL)), but wouldn't a simple MIN() with group by do the same? I may have missed something...

And what is your problem now? Does it still throw an error or just nothing gets inserted, or not the correct number of rows is inserted, or ...?

As far as...

rbarryyoung (6/7/2008)

---

The claims that correlated JOINS are faster than correlated SubSelects in SQL2005 is a myth. They do the same thing and, properly optimized, they should take the same...

TRY..CATCH has pretty complicated rules and I have to admit that I still didn't learn it properly (on the other hand, we have SQL2005 only a few weeks and I...

I created dummy tables with proper name as used in the script and just 2 columns each, and your code worked without any problems or errors. I copied the script...

If you don't know the name and structure of a table before runtime, then it seems something is not quite as it should be... I'm aware that it might be...