|
Dee73
Starting Member
USA
1 Posts |
 Posted - 02/18/2013 : 13:05:27
|
I need to query database using stored proc input parameters from excel sheet.And output of the query stored proc into excel sheet.
This is going to be one time process against production database.I need to do this so that there is not a big impact on the performance.
My Approach is :
1. Import xls into a staging table
2. Have a cursor to process one-row-at a time.
3. Write ouput to other staging table.
4. O/p staging table to xls.
5. Drop both staging table.
Here is what I have worked till now...
CREATE PROCEDURE CCDeclineMessage
DECLARE @keyval VARCHAR (16)
@err int
Begin
AS
CREATE dbo.Transaction_Staging
(
Transaction_id_staging VARCHAR(16),
);
Create dbo.Transaction_message_stage
(
Card_Acceptor_Identification VARCHAR(16),
Host_Response_Code (VARCHAR(8),NULLL)
Host_Response_String (VARCHAR(16),NULLL)
);
BULK
INSERT Transaction_Stage
FROM 'c:\CCDeclineID.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
DECLARE cur CURSOR STATIC LOCAL FOR
SELECT Transaction_id_staging FROM Transaction_Stage
ORDER BY Transaction_id_staging
BEGIN TRANSACTION
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @keyval
IF @@fetch_status <> 0
BREAK
insert into Transation_message_stage
select Acceptor_Identification,Host_Response_Code, Host_Response_String
from finanial_message where Host_Response_Code not in ('00','000') and Acceptor_Identification = @keyval
SELECT @err = @@error
IF @err <> 0
BREAK
END
DEALLOCATE cur
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\CCDeclineResponse.xls;',
'SELECT * FROM [Sheet1$]')
select * from Transaction_message_staging
Drop dbo.Transaction_Stage
drop dbo.Transaction_message_stage
end
I am not sure if the stored procedure is correct.Can we create and drop a staging table with in the Stored proc?
If I am running the stored procedure from my desktop(query anlalyzer ) or Appliations server( using management Studio)-- the xls output will be created on Database server or to my desktop/Application Server?
Any help is appreciated.
Thanks
Dee
|
|
Working with CSV file and staging table
