0 votes
1 view
in Azure by (17.6k points)

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!

1 Answer

0 votes
by (47.2k points)
  • 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.

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...