{"id":124,"date":"2020-10-31T20:23:12","date_gmt":"2020-10-31T20:23:12","guid":{"rendered":"http:\/\/naveedulhaq.com\/?p=124"},"modified":"2021-01-24T20:05:17","modified_gmt":"2021-01-24T20:05:17","slug":"bulk-insert-records-in-the-database-c-sql-server","status":"publish","type":"post","link":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/","title":{"rendered":"Bulk insert records in the database (C# &#038; SQL server)"},"content":{"rendered":"\n<p>A lot of times we come across a requirement to bulk insert data in SQL server. Now there are several ways to bulk insert data in SQL server table but in this example I&#8217;ll use SqlBulkCopy.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Why use SqlBulkCopy<\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li>SqlBulkCopy is super fast as compared to other data inserts.<\/li><li>You can specify different strategies for data insert &amp; you can use multiple instances of SqlBulkCopy at the same time to work more efficiently. <\/li><li>you can use the Table lock option in SqlBulkCopy so that while updating it lock table instead of row (which is the default). This will massive performance gain while inserting a lot of records or loading data in a heap table. (Don&#8217;t use this option with clustered indexes)<\/li><\/ul>\n\n\n\n<p>In this example i have created a data table. I have added payload in datatable and SqlBulkCopy uses Writetoserver to insert this datatable in sqlserver<\/p>\n\n\n\n<pre><code> DataTable dt = new DataTable(\"Products_Temp\");\n dt = ConvertToDataTable(list); \/\/object list is my dto<\/code><\/pre>\n\n\n\n<pre><code>using (SqlConnection conn = new SqlConnection(\"your-connection-string\"))\n            {\n                using (SqlCommand command = new SqlCommand(\"\", conn))\n                {\n                    try\n                    {\n                        conn.Open();\n\n                        \/\/Bulk insert into temp table\n                        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))\n                        {\n                            bulkcopy.BulkCopyTimeout = 660;\n                            bulkcopy.DestinationTableName = \"Products_Temp\";\n                            bulkcopy.WriteToServer(dt);\n                            bulkcopy.Close();\n                        }\n\n                    }\n                    catch (Exception ex)\n                    {\n                        Console.WriteLine(ex.ToString());\n                    }\n                    finally\n                    {\n                        conn.Close();\n                        Console.WriteLine(list.Count + \" Records updated\");\n                    }\n                }\n            }<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Conclusion <\/h3>\n\n\n\n<p>I&#8217;m using SqlBulkCoy for a long time and this class is one of my favorite classes. Although SqlBulkCopy normally uses to insert data, you can use it to do bulk updates as well. For example load data in the temp or staging table and after that run update statement to update the target table.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A lot of times we come across a requirement to bulk insert data in SQL server. Now there are several ways to bulk insert data&#8230;<\/p>\n","protected":false},"author":1,"featured_media":72,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[11,8],"class_list":["post-124","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dot-net-core","tag-dot-net-core","tag-dot-net-framework"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Bulk insert records in the database (C# &amp; SQL server) - Naveed Ul-Haq&#039;s blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Bulk insert records in the database (C# &amp; SQL server) - Naveed Ul-Haq&#039;s blog\" \/>\n<meta property=\"og:description\" content=\"A lot of times we come across a requirement to bulk insert data in SQL server. Now there are several ways to bulk insert data...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Naveed Ul-Haq&#039;s blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-10-31T20:23:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-01-24T20:05:17+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.naveedulhaq.com\/wp-content\/uploads\/2019\/04\/240px-.NET_Core_Logo.svg_.png\" \/>\n\t<meta property=\"og:image:width\" content=\"240\" \/>\n\t<meta property=\"og:image:height\" content=\"240\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Naveed Ul-Haq\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Naveed Ul-Haq\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/index.php\\\/dot-net-core\\\/bulk-insert-records-in-the-database-c-sql-server\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/index.php\\\/dot-net-core\\\/bulk-insert-records-in-the-database-c-sql-server\\\/\"},\"author\":{\"name\":\"Naveed Ul-Haq\",\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/#\\\/schema\\\/person\\\/dd6db5980b965fcae41e096d357c65c9\"},\"headline\":\"Bulk insert records in the database (C# &#038; SQL server)\",\"datePublished\":\"2020-10-31T20:23:12+00:00\",\"dateModified\":\"2021-01-24T20:05:17+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/index.php\\\/dot-net-core\\\/bulk-insert-records-in-the-database-c-sql-server\\\/\"},\"wordCount\":213,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/#\\\/schema\\\/person\\\/dd6db5980b965fcae41e096d357c65c9\"},\"image\":{\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/index.php\\\/dot-net-core\\\/bulk-insert-records-in-the-database-c-sql-server\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.naveedulhaq.com\\\/wp-content\\\/uploads\\\/2019\\\/04\\\/240px-.NET_Core_Logo.svg_.png\",\"keywords\":[\".net core\",\".net framework\"],\"articleSection\":[\".NET\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.naveedulhaq.com\\\/index.php\\\/dot-net-core\\\/bulk-insert-records-in-the-database-c-sql-server\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/index.php\\\/dot-net-core\\\/bulk-insert-records-in-the-database-c-sql-server\\\/\",\"url\":\"https:\\\/\\\/www.naveedulhaq.com\\\/index.php\\\/dot-net-core\\\/bulk-insert-records-in-the-database-c-sql-server\\\/\",\"name\":\"Bulk insert records in the database (C# & SQL server) - Naveed Ul-Haq&#039;s blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/index.php\\\/dot-net-core\\\/bulk-insert-records-in-the-database-c-sql-server\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/index.php\\\/dot-net-core\\\/bulk-insert-records-in-the-database-c-sql-server\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.naveedulhaq.com\\\/wp-content\\\/uploads\\\/2019\\\/04\\\/240px-.NET_Core_Logo.svg_.png\",\"datePublished\":\"2020-10-31T20:23:12+00:00\",\"dateModified\":\"2021-01-24T20:05:17+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/index.php\\\/dot-net-core\\\/bulk-insert-records-in-the-database-c-sql-server\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.naveedulhaq.com\\\/index.php\\\/dot-net-core\\\/bulk-insert-records-in-the-database-c-sql-server\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/index.php\\\/dot-net-core\\\/bulk-insert-records-in-the-database-c-sql-server\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.naveedulhaq.com\\\/wp-content\\\/uploads\\\/2019\\\/04\\\/240px-.NET_Core_Logo.svg_.png\",\"contentUrl\":\"https:\\\/\\\/www.naveedulhaq.com\\\/wp-content\\\/uploads\\\/2019\\\/04\\\/240px-.NET_Core_Logo.svg_.png\",\"width\":240,\"height\":240,\"caption\":\".net core\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/index.php\\\/dot-net-core\\\/bulk-insert-records-in-the-database-c-sql-server\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.naveedulhaq.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Bulk insert records in the database (C# &#038; SQL server)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/#website\",\"url\":\"https:\\\/\\\/www.naveedulhaq.com\\\/\",\"name\":\"Naveed Ul-Haq's blog\",\"description\":\"AI, Optimizely, Azure &amp; more\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/#\\\/schema\\\/person\\\/dd6db5980b965fcae41e096d357c65c9\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.naveedulhaq.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/www.naveedulhaq.com\\\/#\\\/schema\\\/person\\\/dd6db5980b965fcae41e096d357c65c9\",\"name\":\"Naveed Ul-Haq\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/362536aba6cc66917d7558cacd015a81c7cdf1a69b9a28c994764847c487b692?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/362536aba6cc66917d7558cacd015a81c7cdf1a69b9a28c994764847c487b692?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/362536aba6cc66917d7558cacd015a81c7cdf1a69b9a28c994764847c487b692?s=96&d=mm&r=g\",\"caption\":\"Naveed Ul-Haq\"},\"logo\":{\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/362536aba6cc66917d7558cacd015a81c7cdf1a69b9a28c994764847c487b692?s=96&d=mm&r=g\"},\"description\":\"I lead engineering delivery teams for digital commerce and digital experience platforms, combining hands-on architecture experience with strong delivery governance. Over 20+ years, I\u2019ve built and led cross\u2011functional teams (engineering, BA, QA) delivering modern cloud solutions on Azure, microservices, APIs and eCommerce\\\/CMS platforms. My recent focus includes AI-enabled commerce automation: product ingestion and enrichment workflows, content optimisation for SEO and shopping feeds, and agentic tooling to improve marketing and accessibility workflows. I\u2019m passionate about building high-performing teams, establishing quality\\\/release standards, and delivering measurable outcomes across performance, reliability, and speed of change.\",\"sameAs\":[\"https:\\\/\\\/www.naveedulhaq.com\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/naveedulhaq\\\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Bulk insert records in the database (C# & SQL server) - Naveed Ul-Haq&#039;s blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/","og_locale":"en_GB","og_type":"article","og_title":"Bulk insert records in the database (C# & SQL server) - Naveed Ul-Haq&#039;s blog","og_description":"A lot of times we come across a requirement to bulk insert data in SQL server. Now there are several ways to bulk insert data...","og_url":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/","og_site_name":"Naveed Ul-Haq&#039;s blog","article_published_time":"2020-10-31T20:23:12+00:00","article_modified_time":"2021-01-24T20:05:17+00:00","og_image":[{"width":240,"height":240,"url":"https:\/\/www.naveedulhaq.com\/wp-content\/uploads\/2019\/04\/240px-.NET_Core_Logo.svg_.png","type":"image\/png"}],"author":"Naveed Ul-Haq","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Naveed Ul-Haq","Estimated reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/#article","isPartOf":{"@id":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/"},"author":{"name":"Naveed Ul-Haq","@id":"https:\/\/www.naveedulhaq.com\/#\/schema\/person\/dd6db5980b965fcae41e096d357c65c9"},"headline":"Bulk insert records in the database (C# &#038; SQL server)","datePublished":"2020-10-31T20:23:12+00:00","dateModified":"2021-01-24T20:05:17+00:00","mainEntityOfPage":{"@id":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/"},"wordCount":213,"commentCount":1,"publisher":{"@id":"https:\/\/www.naveedulhaq.com\/#\/schema\/person\/dd6db5980b965fcae41e096d357c65c9"},"image":{"@id":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/www.naveedulhaq.com\/wp-content\/uploads\/2019\/04\/240px-.NET_Core_Logo.svg_.png","keywords":[".net core",".net framework"],"articleSection":[".NET"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/","url":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/","name":"Bulk insert records in the database (C# & SQL server) - Naveed Ul-Haq&#039;s blog","isPartOf":{"@id":"https:\/\/www.naveedulhaq.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/www.naveedulhaq.com\/wp-content\/uploads\/2019\/04\/240px-.NET_Core_Logo.svg_.png","datePublished":"2020-10-31T20:23:12+00:00","dateModified":"2021-01-24T20:05:17+00:00","breadcrumb":{"@id":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/#primaryimage","url":"https:\/\/www.naveedulhaq.com\/wp-content\/uploads\/2019\/04\/240px-.NET_Core_Logo.svg_.png","contentUrl":"https:\/\/www.naveedulhaq.com\/wp-content\/uploads\/2019\/04\/240px-.NET_Core_Logo.svg_.png","width":240,"height":240,"caption":".net core"},{"@type":"BreadcrumbList","@id":"https:\/\/www.naveedulhaq.com\/index.php\/dot-net-core\/bulk-insert-records-in-the-database-c-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.naveedulhaq.com\/"},{"@type":"ListItem","position":2,"name":"Bulk insert records in the database (C# &#038; SQL server)"}]},{"@type":"WebSite","@id":"https:\/\/www.naveedulhaq.com\/#website","url":"https:\/\/www.naveedulhaq.com\/","name":"Naveed Ul-Haq's blog","description":"AI, Optimizely, Azure &amp; more","publisher":{"@id":"https:\/\/www.naveedulhaq.com\/#\/schema\/person\/dd6db5980b965fcae41e096d357c65c9"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.naveedulhaq.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/www.naveedulhaq.com\/#\/schema\/person\/dd6db5980b965fcae41e096d357c65c9","name":"Naveed Ul-Haq","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/secure.gravatar.com\/avatar\/362536aba6cc66917d7558cacd015a81c7cdf1a69b9a28c994764847c487b692?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/362536aba6cc66917d7558cacd015a81c7cdf1a69b9a28c994764847c487b692?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/362536aba6cc66917d7558cacd015a81c7cdf1a69b9a28c994764847c487b692?s=96&d=mm&r=g","caption":"Naveed Ul-Haq"},"logo":{"@id":"https:\/\/secure.gravatar.com\/avatar\/362536aba6cc66917d7558cacd015a81c7cdf1a69b9a28c994764847c487b692?s=96&d=mm&r=g"},"description":"I lead engineering delivery teams for digital commerce and digital experience platforms, combining hands-on architecture experience with strong delivery governance. Over 20+ years, I\u2019ve built and led cross\u2011functional teams (engineering, BA, QA) delivering modern cloud solutions on Azure, microservices, APIs and eCommerce\/CMS platforms. My recent focus includes AI-enabled commerce automation: product ingestion and enrichment workflows, content optimisation for SEO and shopping feeds, and agentic tooling to improve marketing and accessibility workflows. I\u2019m passionate about building high-performing teams, establishing quality\/release standards, and delivering measurable outcomes across performance, reliability, and speed of change.","sameAs":["https:\/\/www.naveedulhaq.com","https:\/\/www.linkedin.com\/in\/naveedulhaq\/"]}]}},"_links":{"self":[{"href":"https:\/\/www.naveedulhaq.com\/index.php\/wp-json\/wp\/v2\/posts\/124","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.naveedulhaq.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.naveedulhaq.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.naveedulhaq.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.naveedulhaq.com\/index.php\/wp-json\/wp\/v2\/comments?post=124"}],"version-history":[{"count":0,"href":"https:\/\/www.naveedulhaq.com\/index.php\/wp-json\/wp\/v2\/posts\/124\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.naveedulhaq.com\/index.php\/wp-json\/wp\/v2\/media\/72"}],"wp:attachment":[{"href":"https:\/\/www.naveedulhaq.com\/index.php\/wp-json\/wp\/v2\/media?parent=124"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.naveedulhaq.com\/index.php\/wp-json\/wp\/v2\/categories?post=124"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.naveedulhaq.com\/index.php\/wp-json\/wp\/v2\/tags?post=124"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}