The data will be used by 1 or 2 persons for about a month and then a small dataset will be extracted from this and only that will be used. ...

I have a database with no compression on the tables(source).  I want to create a blank database(destination) where I can create the tables with "data_compression = Page" and then Insert...

Thanks!  I forgot about Try_cast .... Need to start using the new functions.

thanks, I will look into merge replication.

I wpuld like to get the following columns DC, prepaid, collect, WMItem, ItemDescription on the same row by Hdrid + RID. I'm not sure if this is clear or...

Point taken...please see data below.

Create Table dbo.MyTable(ID int, HdrID int, RID int, WmItem varchar(50), ItemDescription varchar(150), DC varchar(50), Prepaid varchar(50), Collect varchar(50))

Go

INSERT INTO MyTable (ID,HdrID, RID, Wmitem,ItemDescription,DC,Prepaid,Collect) VALUES...

This is what worked for me below.  There were some rows with the month names as values ie "FEBRUARY".
Try_Convert(varchar,Try_Convert(date,[date_From], 101), 103) as Date_To2

error msg:
Incorrect syntax near 'partition'

With CTE
as (
select *, row_number() over (partiton by HdrID , TabName order by HdrID) as RN
from xlshdr_EC

I want all the columns... This will only return Hdrid and tabname, right?