Copy data from SQL Server table to historical table and add copy timestamp?

Question

I'm trying to work out a specific way to copy all the data from a specific table (call opportunities) and copy it to a new table with the date stamp copied to the new table, for the only purpose is to generate historical data in the database hosted in Azure Data Warehousing.

What is the best way to do this? So far I have left and created a duplicate table in the data warehouse with an extra columndatecopied

I started using the following query:

SELECT OppName, Oppvalue
INTO Hst_Opportunities
FROM dbo.opportunities

I'm not sure where to go from here!

Answer 1

SELECT INTO Azure SQL Data Warehouse. CREATE TABLE AS CTAS, Azure DW.

, CTAS, :

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;

, Azure DW . , , , SQL Server 2016 Azure SQL.

Answer 2

insert select query, , SQL Server 2008 +, Datawarehouse Azure SQL

INSERT INTO Hst_Opportunities 
   SELECT OppName, Oppvalue, DATEDIFF(SECOND,{d '1970-01-01'},current_timestamp) 
   FROM dbo.opportunities