Try this...

The error actually  said "firehose mode"?  Wow!  So many jokes, so little brain...

We have only just gotten 2005, so I have not used CTE's yet. 

Thanks for this example!  I really want to start to using and understanding this new functionality!  (And...

I cannot follow what you believe should be your output.  Here is the original data you gave us. 

Original data:

parent     child

1             2

1             3

1             4

2             5...

Sure.  Just drop the @OutPut table, (I should have done that from the beginning):

CREATE TABLE dbo.lTable( LastName varchar(100), City varchar(100))

INSERT INTO lTable VALUES( 'Gates', 'Chicago')

INSERT INTO lTable...

michael.rosquit, I do get an error when running your second section of code:

Server: Msg 1008, Level 15, State 1, Line 29

The SELECT item identified by the ORDER BY number...

Can you explain why you need to do this dynamically?  Just because the information is from another table should not mean you need to use Dynamic SQL. 

DECLARE @COUNTS TABLE(...

I am not clear as to why if you pass in 2,7 you believe you should get a "7" in your output.  The same is true of 4,7.  There is...

I am assuming you are on Server1, (the original database) and you want to replace the data in Table1 and Table2 on Server2, (the client database which contains the copy). ...

You're right.  I cannot even force it to work:

DECLARE @Numbers TABLE( Number1 integer NOT NULL,

                                           Number2 integer NOT NULL)

INSERT INTO @Numbers VALUES (2, 3)

INSERT INTO @Numbers...

It's not ignoring it.  As in the example, (the ELSE 2), you are setting it to '2' and there is no column '2'.  Hence, it is defaulting to the first...

I did this with a Function, so it needed a physical table for lTable. 

CREATE TABLE dbo.lTable( LastName varchar(100), City varchar(100))

INSERT INTO lTable VALUES( 'Gates', 'Chicago')

INSERT INTO lTable...

Can you give me more information as how this "doesn't work"? 

DECLARE @t_Table TABLE( Value1 varchar(10) NOT NULL, 

                                        Value2 integer NOT NULL, 

                                        Active bit NOT NULL)

INSERT INTO @t_Table VALUES...

I tried to simplify this and not use a physical table. 

Can you explain what you desire from your ELSE statement in the ORDER BY clause?  This looks to work...

Does the "user" actually need to update, or can the table on the "identical database1" be Truncated and repopulated with the information? 

The reason I ask is that finding those...