Working with CSV file and staging table

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Working with CSV file and staging table

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 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

52326 Posts

Posted - 2013-02-18 : 13:31:35

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources