Chaps, On the final piece of the stored proc puzzle now, I've got a set of queries that achieves exactly what I want them too, I'm now just looking to make that into a proc and make a couple of variables dynamic.
CREATE PROCEDURE usp_devicelog @tankid int, @filename varchar(100) AS TRUNCATE TABLE DeviceStaging BULK INSERT DeviceStaging FROM @filename WITH (FIELDTERMINATOR = ',') INSERT INTO MacAddress SELECT DISTINCT MacAddress, Port FROM DeviceStaging WHERE MacAddress NOT IN (SELECT MacAddress FROM MacAddress) INSERT INTO MacLog (MacAddress_ID, LogClass_ID, ThinkTank_ID, DateTime) SELECT MacAddress.MacAddress_ID, DeviceStaging.LogClass_ID, @tankid, DeviceStaging.DateTime FROM DeviceStaging JOIN MacAddress ON DeviceStaging.MacAddress = MacAddress.MacAddress That's the final code which should work fine, however trying to make both the filepath and the tankid as dynamic variables that I pass in is proving tricky. When I run that code in the query analyzer I get the following error. Msg 102, Level 15, State 1, Procedure usp_devicelog, Line 6 Incorrect syntax near '@filename'. Msg 319, Level 15, State 1, Procedure usp_devicelog, Line 7 Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. Which would suggest it doesn't like me using a variable for the file path. Any ideas? Thanks, Rob -----Original Message----- From: Robert Rawlins - Think Blue [mailto:[EMAIL PROTECTED] Sent: 23 April 2007 16:55 To: CF-Talk Subject: RE: Bulk Insert Thanks for that chaps, Neither seem to work and still chuck back the same error in query analyser. Thanks, Rawlins -----Original Message----- From: AJ Mercer [mailto:[EMAIL PROTECTED] Sent: 23 April 2007 16:43 To: CF-Talk Subject: Re: Bulk Insert try puttind an alias on the sub-select in the where clause eg WHERE DeviceStaging.MacAddress NOT IN ( SELECT ma2.MacAddress FROM MacAddress ma2) On 4/23/07, Robert Rawlins - Think Blue <[EMAIL PROTECTED]> wrote: > > Thanks AJ, That's all sorted now. :-D > > Next part of my query is now causing problems (again with no CF error, > Doh!) > I'm trying to move all mac address's that don't currently exists in the > MacAddress table from the DeviceStaging table. > > INSERT INTO MacAddresses (MacAddress, Port) > SELECT DeviceStaging.MacAddress, DeviceStaging.Port > FROM DeviceStaging > WHERE DeviceStaging.MacAddress NOT IN (SELECT MacAddress > FROM MacAddress) > > Any ideas why this won't run? When placing it through query analyzer it > tells me: > > Msg 208, Level 16, State 1, Line 1 > Invalid object name 'DeviceStaging'. > > But I defiantly have a table called DeviceStaging as that's where the > import > happens. > > Thanks, > > Rob ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276093 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
