Azure Synapse Archives - Page 2 of 2

SQL GREATEST and LEAST Functions Alternative in Synapse and TSQL

Many relational databases such as Oracle, Teradata supports GREATEST and LEAST functions. These functions are quite useful when you are comparing data from two or more columns. But, Azure Synapse data warehouse which is based on the Microsoft SQL Server TSQL does not support GREATEST and LEAST functions. You either have to use a case statement or write your own user defined functions (UDFs). In this article, we will check what are the GREATEST and LEAST Functions Alternative in Synapse and TSQL. GREATEST and LEAST Functions Alternative in Synapse and…

Comments Off

February 4, 2021

Azure Synapse

SQL GROUPING SETS Alternative in Synapse

The GROUPING SETS option in SQL gives you the ability to combine multiple GROUP BY clauses into one GROUP BY clause. By definition, a grouping set is a group of columns by which you group. Azure Synapse SQL data warehouse does not support GROUPING SETS. In this article, we will check this SQL GROUPING SETS alternative in Azure Synapse SQL data warehouse with an example. SQL GROUPING SETS Alternative in Synapse As mentioned, GROUPING SETS will combine multiple GROUP BY clauses into one GROUP BY clause. The results are the…

Comments Off

January 28, 2021

Azure Synapse

Azure Synapse Recursive Query Alternative-Example

You can use recursive query to query hierarchies of data, such as an organizational structure, bill-of-materials, and document hierarchy. Though Azure Synapse uses T-SQL, but it does not support all features that are supported in T-SQL. One of such features is Recursive CTE or VIEWS. Azure Synapse does not support either WITH RECURSIVE Clause or using the RECURSIVE Clause in a CREATE VIEW Statement. In this article, we will check Azure Synapse Recursive Query Alternative with an working example. Azure Synapse Recursive Query Alternative In the other RDBMS such as…

Comments Off

October 13, 2020

Azure Synapse

Azure Synapse Export Data using sqlcmd – Example

Exporting data to CSV or text format is a common requirement in the data warehouse. Many application accept delimited data as an input. Instead of providing access to actual tables, organizations export data in the form of CSV and provide it for analysis. In this article, we will check how to export Azure Synapse data using sqlcmd command line interface. We will also check different method that you can use sqlcmd to export data. Azure Synapse Export Data using sqlcmd The sqlcmd command line tool is one of the easiest…

Comments Off

July 21, 2020

Azure Synapse

Azure Synapse Update Join Syntax – Update using other Table

In an ETL model, we use fact tables to store data. Initially, data is loaded into stage (intermediate) tables and then finally, cleansed data is loaded to target fact tables. New records are loaded and existing records are updated, You can use MERGE statement or update table using some other table. In this article, we will check Azure synapse update join syntax with an example. Azure Synapse Update Join Many ETL applications such as loading fact tables use an update join statement where you need to update a table using…

Comments Off

July 21, 2020

Azure Synapse

Azure Synapse @@ROWCOUNT Alternative

The @@ROWCOUNT is one of the important system variable that holds the count of the last executed statement in SQL server and Azure SQL Database. This variable is often used in conditional statements such as IF statement to execute and skip certain T-SQL statements. Azure Synapse does not support @@ROWCOUNT variable. In this article, we will check Azure Synapse @@ROWCOUNT Alternative. The @@ROWCOUNT in SQL server returns the number of rows affected by the last statement. Azure Synapse @@ROWCOUNT Alternative When you are migrating existing applications such as a Teradata…

Comments Off

July 6, 2020