X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Insert Query Desire, Any Versions    
post May 8 2019, 05:48 PM

Posts: 791
Joined: 7-May 11

I have a weather database that I have created. I import my data from a flat file into a temp table "WeatherData_Temp" I then want to insert that data into "WeatherData". As you can see I am importing every second (which I will condense down later) but there are a number of rows where the data is identical. Is there any way to write a query that would not insert a row if it matched (with the exception of the timestamp) the preceding row?

Attached File  Untitled_picture.png ( 172.3K )Number of downloads: 10
Go to the top of the page
post May 8 2019, 06:21 PM

Posts: 1,615
Joined: 25-January 16
From: The Great Land

They aren't identical records because the timestamp is different.

I don't think a query alone can accomplish, at least not simply, because the data in 7 fields has to checked. For what period would you want to compare the 7 fields and discard 'dups' - every minute, hour, day?

Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
post May 8 2019, 06:55 PM

Posts: 3,850
Joined: 27-February 09

If you can return a "bin value" for a series of times, then you can use ROW_NUMBER() with an OVER BY clause to identify which sequential records could be considered duplicates, and then you could just filter those out.

Any chance you could post a CREATE TABLE and an INSERT script to populate the table with some sample data? And how much of a window of time would you consider to be in the same "bin" (well, since you're grouping times together).
This post has been edited by MadPiet: May 8 2019, 06:57 PM
Go to the top of the page
post May 9 2019, 11:05 AM

Posts: 791
Joined: 7-May 11

Thanks for taking the time to reply.

Having given this some more thought, and not having a desire to store a line of data for each second. I will probably format the timestamp to date, hours and minutes (dropping the seconds) and generate the average of each value for that minute.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    7th August 2020 - 10:37 PM