Inserting multiple records using one INSERT INTO statement and SQL Server

No votes yet

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#">

BUT, there's one down side of this approach. Since I always use *.IDENTITYCOL as return from set method I checked if I'll maybe get some kind of array of inserted records... The answer is. my friend, no. (At least for CF8)

Element IDENTITYCOL is undefined in TESTRESULT.

In SQL server 2008 you could even simplify above statement and write this:

  1. <!--- SQL Server 2008 --->
  2. <cfquery name="test" datasource="#this.dsn#" result="testResult">
  3.  INSERT INTO contacts (
  4.           firstname
  5.           ,lastname
  6.       )
  7.         VALUES
  8.    (
  9.        ( <cfqueryparam cfsqltype="cf_sql_varchar" value="Marko">
  10.         , <cfqueryparam cfsqltype="cf_sql_varchar" value="Simic">),
  11.  
  12.         ( <cfqueryparam cfsqltype="cf_sql_varchar" value="Boris">
  13.         , <cfqueryparam cfsqltype="cf_sql_varchar" value="Huskic"> 
  14.   )
  15. </cfquery>

Maybe, I could go wild and try something like this:

  1. <cfquery name="test" datasource="#this.dsn#" result="testing">
  2.  <!--- DECLARE temp table --->
  3.     DECLARE @resultSet TABLE( no INT )
  4.  
  5.  <!--- INSERT last current identity value into temp table --->
  6.     INSERT INTO @resultSet (no) VALUES (IDENT_CURRENT('contacts'));
  7.  
  8.     INSERT INTO contacts (
  9.               firstname
  10.                ,lastname
  11.    )
  12.        SELECT <cfqueryparam cfsqltype="cf_sql_varchar" value="Marko">
  13.                ,<cfqueryparam cfsqltype="cf_sql_varchar" value="Simic">
  14.  
  15.        UNION ALL
  16.  
  17.      SELECT <cfqueryparam cfsqltype="cf_sql_varchar" value="Boris">
  18.                , <cfqueryparam cfsqltype="cf_sql_varchar" value="Huskic"> ;
  19.  
  20.    <!--- INSERT last inserted identity value into temp table --->
  21.    INSERT INTO @resultSet (no) VALUES (scope_identity());
  22.  
  23.  <!--- SELECT and return values from temp table --->
  24.   SELECT * from @resultSet;
  25.  
  26. </cfquery>

But that would be too nerdy :)

Post new comment

The content of this field is kept private and will not be shown publicly. If you have a Gravatar account, used to display your avatar.
Mollom CAPTCHA (play audio CAPTCHA)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated.
Powered by Drupal, an open source content management system