SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Working with CSV file and staging table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Dee73
Starting Member

USA
1 Posts

Posted - 02/18/2013 :  13:05:27  Show Profile  Reply with Quote
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



visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 02/18/2013 :  13:31:35  Show Profile  Reply with Quote
you can create and drop staging table within procedure. No issues with that

The file will be created in the machine where query is run in the path specified.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000