Comparing dates as conditions for inserting rows in table

Question

Post reply

Comparing dates as conditions for inserting rows in table

fmeuriot

SSC Rookie

Points: 27
More actions
February 6, 2010 at 1:51 am

#216348

Hi,
I'm new to SQL and have a problem with a query that I just cant seem to be able to solve.
So, I have an Access database containing iclient number, security and date, that information has to be updated every month, the source for the update being an excel file. But I dont want to overwrite on the existing data so what i wanted to do was add the current month's data, when it's not already in there.
What I did was an insert query which inserts the data from the excel file to the data base only if the column containing the date of the data is different from what is already in the database.
Here is what I came up with and it is not workin and i dont understand why:
INSERT INTO DatabaseTable ( Client, Security, Date )
SELECT ExcelTable.Client, ExcelTable.[Security Description], ExcelTable.Date
FROM ExcelTable
WHERE (((Exists (SELECT 1 FROM DatabaseTable WHERE DatabaseTable.Date = ExcelTable.Date)=false)));
The query doesnt insert any rows and looks as though it considers the dates fron the two tables are identical even though they are not.
Can anyone help please ?
Thanks,
Fred

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 1

I would try something like this:

I usually just write a select statement that works for all cases then add the INSERT on top.

This left join syntax is common for the sort of operation you're looking to do

INSERT INTO DatabaseTable ( Client, Security, Date )

SELECT E.Client, E.[Security Description], E.Date

FROM ExcelTable E LEFT JOIN DatabaseTable D ON

E.Client = D.Client AND

E.[Security Description] = D.Security AND

E.Date = G.Date /*this might need to be a datediff(dd,E.Date,G.Date) = 0 depending on the datetime precision you're looking for*/

WHERE D.Date is NULL -- where the record doesn't exist in the database table

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

fmeuriot SSC Rookie Points: 27 More actions · Answer 2

Hi Craig,

Thanks a lot for taking the time to look into this. I tried what you suggested but it is not working, I get an error message:

"Cannot Join on Memo, OLE or Hyperlink Object 'E.Client = D.Client AND

E.[Security Description] = D.Security AND E.Date = D.Date '"

The weird thing is there are no Memo, OLE or Hyperlink Object in any of the tables. I thought this might be due to the fact that one of the tables is a linked table but I tried it on a database table and I still get the same message.

Any idea what the problem might be?

Thanks,

Fred.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

fmeuriot (2/6/2010)
Hi,
I'm new to SQL and have a problem with a query that I just cant seem to be able to solve.
So, I have an Access database containing iclient number, security and date, that information has to be updated every month, the source for the update being an excel file. But I dont want to overwrite on the existing data so what i wanted to do was add the current month's data, when it's not already in there.
What I did was an insert query which inserts the data from the excel file to the data base only if the column containing the date of the data is different from what is already in the database.
Here is what I came up with and it is not workin and i dont understand why:
INSERT INTO DatabaseTable ( Client, Security, Date )
SELECT ExcelTable.Client, ExcelTable.[Security Description], ExcelTable.Date
FROM ExcelTable
WHERE (((Exists (SELECT 1 FROM DatabaseTable WHERE DatabaseTable.Date = ExcelTable.Date)=false)));
The query doesnt insert any rows and looks as though it considers the dates fron the two tables are identical even though they are not.
Can anyone help please ?
Thanks,
Fred

Hi Fred

Forget about the INSERT part of the statement until you are satisfied that the SELECT part is doing what you want. Until then it's unnecessary baggage.

The error message you are getting is unfamiliar and you aren't getting the error message I'd expect - are you really using SQL Server to run this query?

If you are using Access as you suggest, then you might be better off posting in an Access forum.

In any case, I'd recommend a staging table to import the Excel data into: a staging table has the

benefit that you know exactly what the structure is but at the same time you can fill rows with unknown buckets of data from the source which can subsequently be processed into something sensible for the target table to accept.

Hope this helps

Cheers

ChrisM@home

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

fmeuriot SSC Rookie Points: 27 More actions · Answer 4

Thanks for all the help, will look into it

Have a good day!