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)
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
DECLARE cur CURSOR STATIC LOCAL FOR
SELECT Transaction_id_staging FROM Transaction_Stage
ORDER BY Transaction_id_staging
WHILE 1 = 1
FETCH cur INTO @keyval
IF @@fetch_status <> 0
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
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'SELECT * FROM [Sheet1$]')
select * from Transaction_message_staging
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.