Hello Chaps,
Looking for a little help with writing a stored procedure, It's nothing
OVERLY complex, but it's a little beyond me at the moment. Basically the
proc will perform a bulk insert from a text file which is simple enough, but
I need it to split that up into two tables. Here is a quick example of my
text file.
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:00:00, 1
GF:34:00:3F:FD, 6, 2, 2007-01-01 13:01:00, 1
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:04:00, 1
GF:34:00:3F:FD, 6, 4, 2007-01-01 13:08:00, 1
Now firstly is take all the entries from the first and second columns and
places them into a table called MacAddress, and the database will assign
them all with a primary key as the tables identity.
I then want to take all the other entries and place them into a table called
MacLog where each log enty is listed, but with a foreign key which builds a
link to the corresponding MacAddress entry. So I've managed to cobble
together a couple of ideas from other source on how to do this.
create procedure usp_Something AS
BULK INSERT YourStagingTable
FROM 'c:\YourInputFile.txt'
INSERT INTO TableWithMacAddresses
SELECT MacAddress, Port
FROM YourStagingTable
WHERE MaxAddress NOT IN (SELECT MacAddress FROM TableWithMacAddresses)
INSERT INTO TableWithLoggingData
SELECT {Your Columns}
FROM YourStagingTable
GO
This does the bulk insert form the text file into a temporary staging table
at which point it adds all the unique entries into my MacAddress table.
Its that's third query that I really need help with first. How do I get it
to do an insert into MacLog and know that it has that unique identifier to
the MacAddress table, placing the correct foreign id in there?
I'd also like to know how to make that file path for the txt file a dynamic
variable that I can pass in when I trigger the stored proc.
Thanks again guys,
Rob
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:275995
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
