How to implement SQL Bulk Copy in SQL Server

SqlBulkCopy for insert multiple rows of data into table in SQL Server

Sometimes we need to insert bulk amount of data into a table in SQL. We can insert bulk data to SQL in different methods. Here we are going to demonstrate how to insert large amount of data using SQL Bulk Copy method.

Example of SQL BulkCopy method

Here we are going to insert multiple rows of sample data into Employee table. First of all create a table in SQL named Employee. After that  create sample data for employee table and finally this multiple rows of employee data insert into table using SQL bulk copy method.

 

Create a Table in SQL 

CREATE TABLE Employee
(
ID INT,
EmpName varchar(200),
Department varchar(200),
DOB DateTime
);

SQLMulkCopy Example in SQL Server

 

C# Code to generate sample data and insertion using SQL Bulk Copy

private void loadDataBySQLBulkCopy()
        {
            DataTable dtTblEmployee = new DataTable();
            dtTblEmployee.Columns.Add("ID", typeof(Int32));
            dtTblEmployee.Columns.Add("EmpName", typeof(String));
            dtTblEmployee.Columns.Add("Department", typeof(String));
            dtTblEmployee.Columns.Add("DOB", typeof(DateTime));

            DataRow dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 1; //Bind Data to Columns
            dtrow["EmpName"] = "Wazeem";
            dtrow["Department"] = "Admin";
            dtrow["DOB"] = "1990-10-16 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 2; //Bind Data to Columns
            dtrow["EmpName"] = "Aslam";
            dtrow["Department"] = "HR";
            dtrow["DOB"] = "1987-05-16 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 3; //Bind Data to Columns
            dtrow["EmpName"] = "John";
            dtrow["Department"] = "Finance";
            dtrow["DOB"] = "1992-12-11 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            dtrow = dtTblEmployee.NewRow(); // Create New Row
            dtrow["ID"] = 4; //Bind Data to Columns
            dtrow["EmpName"] = "Mishal";
            dtrow["Department"] = "Infra structure";
            dtrow["DOB"] = "1989-04-01 12:00:00.000";
            dtTblEmployee.Rows.Add(dtrow);

            SqlBulkCopy bulkCopy = new SqlBulkCopy(
                "server=TEST;database=TEST;uid=test;password=test",
                SqlBulkCopyOptions.TableLock);
            bulkCopy.DestinationTableName = "dbo.Employee";
            bulkCopy.WriteToServer(dtTblEmployee);

        }

About these ads

How many classes can a single .NET DLL contain?

There’s a common question in a programming interview that sounds like this:

“How many classes can a single .NET DLL contain?”

I did a little research on the web and I saw that lots of people think that the number is unlimited. Well, in my opinion, this is wrong!

Let me clarify this! Inside MSDN there’s a section on Overview on .NET Framework that contains a section on Metadata and Self-Describing Components. In this section you will find out how metadata is stored inside a Portable Executable and what is the structure of Metadata. When you are looking inside “Metadata and the PE File Structure” , you will find out that each element of your program is stored in a “Metadata table”(one for classes, one for members, a.s.o.). Each element is identified by a “Metadata token” and it’s definition is: ”Each row of each metadata table is uniquely identified in the MSIL portion of the PE file by a metadata token.Metadata tokens are conceptually similar to pointers, persisted in MSIL, that reference a particular metadata table.”(see MSDN). After this section, there is an explanation of these tokens. “A metadata token is a four-byte number. The top byte denotes the metadata table to which a particular token refers (method, type, and so on). The remaining three bytes specify the row in the metadata table that corresponds to the programming element being described.”

Let me stop here. If a class is identified in the metadata table using a metadata token and the metadata token is afour-byte number, this means that the number of classes that an assembly can contain is smaller than the biggest number on 3 bytes (the first byte is a connection to the metadata table type). The biggest number on 3 bytes is 2^24– 1 or 16777215.

Therefore, the maximum number of classes (and classes members) that a .NET assembly can contain is 16777215. Am I right? (Wait for comments!)

That’s nice, right? :)

If I’ll have the time, I’ll try to write some code that will generate an assembly with more than 16777215 classes and see if I can use it… :)

is not a valid identifier.

I wrote this select but i recive a not valid identifier error message why??!?!?!?


set @sql = ' SELECT eVisionDalsid..'+@InvolvedTable+'.HeatEditedChemistryID AS tabid, eVisionDalsid..Heats.HeatID AS hid, eVisionDalsid..'+@InvolvedTable+'.HeatID AS Expr3,
eVisionDalsid..'+@InvolvedTable+'.'+@FieldValue+' AS fieldvalue, eVisionDalsid..Heats.HeatNumber AS Expr5, eVisionDalsid..HeatOrders.OrderNumber AS Expr6
FROM eVisionDalsid..'+@InvolvedTable+' INNER JOIN
eVisionDalsid..Heats ON eVisionDalsid..'+@InvolvedTable+'.HeatID = eVisionDalsid..Heats.HeatID LEFT OUTER JOIN
eVisionDalsid..HeatOrders ON eVisionDalsid..Heats.HeatID = eVisionDalsid..HeatOrders.HeatID
WHERE (eVisionDalsid..'+@InvolvedTable+'.HeatEditedChemistryID = @IdRegister)'

exec @sql


this is the error message:

Server: Msg 203, Level 16, State 2, Line 30
The name ' SELECT eVisionDalsid..HeatEditedChemistry.HeatEditedChemistryID AS tabid, eVisionDalsid..Heats.HeatID AS hid, eVisionDalsid..HeatEditedChemistry.HeatID AS Expr3,
eVisionDalsid..HeatEdit' is not a valid identifier.

exec (@sql)

How to update top 100 rows from table in SQL

How to Update top 100 amount of data from a table in SQL

Below codes update top 100 rows. So it never meets the timeout exception and will complete execution faster than normal query
Query for update status of  top 100 data from the table My Table order by date in SQL 

WITH Q as

(


select top 100 * from My_Table order by DATE desc


)


UPDATE Q


SET Status = 0

How to delete large amount of rows from table in SQL

How to delete huge amount of data from a table in SQL

In some scenario we have to delete large amount of rows from sql table and it will going to timeout if the table has very large amount of rows (Some tables in the database has more than crore rows). In this scenario we need to delete some small amount of records and from the table and continue the process until all records in the table deleted.

Query for recursive deletion from the table in SQL 

Below codes delete 50000 rows recursively untill all records int the table deleted. So it never meets the timeout exception and will complete execution faster than normal query

WHILE exists (
SELECT * FROM myTable WHERE name like ‘%ab%’
 )
DELETE TOP (50000) scanned_cont_dtls WHERE name like ‘%ab%’;

How to complete remove deleted files from hard disk

Step:1   Press Win key + R
Step:2   Type CMD
Step:3   Type cipher/W:C\
Step:4   Press Enter

After  C then try D, E, F etc.

How to Calculate Age in Sql Server OR Find age using SQL query

Calculate age of employee using SQL Server

To calculate an age from the SQL table is little bit tricky.  We are demonstrating how to calculate the age from the date of birth fields in the SQL table. While fill the application form it’s better to fill the Date Of Birth field instead of age field. Because age field can calculate from the Date Of Birth field easily and accurately.

Find the age from the Date Of Birth using SQL Server

Here we are having a table called Employee and having coloumns ID,Name and DOB. From this table we need to find the age of each employee using SQL script.

SELECT DOB AS DateOfBirth,

GETDATE() AS CurrentDate,

DATEDIFF(YEAR,DOB,GETDATE()) -

(CASE WHEN

DATEADD(YY,DATEDIFF(YEAR,DOB,GETDATE()),DOB) > GETDATE()

THEN 1

ELSE 0 END) AS Age

FROM Employee