Tech-Discussion

         Multi Table Insert We insert data into a table, and sometimes, we must archive/back it up in another table. MS-SQL Server   We can achieve this by using magic or virtual tables present, like INSERTED,  as below: However, in  Snowflake, the insert statement has an additional clause,  INSERT ALL,  which allows you to insert into multiple tables:  For more details, you can refer to the following:  Multi_Table_Insert . Yogesh Shinde LinkedInProfile <<< Back   Next >>>        

         Set Multiple Variables Sometimes we get the values from table and assign to multiple variables. MS-SQL Server   We can achieve this as below: However, in  Snowflake,  this can be achieved in little bit different way as below:  Yogesh Shinde LinkedInProfile <<< Back   Next >>>       

          IS DISTINCT FROM In some cases, we need to get the records that are NOT mapping as below: And you are expecting the result as below: MS-SQL Server   We can achieve this by using INNER JOIN as below: However, in  Snowflake,  this can be achieved in the same way as above; however, you can use the  IS DISTINCT FROM   command as below:  For more details, you can refer to the following:  IS DISTINCT FROM . Yogesh Shinde LinkedInProfile <<< Back   Next >>>      

          SELECT records - TABLE To see the records from any table, you need to use the SELECT command. MS-SQL Server   If we want to fetch ALL records, then you can use the SELECT command as below: However, in  Snowflake,  this can be achieved in the same way as above; however, you can  use the  TABLE  command as below:  Yogesh Shinde LinkedInProfile <<< Back   Next >>>     

          ALTER Table ADD Column Business requirements change during development or even after the project goes live, and you want to add an additional column to the existing table. MS-SQL Server   If we want to add a new column to the existing table, it is always better to check whether it exists. For this, in SQL, you have to perform 2 steps as below: However, in  Snowflake,  this can be achieved in a single step.  Yogesh Shinde LinkedInProfile <<< Back   Next >>>    

          ON Clause & Cross Join We use different joins to pull the records from multiple tables. We need to use the ON clause along with JOIN. What will happen if we don't use the ON clause? MS-SQL Server   If we don't mention the ON clause, it throws an error. However, if we use the same column from the same table on both sides, it works as a cross-join.  Also, if you mention condition as 1=1 in the ON clause, it treats as CROSS JOIN. However, in  Snowflake,  both above queries will work.  Apart from this, if you don't mention the ON clause, it treats it as CROSS JOIN: Yogesh Shinde LinkedInProfile <<< Back   Next >>>   

         Derived Column Sometimes, you use two columns to derive values from them, and you need to filter the data based on this derived value. MS-SQL Server   We can achieve this by an additional level of SELECT as below: However, Snowflake allows you to use a derived column in the WHERE clause as below: Yogesh Shinde LinkedInProfile <<< Back   Next >>>   

        Data Type in Grid You regularly run a query and the result comes in the grid below. Sometimes you need to know the datatype of any column. MS-SQL Server   The grid which shows the data can't display the data type of that column. You have to go back to the table structure to see the data type of that column. However, the  Snowflake  grid shows datatype in symbolic ways as below: Yogesh Shinde LinkedInProfile <<< Back   Next >>>   

       Divide by Zero Sometimes in calculation, if zero comes at the denominator place, it throws an error like Divide by Zero.   MS-SQL Server   You can find the workaround as below : However, in  Snowflake  we can use  DIV0  as below. For more details, you can refer to the following  DIV0   and  DIV0NULL . Yogesh Shinde LinkedInProfile <<< Back   Next >>>   

       GET_DDL: View Code Sometimes we need to see the code of any object like Stored Procedure, View, or any other object.   MS-SQL Server   You can achieve this by using Object Explorer and searching for a particular object. You can achieve the same thing by using the code below: However, in  Snowflake  we can use the code as below . For more details, you can refer to the following  GET_DDL . Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

      Number Generator Sometimes you need to generate numbers in sequential orders as below:   MS-SQL Server   You can achieve this by using multiple ways, one of them is using CTE . However, in  Snowflake  we can achieve this by using a  Generator. For more details, you can refer to the following  Generator . Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

     Affected Rows - SQLROWCOUNT DML commands are part of our day-to-day life. After DML command execution if you want to get the number of records executed you can get it by row count. In this case, you are inserting 6 records in a table and you should get that count  i.e. six.         INSERT INTO Main_Table ( store_ID , province , profit )         VALUES                 ( 1 , 'Ontario' , 500 ),                 ( 2 , 'Saskatchewan' , 500 ),                 ( 3 , 'Ontario' , 450 ),                 ( 4 , 'Ontario' , 450 ),                 ( 5 , 'Saskatchewan' , 450 ),                 ( 6 , 'USA' , 650 )                   ;   MS-SQL Server ...

                  DATA TYPE CONVERSION Many times you need to convert data from one data type to another data type.  Suppose you have string ' 2025-01-01 ' and want to convert it into DATE Format. MS-SQL Server We can achieve this by using the  CAST() OR CONVERT() functions as below. In  Snowflake,  we can use a similar approach and also other functions are available.   For more details, you can refer to the following  CONVERSIONS . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

                 POSITION If you want to search for a single character or group of characters in another string. ------------------------------------+------------+   Available_String                   | Position_1 | ------------------------------------+------------+   nevermore1 , nevermore2 , nevermore3 .|   1         |   -----------------------------------+------------+ In this case, we want to search " nevermore " in the string " nevermore1, nevermore2, nevermore3 .". It should return the position as 1. MS-SQL Server We can achieve this by using the  CHARINDEX()  as below. To get the second occurrence, we need to use the third parameter of CHARINDEX() as CHARINDEX('nevermore', province,5). In  Snowflake,  we can use a similar approach, which is  REGEXP_INSTR () .  This gives you the flexibility to find a second occurre...

                RANDOM Your table has many records and if you want to sort them randomly. MS-SQL Server   We can achieve this by using the NEWID() as below. In  Snowflake,  we can use a similar approach by using  RANDOM() .  For more details, you can refer to the following  RANDOM . Yogesh Shinde LinkedInProfile <<< Back   Next >>>

                CONTAINS If your table column has many records and you want to filter the data based on particular letter(s) consisting of. Eg. it should contain " tea " Actual Data: ------------+   DRINK_NAME | ------------+   coffee     |   ice tea    |   latte      |   tea        |  [ NULL ]     | ------------+ Expected Data: ------------+   DRINK_NAME | ------------+   tea        |   ice tea    | ------------+ MS-SQL Server   We can achieve this by using the LIKE operator as below. In  Snowflake,  we can use a similar approach as above.  However, Snowflake has an additional operator  CONTAINS .  For more details, you can refer to the following  CONTAINS . Yogesh Shinde LinkedInProfile <<< Back   Next >>>