Archive

Posts Tagged ‘SQL’

Working with JSON data in SQL Server

July 26, 2020 1 comment

In this article, lets see how to work with JSON data in SQL Server using SSMS. Following features will be covered in this article.

  • Format SQL table data in JSON format (Using FOR JSON PATH clause)
  • Store JSON data in SQL table
  • Query JSON data in SQL server (Using JSON_VALUE and JSON_QUERY functions)
  • Update JSON data in SQL table (Using JSON_MODIFY function)
  • Convert JSON data in to SQL table format (Using OPENJSON function)
  • Validate JSON data format (Using ISJSON function)

Format SQL table data in JSON format:

Lets see how to format SQL query tabular results in JSON format using FOR JSON clause.

  • Create a simple ‘Employee’ table in SQL server.
  • When you query ‘Employee’ table you would get the result in tabular format.
  • Lets convert the result set in to JSON format using FOR JSON clause.
-- JSON PATH
select *
from Employee
FOR JSON PATH
  • Use WITHOUT_ARRAY_WRAPPER to get a single JSON object instead of an array. Use this option if the result of query is single object.
-- JSON PATH and WITHOUT_ARRAY_WRAPPER
select *
from Employee
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  • We can also form inline JSON object format. Lets see how to show ‘Address’ as a separate object.
-- JSON PATH, WITHOUT_ARRAY_WRAPPER and 'Address' as seperate object
select
ID,Name,
Street1 as [Address.Street1], Street2 as [Address.Street2],City as [Address.City], ZipCode as [Address.ZipCode]
from Employee
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Store JSON data in SQL table:

  • JSON is a textual format that can be used like any other string type in SQL Database. JSON data can be stored as a standard NVARCHAR.
  • In the example below SQL table has ‘JSONData’ column which is NVARCHAR(MAX).

Query JSON data in SQL server:

  • Lets query the JSON data using JSON_VALUE and JSON_QUERY.
  • JSON_VALUE:
    • JSON_VALUE function extracts a value from JSON text stored in the SQL column.
    • The extracted value can be used in any part of SQL query.
  • JSON_QUERY:
    • JSON_QUERY function extracts complex sub-object such as arrays or objects that are placed in JSON text.
    • Example ‘Tags’ column in above SQL Table.
Query records whose Color is ‘Yellow’

Update JSON data in SQL table:

  • JSON_MODIFY function can be used to update the JSON text without re-parsing the entire structure.
  • Below is the query to update the ‘Price’ of all ‘Yellow’ color products to 1000.
Update Price for records where Color is ‘Yellow’
Update Products
set JSONData = JSON_MODIFY(JSONData,'$.Price',1000)
where
JSON_VALUE(JSONData,'$.Color') = 'Yellow'
  • Post query execution, ‘Price’ updated to 1000 as shown below.

Convert JSON data in to SQL table format

  • Using OPENJSON function JSON data can be transformed in to SQL table format.
  • OPENJSON
    • Its a table-value function that parses JSON text, locates an array of JSON objects, iterates through the elements of the array, and returns one row in the output result for each element of the array.
Convert JSON data to Table format

Validate JSON data format

  • Since JSON is stored in a standard text, there are no guarantees that the values stored in text columns are properly formatted using ISJSON function.
  • ISJSON function returns the value 1 if the data properly formatted in JSON.
ISJSON(SQL_Cell) > 0

🙂

Categories: SQL Tags: ,

SQL Server- Drop If Exists (DIE) Objects

September 28, 2018 Leave a comment

Other day, I generated my Database SQL Script including script to create Tables, Procedures, Types, Views etc., using Tasks -> Generate Scripts… option which I had to deploy on my test instance.

DIE_1

‘Generate Scripts’ generate the SQL script with ‘CREATE’ statement (i.e., It will have ‘CREATE Table’, ‘CREATE PROCEDURE’ etc…)

Issue is, what if I already had Database script ran previously and want to only update the Database objects, you would get script error if you run the generated SQL Script.

Solution is, we need to check existence of the object (i.e.,Table, Procedure, View etc.) and Drop if exists the object and run the CREATE statement generated using ‘Generate Scripts…’ option.

Drop If Exists (DIE) statement in SQL Server 2016:

From SQL Server 2016 CTP3 we can use DIE  statements to check if the Object exists and Drop before run the CREATE statements.

  • Dropping ‘Procedure’ if exists:

DROP PROCEDURE IF EXISTS [Stored_Procedure_Name]

  • Dropping ‘Table’ if exists:

DROP TABLE IF EXISTS [Table_Name]

  • Dropping ‘Type’ (i.e.,User Defined Table Type/User Defined Data Types) if exists:

DIE_2

DROP TYPE IF EXISTS [Type_Name]

  • Delete Table’s COLUMN if Exists.

ALTER TABLE DROP COLUMN IF EXISTS

  • Delete Table’s CONSTRAINT if exists

ALTER TABLE DROP CONSTRAINT IF EXISTS

Below are the list of Objects that can be included in DIE statements.

DIE_3

Note:

  • If your SQL version is before 2016 below is the syntax

IF OBJECT_ID(‘Table_Name‘, ‘U’) IS NOT NULL
DROP TABLE Table_Name;

  • You can also use ‘Check for object existence‘ and ‘Script DROP and CREATE‘ options while ‘Generating Script’ which delete and recreate objects.

DIE_4.PNG

🙂

Categories: SQL Tags: ,

C# – SQL – Bulk Insert records from Data Table and CSV

March 31, 2018 1 comment

In my previous article I provided the steps to update records in single transaction using SQL “Table-Valued Parameter Types”.

In this article, lets see an easy way to bulk insert the records using SQLBulkCopy

What is SQLBulkCopy:

  • SQLBulkCopy class comes with ‘System.Data’ namespace and can be used to write/Insert data to SQL Server tables.
  • Though there are other ways to load data into a SQL Server table (i.e., INSERT statements, for example) but ‘SQLBulkCopy’ offers a significant performance advantage.

Below are the steps to use SQLBulkCopy with the combination of ‘Data Table’

Steps:

To simplify the explanation, I am taking a ‘Student’ table with 3 columns.

  • Create a ‘Student’ table.

Data Table 1

  • In C# console application, prepare a ‘Data Table’ with the structure similar to ‘Student’ SQL table.

tableStudents = new DataTable(“Student”);
tableStudents.Columns.Add(“StudentID”, typeof(string));
tableStudents.Columns.Add(“StudentName”, typeof(string));
tableStudents.Columns.Add(“City”, typeof(string));

// Add ‘Student’ data as Rows to the Data Table
DataRow rowStudent = tableStudents.NewRow();
rowStudent[“StudentID”] = “RJ001”
rowStudent[“StudentName”] = “Rajeev”;
rowStudent[“City”] = “Hyd”;

//Add the Data Row to Table
tableStudents.Rows.Add(rowStudent);

  • Instantiate and execute the ‘SQLBulkCopy’ by passing the Data Table

using (SqlBulkCopy bulkCopy = new SqlBulkCopy({SQLConnection}, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null))
{
bulkCopy.DestinationTableName = “Student“; // Your SQL Table name

//Optional mappings. Not required, if your ‘Data Table’ column names match with ‘SQL Table’ column names
bulkCopy.ColumnMappings.Add(“StudentID”, “StudentID”);
bulkCopy.ColumnMappings.Add(“StudentName”, “StudentName”);
bulkCopy.ColumnMappings.Add(“City”, “City”);
bulkCopy.WriteToServer(tableStudents);
isSuccuss = true;
}

  • Execute the code which inserts rows from ‘Data Table’ to ‘Student’ SQL table.

Now lets see how to insert the CSV data to SQL table with query.

Note: You can also do the same with SQL Import Wizard

Bulk insert records from CSV to SQL Table:

  • Assume that you have data available in a CSV file and want to insert them as records in SQL Table.

Insert 1

  • Below is the query

Bulk Insert {SQL Table Name}

from ‘CSV File Path’

with
(rowterminator=’\n’,fieldterminator=’,’)

Insert 2

Categories: SQL Tags: , ,

C# – Bulk Update Records – Pass Data Table to SQL Stored Procedure

March 30, 2018 3 comments

I was building a console application to transact with large set of data in a SQL table and got a requirement to bulk update records.

Below are the options I got.

Option 1:

  • Loop through the records in Console and make SQL update call for each record.
  • This is a tedious and non-scalable for huge volumes data, as we need to make one SQL server transaction for each record from client (i.e, Console).

Option 2:

  • Populate the records to update as rows in a ‘Data Table’ and pass it to a Stored Procedure, and have the Update statement in Stored Procedure.
  • This is optimal as the heavy  SQL updates are off loaded from Console to SQL server.

Lets see how to implement options 2.

To make the Stored Procedure accept Data Table, we need to define “Table-Valued Parameter Types” in SQL first.

What is a ‘Table-Valued Parameter Type”:

  • Table-valued parameters are based on strongly-typed table structures that are defined by using SQL CREATE TYPE statements.
  • You need create a “Table-Valued Type” (i.e.,StudentTableType from below scenario) with the structure similar to your Table (i.e., Student table from below scenario).

Steps to implement Option 2:

To simplify the explanation, I am taking a ‘Student’ table with 3 columns.

  • Create ‘Student’ Table

Data Table 1

  • Create a ‘User-Defined Table Type’ (i.e., ‘StudentTableType‘) with ‘Student’ columns.
    • Note: You don’t need to include all columns. Add columns only you need to update.

Data Table 2

  • Create a Stored Procedure with StudentTableType as Parameter

Data Table 3

  • In the Console, populate a ‘Data Table’ with ‘Student’ records as ‘Data Row’

tableStudents = new DataTable(“Student”);
tableStudents.Columns.Add(“StudentID”, typeof(string));
tableStudents.Columns.Add(“StudentName”, typeof(string));
tableStudents.Columns.Add(“City”, typeof(string));

// Add ‘Student’ data as Rows to the Data Table
DataRow rowStudent = tableStudents.NewRow();
rowStudent[“StudentID”] = “RJ001”
rowStudent[“StudentName”] = “Rajeev”;
rowStudent[“City”] = “Hyd”;

//Add the Data Row to Table

tableStudents.Rows.Add(rowStudent);

  • Execute the Stored Procedure from Console by Passing ‘DataTable’ (i.e.,tableStudents) as Parameter

using (SqlConnection connection = ConnectionManager.SQLConnection)
{
SqlCommand cmdUpdateStudents = new SqlCommand(“BulkUpdateStudents”, connection)
{
CommandType = CommandType.StoredProcedure
};

SqlParameter tvpParam = cmdUpdateStudents.Parameters.AddWithValue(“@tvpStudents”, tableStudents);
tvpParam.SqlDbType = SqlDbType.Structured;

cmdUpdateStudents.ExecuteNonQuery();
}

 

 

 

SQL query to split a string separated by Comma (or any special character)

Assume that you have a String with Comma separated value and wanted to split and get the collection in SQL.

My string looks ‘A,B,C,D, ‘ and I want output as

Value

A

B

C

D

Approach

The approach I am following here is

  • Declare a Temporary table
  • Split the string
  •  Insert the split values in to Temporary table

Query

DECLARE @MyValuevarchar(100)

DECLARE @posINT

DECLARE @lenINT

DECLARE @valuevarchar(8000)

SET @pos= 0

SET @len= 0

— Set the Comma separated value

SET @MyValue=‘A,B,C,D,’

— Declare Temporary Table to store split values

DECLARE @MyTempTbl TABLE (

[Value] [nvarchar](100)

)

WHILE CHARINDEX(‘,’,@MyValue,@pos+1)>0

BEGIN

SET @len=CHARINDEX(‘,’,@MyValue,@pos+1)@pos

SET @value=SUBSTRING(@MyValue,@pos,@len)

— Insert the splitter value in to Temporary Table

INSERT INTO @MyTempTbl Values(@value)

SET @pos=CHARINDEX(‘,’,@MyValue,@pos+@len)+1

END

— Get the resultset

SELECT * FROM @MyTempTbl

Output you get as below

String Split Result

String Split Result

Note

  • My requirement was to split the string and use in ‘IN’ condition, hence I created a Temporary table.
  • Also you can replace ‘,’ with any of the character

🙂

 

Categories: SQL Tags: ,

Auto generate new GUID for ‘uniqueidentifier’ column in SQL Table

June 30, 2014 1 comment

If you a have a column of type ‘uniqueidentifier’ in your SQL table, and you want to auto generate a new Guid every time when you insert a record, you can use the ‘Default Value or Binding’ property of ‘Column Properties’.

Set Default Value or Binding = newid()

uniqueidentifier Column

uniqueidentifier Column

🙂

Categories: SQL Tags: , ,

SQL Query to fetch top 1 record from each group

I have a SQL table ‘Market’ with 3 Groups (i.e., Fruits, Vegetable and Meat) and below is the data

SQL Table - Market

SQL Table – Market

 

My requirement is

  • From each group fetch the top 1 item by ‘Priority’ column (i.e., Priority = 1) like below
Name Group

Priority

Apple Fruits 1
Fish Meat 1
Potato Vegetable 1

The approach is to provide ‘Rank’ using ROW_NUMBER to records in each group and get the Top 1 record

Below is the Query

— Create a temporary table with ‘Rank’ column

;WITH TempTbl AS

(

SELECT *,

ROW_NUMBER() OVER (

PARTITION BY [Group]

ORDER BY [Priority] ASC

) AS Rank

FROM [dbo].[Market]

)

— Select the Rank = 1 records

SELECT *

FROM TempTbl

WHERE

Rank = 1

🙂

Categories: SQL Tags: , , ,

Case statement in Select clause

September 13, 2011 Leave a comment

Hi,

Below is a sample query that has “CASE statement”  in SELECT clause

SELECT EmpID,

CASE
WHEN Salary>=2000 THEN ‘High Salaried’
WHEN Salary<=1000 THEN ‘Medium’
ELSE ‘Below Bar’
END

‘New Column’, *

FROM

Employee

Below is the screenshot for reference

CASE in SELECT Clause

CASE in SELECT Clause

Hope it helps 🙂

How to get Object Type Codes of Entities in CRM 2011

September 8, 2011 3 comments

Hi,

In this article i have explained different ways to fetch entities “Object Type Code”

  •  Using SQL Query :-

Get Object Type codes by Sql Query

Query>

SELECT ObjectTypeCode,*

FROM

ENTITYVIEW

Using JScript  :-

  • The “ObjectTypeCode” can be extracted from Query String using JScript
  • “ObjectTypeCode” resides in “etc” query string  parameter
  • Below is the JScript statement to get “ObjectTypeCode”

var currEntityObjTypeCode= Xrm.Page.context.getQueryStringParameters().etc

Key Points

  • Type codes below 10,000 are reserved for OOB entities.
  • Custom entities have a value greater than or equal to 10,000.

Note:- Custom entity object type codes may change during import and are not guaranteed to be the same between systems.

Getting Object Type Code by ‘Entity Name’ using Jscript

Below script uses CRM inbuilt logic and return the entity type code (or) object type code for the given entity name.

function getObjectTypeCodeByName(entityName) {

try {

var lookupService = new RemoteCommand(“LookupService”, “RetrieveTypeCode”);

lookupService.SetParameter(“entityName”, entityName);

var result = lookupService.Execute();

if (result.Success && typeof result.ReturnValue == “number”) {

return result.ReturnValue;

} else {

return null;

}

} catch (e) {

alert(“Error while getting ETC by Name – ” + e.description);

}

}

Hope it helps 🙂