Inserting multiple records using one INSERT INTO statement and SQL Server

As seen on Adam Presley's blog entry it is possible to insert more than one record using only one call to SQL server.

  1. <cfquery name="test" datasource="#this.dsn#" result="testResult">
  2.   INSERT INTO contacts (
  3.               firstname
  4.                ,lastname
  5.    )
  6.        SELECT <cfqueryparam cfsqltype="cf_sql_varchar" value="Marko">
  7.                ,<cfqueryparam cfsqltype="cf_sql_varchar" value="Simic">
  8.  
  9.        UNION ALL
  10.  
  11.      SELECT <cfqueryparam cfsqltype="cf_sql_varchar" value="Boris">
  12.                , <cfqueryparam cfsqltype="cf_sql_varchar" value="Huskic">
  13. </cfquery>
  14.  
  15.  
  16. <cfdump var="#testResult#">
  17. <cfdump var="#testResult.IDENTITYCOL#">

Reseeding SQL Server-s identity column

This post is for my personal reference since I need this once in a while and I always forget how it's done. This is usually needed when you want to "reset" some table and you can't truncate it because of foreign key constraints.

  1. DBCC CHECKIDENT (myTable, reseed, 0)
  2.  
  3. /* instead of 0, you can put other number */

Powered by Drupal, an open source content management system