Generate Insert Statement from MS SQL Table

This blog article shows you a code snippet to generate INSERT statement based on the data in a SQL Server table. The code snippet is as follows.

static void ExtractDataToInsertStatements(string connectionString, string tableName, string outputFilePath)
{
     using (SqlConnection connection = new SqlConnection(connectionString))
     {
         connection.Open();
         string selectQuery = $”SELECT * FROM {tableName}”;
         using (SqlCommand command = new SqlCommand(selectQuery, connection))
         {
             using (SqlDataReader reader = command.ExecuteReader())
             {
                 using (StreamWriter writer = new StreamWriter(outputFilePath))
                 {
                     while (reader.Read())
                     {
                         string insertStatement = $”INSERT INTO {tableName} VALUES (“;
                         for (int i = 0; i < reader.FieldCount; i++)
                         {
                             object value = reader.GetValue(i);
                             if (value == DBNull.Value)
                             {
                                 insertStatement += “NULL”;
                             }
                             else if (value is string)
                             {
                                 insertStatement += $”‘{value}'”;
                             }
                             else if (value is DateTime)
                             {
                                 insertStatement += $”‘{((DateTime)value).ToString(“yyyy-MM-dd HH:mm:ss”)}'”;
                             }
                             else
                             {
                                 insertStatement += value;
                             }
                             if (i < reader.FieldCount – 1)
                             {
                                 insertStatement += “, “;
                             }
                         }
                         insertStatement += “);”;
                         writer.WriteLine(insertStatement);
                     }
                 }
             }
         }
     }
}

You need to touch it up sometimes because of the special characters.

Also: Insert Data to SQL Server through Swagger from Web Api C#

Source code download: https://github.com/chanmmn/database/tree/main/2024/ConAppScriptData?WT.mc_id=DP-MVP-36769

Reference: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection?view=netframework-4.8.1?WT.mc_id=DP-MVP-36769

https://www.w3schools.com/sql/sql_insert.asp?WT.mc_id=DP-MVP-36769