Using csv file with sql

[Caren]

I can export my bank data to comma separated value (.csv) format. My check payment data is in SQL tables. I want to find a way to compare the .csv file with the SQL table and if the check number matches, place a C in the Cleared Check field in the SQL table. Can this be done, hopefully without running the whole thing through another database like Access? I was in hopes of simply running an SQL saved query in SQL Query Analyser. We are using Microsoft SQL 7.0.

 

[wushutwist]

Import the csv file into a temporary table and then run a stored procedure to check between the tables and empty the temp table at the end.

SQL Code will look similar to this:

CREATE PROCEDURE [dbo].[sp_mark_cleared_checks] AS
Update tblCheckPayments a
SET a.Cleared_Check = "C"
WHERE a.CheckNumber = 
     (SELECT b.CheckNumber FROM tblBankData b
      WHERE b.CheckNumber = a.CheckNumber);
DELETE FROM tblBankData;

Wushutwist

 

[Caren]

How do I import the csv file into a temporary table? Can it be done in the same stored procedure or query? I am new at this. Sorry. I also don't understand what you are doing in the first two lines, before AS. Is that punctuated right?

 

[wushutwist]

Sorry the parser obiviously messed with my open brackets, here is the code minus brackets.

CREATE PROCEDURE dbo.sp_mark_cleared_checks AS
Update tblCheckPayments a
SET a.Cleared_Check = "C"
WHERE a.CheckNumber = 
     (SELECT b.CheckNumber FROM tblBankData b
      WHERE b.CheckNumber = a.CheckNumber);
DELETE FROM tblBankData;

As far as the temp table thing. You should create the temp table before importing the file, so you should know what fields and datatypes to use afterall it is your file. Then right click on the table and import data, the wizard should take you thru the rest without much of a problem.
Oh yeah, to execute the stored procedure, go to query analyzer.
Wushutwist