If you unpivot the data to a temp table

    create table #T
    (
      PK varchar(12) not null,
      UpdateDate datetime not null,
      ColumnName nvarchar(128) not null,
      Value varchar(max),
      Version int not null
    );
    

You could match the rows to find new and old value with a self join on `PK`, `ColumnName` and `Version = Version + 1`.

The not so pretty part is, of course, doing the unpivot of your 300 columns into the temp table from the two base tables.

XML to the rescue to make things less awkward.

It is possible to unpivot data with XML without having to know what actual columns there are in the table that will be unpivoted. The column names must be valid as element names in XML or it will fail.

The idea is to create one XML for each row having all the values for that row.

    select bt.PK,
           bt.UpdateDate,
           (select bt.* for xml path(''), elements xsinil, type) as X
    from dbo.bigtable as bt;

<!-- language:lang-xml -->

    <UpdateDate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2001-01-03T00:00:00</UpdateDate>
    <PK xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">PK1</PK>
    <col1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">c1_1_3</col1>
    <col2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">3</col2>
    <col3 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
    <colN xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2001-01-03T00:00:00</colN>

`elements xsinil` is there to create elements for columns with `NULL`.

The XML can then be shredded using `nodes('*')`  to get one row for each column and use `local-name(.)` to the get element name and `text()` to get the value.

      select C1.PK,
             C1.UpdateDate,
             T.X.value('local-name(.)', 'nvarchar(128)') as ColumnName,
             T.X.value('text()[1]', 'varchar(max)') as Value
      from C1
        cross apply C1.X.nodes('row/*') as T(X)

Full solution below. Note that `Version` is reversed. 0 = Last version.


    create table #X
    (
      PK varchar(12) not null,
      UpdateDate datetime not null,
      Version int not null,
      RowData xml not null
    );
    
    create table #T
    (
      PK varchar(12) not null,
      UpdateDate datetime not null,
      ColumnName nvarchar(128) not null,
      Value varchar(max),
      Version int not null
    );
    
    
    insert into #X(PK, UpdateDate, Version, RowData)
    select bt.PK,
           bt.UpdateDate,
           0,
           (select bt.* for xml path(''), elements xsinil, type)
    from dbo.bigtable as bt
    union all
    select bt.PK,
           bt.UpdateDate,
           row_number() over(partition by bt.PK order by bt.UpdateDate desc),
           (select bt.* for xml path(''), elements xsinil, type)
    from dbo.bigtable_archive as bt;
    
    with C as 
    (
      select X.PK,
             X.UpdateDate,
             X.Version,
             T.C.value('local-name(.)', 'nvarchar(128)') as ColumnName,
             T.C.value('text()[1]', 'varchar(max)') as Value
      from #X as X
        cross apply X.RowData.nodes('*') as T(C)
    )
    insert into #T (PK, UpdateDate, ColumnName, Value, Version)
    select C.PK,
           C.UpdateDate,
           C.ColumnName,
           C.Value,
           C.Version
    from C 
    where C.ColumnName not in (N'PK', N'UpdateDate');
    
    /*
    option (querytraceon 8649);
    
    The above query might need some trick to go parallel.
    For the testdata I had on my machine exection time is 16 seconds vs 2 seconds
    http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx
    http://sqlblog.com/blogs/adam_machanic/archive/2013/07/11/next-level-parallel-plan-porcing.aspx
    
    */
    
    select New.PK,
           New.UpdateDate,
           New.ColumnName,
           Old.Value as OldValue,
           New.Value as NewValue
    from #T as New
      left outer join #T as Old
        on Old.PK = New.PK and
           Old.ColumnName = New.ColumnName and
           Old.Version = New.Version + 1;