Click here to Skip to main content
15,867,835 members
Articles / Programming Languages / SQL
Tip/Trick

Insert Multiples Records to SQL Server Database

Rate me:
Please Sign up or sign in to vote.
5.00/5 (11 votes)
15 Mar 2017CPOL1 min read 22.1K   586   14   3
Read text file and insert mutiples records in just one SQL Sentence

Introduction

Sometimes, we need to updates databases by sending more than just one record, so datatables will be very useful in these cases.

I will explain an example about how to insert multiple records from text file to a SQL Server Database.

Background

Datatables were born in SQL Server version 2008, so this object allows to store a great amount of records and sending to a database consuming low resources of memory, this object is suitable if you have to transfer a lot records without breaking down the server.

Using the Code

You will need to create a console application .NET app (C# or VB) to read the text file.

Create a database called NetSamples with the next fields:

Image 1

After that, you must create the object DataTable like this:

Image 2

Run this sentence in your database:

C++
CREATE TYPE dbo.tbCountry AS TABLE (
    idCountry smallint,
    name varchar(100)
);

Create a console application and later, add the datatable structure definition in your code:

C++
/// <summary>
/// This example method generates a DataTable.
/// </summary>
static DataTable GetTable()
{
     DataTable table = new DataTable();
     table.Columns.Add("idCountry", typeof(short));
     table.Columns.Add("name", typeof(string));
     return table;
}

Create a stored procedure that will receive a datatable parameter and after this, datatable will be inserted in just one SQL sentence, like this:

C++
CREATE PROCEDURE InsertCountries
@dtCountry dbo.tbCountry READONLY
AS
BEGIN
INSERT INTO Country(idCountry,[name]) SELECT idCountry,[name] FROM @dtCountry
END
GO

Define a function to read the text file and store each record inside the DataTable called table.

C++
public static void readFile() {
            try
            {
                DataTable table = new DataTable();
                table = GetTable();

                // Create an instance of StreamReader to read from a file.
                // The using statement also closes the StreamReader.
                using (StreamReader sr = new StreamReader
                     (System.Environment.CurrentDirectory + @"\Countries.txt"))
                {
                    string line;
                    int i = 1;

                    // Read and display lines from the file until 
                    // the end of the file is reached. 
                    while ((line = sr.ReadLine()) != null)
                    {
                        table.Rows.Add(i, line);
                        Console.WriteLine(line);
                        i++;
                    }
                }
                //Insert datatable to sql Server
                insert(table);
            }
            catch (Exception e)
            {
                // Let the user know what went wrong.
                Console.WriteLine("The file could not be read:");
                Console.WriteLine(e.Message);
            }
            Console.ReadKey();
        }

Define a function to insert dtData (datatable) to SQL Server Database NetSamples.

C++
static void insert(DataTable dtData) {
            SqlConnection con = new SqlConnection(@"Data Source=COBOGPGP8468\SQLSERVER;
                                Initial Catalog=NetSamples;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("InsertCountries", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@dtCountry", dtData);
            cmd.Connection = con;
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                Console.WriteLine("Records inserted successfully!");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

Run the Console Application and you will see:

Image 3

Search in your database using the SQL sentence: "Select * from Country":

Image 4

History

  • 16th March, 2017: First version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Uruguay Uruguay
I am Software Developer with a passion for technology with strong backend (PHP, MySQL, PostgreSQL and SQL Server) and frontend (HTML5, CSS3, AngularJs and KnockOutJs) experience. I combine my education with expertise in CSS3, HTML5, Javascript to build creative and effective websites and applications.

If you wish to contact me then please do so on ricardojavister@gmail.com or feel free to skype me on
ricardo-torres-torres.

Comments and Discussions

 
PraiseUseful intro to data tables Pin
Member 1236439016-Mar-17 23:09
Member 1236439016-Mar-17 23:09 
GeneralRe: Useful intro to data tables Pin
Ricardo_Torres_Torres22-Mar-17 22:42
professionalRicardo_Torres_Torres22-Mar-17 22:42 
QuestionTo those who like to take extra care, be forewarned that SQL Server Pin
John Huff15-Mar-17 11:39
John Huff15-Mar-17 11:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.