sqlvogel (11/25/2013)
Use ROW_NUMBER() for surrogate key assignment and order on the natural key. As long as you receive the same data set each time the surrogates will be the same.Better still, don't truncate your target table. Why would you want to do that? E.g.:
1. Populate a "landing" table with your snapshot of source data.
2. Join to the target table to identify only those rows that are newly arriving in the data set. Assign surrogates to the new rows.
3. Insert new rows to the target table.
I'm not always receiving the same data set, so using row number does not work. For the second part, sometimes we have to rebuild the table.
The question is:
Is there a way to get a single value for the four columns, aside from concatenation and not using identity?