Advertisements

Archive

Posts Tagged ‘SQL’

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

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

Advertisements
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

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 🙂