Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Dee73
Starting Member
1 Post |
Posted - 2013-02-18 : 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 CCDeclineMessageDECLARE @keyval VARCHAR (16) @err intBeginASCREATE 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' )GODECLARE cur CURSOR STATIC LOCAL FOR SELECT Transaction_id_staging FROM Transaction_Stage ORDER BY Transaction_id_stagingBEGIN TRANSACTIONOPEN curWHILE 1 = 1BEGIN FETCH cur INTO @keyval IF @@fetch_status <> 0 BREAKinsert into Transation_message_stageselect Acceptor_Identification,Host_Response_Code, Host_Response_Stringfrom finanial_message where Host_Response_Code not in ('00','000') and Acceptor_Identification = @keyval SELECT @err = @@error IF @err <> 0 BREAKENDDEALLOCATE curINSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\CCDeclineResponse.xls;','SELECT * FROM [Sheet1$]')select * from Transaction_message_stagingDrop dbo.Transaction_Stagedrop dbo.Transaction_message_stageendI 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.ThanksDee |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-18 : 13:31:35
|
you can create and drop staging table within procedure. No issues with thatThe file will be created in the machine where query is run in the path specified.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|