I am not sure how SSIS would cope with LineNumber of type identity. Maybe someone with more experience of SSIS can comment.

If you have the data in a text file...

If you have imported the LineNumber into SQL Server then everything should be fine.

If you had to resort to creating the identity column you should, as far as possible, verify...

Unfortunately in terms of relational database theory the rows are not next to each other as rows are unordered.

If the source of the rows is something like a CSV file...

Umm....

Let me try again.

A table is an unordered set so how do you tell what are the first two rows? (Obviously not the order you have listed them in as...

• This reply was modified 5 years, 5 months ago by Ken McKelvey.
• This reply was modified 5 years, 5 months ago by Ken McKelvey.

>the 1st 2 rows have all data for column 1-6 as it has row start with 2 and 3 below each other

What do you mean by the first two rows?

How...

SELECT C.Colo_ID, C.Colo_Procdate, X.Res_Date, X.Res_value
FROM #Colo C
CROSS APPLY
(
SELECT TOP(1) R.Res_Date, R.Res_value
FROM #Res R
WHERE R.Res_id = C.Colo_ID
AND R.Res_Date <= C.Colo_Procdate
ORDER BY R.Res_date DESC
) X;

You question does not make sense.

If you would care to post a sensible question (with consumable test data!!!!!) someone might give you a sensible answer.

-- *** Consumable...

• This reply was modified 5 years, 5 months ago by Ken McKelvey.

If you have not already read it, you may find the following to be useful:

https://www.red-gate.com/library/troubleshooting-sql-server-a-guide-for-accidental-dbas

An example of a nested join is:

FROM TableA A
LEFT JOIN
(
TableB B
JOIN TableC C
ON B.Bid = C.Bid
)
ON A.Aid = B.Aid;

which could be written like:

FROM TableA...

• This reply was modified 5 years, 6 months ago by Ken McKelvey.

Brackets are generally used to make nested joins more recognizable even thought it is actually the order of the ONs that matter.

As this is not a nested join, the brackets...

If anyone else is affected by this, it still occurs with SQL2017 CU22 on a test VM.

This post does not make much sense so I presume your query actually has a WHERE clause.

If the linked server has a different collation etc then the four part naming...

Also:

1. what is the point of x7? It is not referenced.

2. if the code is part of a stored procedure all those variables in the WHERE clause could produce parameter...

ps I think this is some sort of packing or loading algorithm but the details are not clear.

The logic seems to be that, for each Noseqtrt, the rank for an address with an even count has to start with an odd number.

This sort of logic is best...

• This reply was modified 5 years, 6 months ago by Ken McKelvey.