Need help with scripting for importing exporting to CSV file using staging table

• sinha73

  Newbie

  Points: 3

  More actions

  February 18, 2013 at 11:21 am

  #274348

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

  )

  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

• Abu Dina

  SSChampion

  Points: 14155

  More actions

  February 18, 2013 at 11:45 am

  #1588935

  If this is a one off exercise then no need to create a sproc for it to be honest.

  You can use the Import/Export utility to import your ID into a staging table first.

  No need for a cursor either 😀 You can do a simple join like the below:

  insert into Transation_message_stage

  select Acceptor_Identification,Host_Response_Code, Host_Response_String

  from finanial_message as a

  inner join Transaction_Staging as b

  on a.Acceptor_Identification = b.Transaction_id_staging

  WHERE a.Host_Response_Code not in ('00','000')

  You can then export the Transation_message_stage table using the Import/Export utility again.

  ---------------------------------------------------------

  It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
  David Edwards - Media lens[/url]

  Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
  Howard Zinn

• sinha73

  Newbie

  Points: 3

  More actions

  February 18, 2013 at 12:10 pm

  #1588938

  I have never used import/export utility.Can you share steps for import export?

  To use import export utility I should have the staging table already created?

  Thanks

  Dee

• Abu Dina

  SSChampion

  Points: 14155

  More actions

  February 18, 2013 at 1:14 pm

  #1588952

  sinha73 (2/18/2013)

  ---

  I have never used import/export utility.Can you share steps for import export?

  To use import export utility I should have the staging table already created?

  Thanks

  Dee

  Sure, see below: You can set the destination towards the end of the wizard.

  To start the import wizard, right-lick the destination database - TASKS - Import Data...

  

  ---------------------------------------------------------

  It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
  David Edwards - Media lens[/url]

  Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
  Howard Zinn

• sinha73

  Newbie

  Points: 3

  More actions

  February 18, 2013 at 1:52 pm

  #1588957

  :-):-):-):-) Thanks a lot!It helped!