Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
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!

29.3k questions

30.6k answers

501 comments

104k users

Browse Categories

...