Copy data from a SQL Server table into a historic table and add a timestamp of the time copied?

Question

I'm trying to work out a specific way to copy all data from a particular table (let's call it opportunities) and copy it into a new table, with a timestamp of the date copied into the new table, for the sole purpose of generating historic data into a database hosted in Azure Data Warehouse.

What's the best way to do this? So far I've gone and created a duplicate table in the data warehouse, with an additional column called date copied.

The query I've started using is:

SELECT OppName, Oppvalue

INTO Hst_Opportunities

FROM dbo.opportunities

 I am not really sure where to go from here!

Answer 1

• In Azure SQL Data Warehouse, SELECT INTO is not supported. we must make use of CREATE TABLE AS or CTAS syntax.

• If we want to fix the copy date, we need to assign it a variable prior to CTAS, in the following way:

DECLARE @copyDate DATETIME2 = CURRENT_TIMESTAMP

CREATE TABLE dbo.Hst_Opportunities

WITH

(

    CLUSTERED COLUMNSTORE INDEX,

    DISTRIBUTION = ROUND_ROBIN

)

AS 

SELECT OppName, Oppvalue, @copyDate AS copyDate

FROM dbo.opportunities;

• The use case of Azure Data Warehouse is millions and billions of rows with terabytes of data. It is not suitable for low volume so we need to consider a traditional SQL server 2016 or Azure SQL Database.