November 23, 2010 at 1:08 pm
I'm attempting build a prodedure that performs the below bulk insert. The catch is I'd like to pass in values from a table column containing the filepath and name. The files I want to load are generated by Unix, which is why I have to write it this way.
Any help would be greatly appreciated.
Nick
--load table
create table LoadTbls (TblLoc varchar(100), TblNm varchar(100));
insert into LoadTbls values('C:\zData\meminfo.txt','MemInfo') ;
insert into LoadTbls values('C:\zData\claim.txt','Claim') ;
--tsql
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT master.dbo.AD_Structure
FROM ''c:\zData\meminfo.txt''
WITH (
FIELDTERMINATOR = ''|'',
FIRSTROW = 1,
LASTROW = 1,
ROWTERMINATOR = '''+CHAR(10)+'''
)'
EXEC (@bulk_cmd) ;
November 23, 2010 at 1:13 pm
November 23, 2010 at 1:17 pm
Thanks Ron. The bulk insert I provided has the file path and name hardcoded. I'd like to pass in several file path and name so I don't have to repeat the code as many times as I have inserts to do (from a table where they are stored).
November 23, 2010 at 2:27 pm
1. Altered your table LoadTbls to include a unique value, in this case an identity column, so that I could pass in the specific load I required,
2. Inserted a print statement so that the resulting bulk load command could be visually verified.
3. To test I would cut and paste the printed statement into SSMS
4. Edit the PRINT statement by deleting it
5. Remove the 2 -- in front of the EXEC
6. Test, does it work?
create table #LoadTbls (TblLoc varchar(100), TblNm varchar(100),ID INT IDENTITY(1,1));
insert into #LoadTbls values('C:\zData\meminfo.txt','MemInfo') ;
insert into #LoadTbls values('C:\zData\claim.txt','Claim') ;
--SELECT * FROM #LoadTbls use for testing contents of LoadTbls
--tsql---
CREATE PROC Dbo.BulkLoad 1
@FileIWant INT
AS
DECLARE @bulk_cmd varchar(1000)
DECLARE @File VARCHAR(128)
SET @File = (SELECT TblLoc FROM #LoadTbls WHERE ID = 1)
SET @bulk_cmd = 'BULK INSERT master.dbo.AD_Structure
FROM '''+@File+'''
WITH (
FIELDTERMINATOR = ''|'',
FIRSTROW = 1,
LASTROW = 1,
ROWTERMINATOR = '''+CHAR(10)+'''
)'
--EXEC @bulk_cmd
PRINT @bulk_cmd
One other comment, are you sure, really sure you want the table receiving the loaded data in the MASTER db. If it were me, that is not where I would have ... for sure not in MASTER.
November 23, 2010 at 3:02 pm
First of all, thank you for taking the time to help me!
I built the proc to print, then executed it: exec Dbo.BulkLoad_1 '#LoadTbls.TblLoc' ;
It printed the DML to execute the meminfo insert, which ran successfully. The DML for claim was not generated.
I then followed the steps you laid out and executed the proc the same way and got the below message:
Msg 7202, Level 11, State 2, Procedure BulkLoad_1, Line 23
Could not find server 'BULK INSERT master' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
This is the proc:
CREATE PROC Dbo.BulkLoad_1
@FileIWant varchar(1000)
AS
DECLARE @bulk_cmd varchar(1000)
DECLARE @File VARCHAR(128)
SET @File = (SELECT TblLoc FROM #LoadTbls WHERE ID = 1)
SET @bulk_cmd = 'BULK INSERT master.dbo.AD_Structure
FROM '''+@File+'''
WITH (
FIELDTERMINATOR = ''|'',
FIRSTROW = 1,
LASTROW = 1,
ROWTERMINATOR = '''+CHAR(10)+'''
)'
EXEC @bulk_cmd ;
exec Dbo.BulkLoad_1 '#LoadTbls.TblLoc' ;
What am I missing?
Thank you for commenting on the table being in master. I'll move it now...
Nick
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply