> -----Original Message-----
> From: Chad McCue [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, March 08, 2006 12:59 PM
> To: CF-Talk
> Subject: Using SQL Bulk Insert
>
> I am try to use a bulk insert instead of looping through a result set.
>
> <CFQUERY datasource="#request.dsource#" name="Emails">
> SELECT EmailAddress
> FROM Emx_Acct_Addresses
> WHERE ListID = '#AddressListID#'
> </CFQUERY>
>
> <CFSET EmailArray = ArrayNew(1) />
> <CFSET EmailArray = ListToArray(valueList(Emails.EmailAddress)) />
>
> <!--- Need to insert the emails into the Sending Table --->
> <CFQUERY datasource="#request.dsource#">
> BULK INSERT INTO BroadcastedEmails
> (EmailAddress)
> VALUES ()
> </CFQUERY>
>
> What would I use here for a value?
The BULK INSERT doesn't accept values like that (at least as far as I'm
aware). It takes a file and dumps it to a table - it only accepts the FROM
and WITH clauses. Here's an example from Books Online:
BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
Here's the full syntax description:
BULK INSERT [ [ 'database_name'.] [ 'owner' ].] { 'table_name' FROM
'data_file' }
[ WITH
(
[ BATCHSIZE [ = batch_size ] ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ]
[ [ , ] DATAFILETYPE [ =
{ 'char' | 'native'| 'widechar' | 'widenative' } ] ]
[ [ , ] FIELDTERMINATOR [ = 'field_terminator' ] ]
[ [ , ] FIRSTROW [ = first_row ] ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ]
[ [ , ] LASTROW [ = last_row ] ]
[ [ , ] MAXERRORS [ = max_errors ] ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH [ = rows_per_batch ] ]
[ [ , ] ROWTERMINATOR [ = 'row_terminator' ] ]
[ [ , ] TABLOCK ]
)
]
I'm using the SQL Server 2000 reference - it may be different for other DBs
or versions.
Jim Davis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234670
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
