CODEWITHSUNDEEP
sqlsql-servercoldfusion
toleolu
toleolu

Reputation: 77

SQL Insert multi rows from multi list values

I am trying to insert records into a SQL 2008 R2 table where the values being inserted vary. 

<cftry>    
    <cfquery name="AddListMembers" datasource="HHSCIntra">    
       INSERT INTO tbl_MailMembers (DateEntered, EnteredBy, Method,Member,List)    
       VALUES (
          CURRENT_TIMESTAMP
          , '#session.UserID#'
          , 'A'
          , '#session.RecipientDistinguishedName#'
          , '#session.DistListDistinguishedName#'
       )
    </cfquery>
    <cfcatch type="Database">
       <cfoutput>#cfcatch.message#</cfoutput>
    </cfcatch>    
</cftry>

The values that vary are session.RecipientDistinguishedName and session.DistListDistinguishedName, all of the other values are constant. Basically one time I might be

I've tried a number of loop combinations but all I get is one record written to the table: the first member in the list and the first distribution list.

UPDATE:

OK things have changed a bit, we're only allowing users to work on one distribution list at a time. So below what I have now. (Please note I left out cfqueryparam and some other details so things would fit better in the post.)

<cfloop list="#session.RecipientDistinguishedName#" index="i" delimiters="">
   <cfquery name="AddListMembers" datasource="HHSCIntra">
      INSERT INTO tbl_MailMembers (DateEntered, EnteredBy, Method, Member, List)
      VALUES (
         CURRENT_TIMESTAMP
         , '#session.UserID#'
         , 'A'
         , '#i#'
         , '#session.DistListDistinguishedName#'
      )
   </cfquery>
</cfloop>

In the code where I pull the employee distinguished name from AD, I added this:

<cfinput type="hidden" name="frmEmpDistinguishedName"
        value="#EmployeeAdd.distinguishedName#;">

The semi colon is added to the end of the distinguished name so that I can use that as a separator when I query that table. So what ends up in the column Member is a long string containing all the distinguished name separated by a semi-colon.

One thing I have noticed is the first distinguished name in the column starts out as CN=, but all subsequent distinguished names start out ,CN=. I'm playing around with querying that table to see if that's going to be a problem. Any suggestions on better ways of doing this would be appreciated.

FINAL UPDATE:::

I removed the ; from the cfinput tag, and added the code below before the cfloop in the insert query. Now I'm getting on long string of multiple distinguished names, separated by a semi-colon in the column Member. 

<cfset session.RecipientDistinguishedName="#Replace(session.RecipientDistinguishedName,'DC=org,','DC=org;','ALL')#">

Upvotes: 0

Views: 219

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20794

With sql server you can use this construct.

<cfquery>
insert into yourTable
(field1, field2, etc)
select null, null, etc
where 1 = 2
<cfloop>
union 
select value1, value2, etc
</cfloop>
</cfquery>

I'll leave the details to you. Pay attention to Henry's comment about query parameters.

Upvotes: 1

Related Questions