Retrieve data from table based on condition

Question

Post reply

Retrieve data from table based on condition

Grass

Ten Centuries

Points: 1149
More actions
May 14, 2014 at 8:49 pm

#288773

Hi all,
I am trying to insert data from one table to another table which is easy. But i have two situations where first i have to load all the previous data from the source then once i loaded all previous data then i only want to retrieve data from previous day. this process will be run schedule every night to extract previous data data only. my question is what SQL condition should i use for both queries (one query to extract all previous data and second query will extract previous data and will run the second step on wards every night).Hope everyone understand the question. Please reply.

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

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

pietlinden SSC Guru Points: 63512 More actions · Answer 1

One thing I would do when loading the data is to timestamp the new records when inserting them.

DECLARE @TimeNow DATE = GETDATE(); -- sets the value of the variable to current date

INSERT INTO SomeTable(Col1, Col2,...TimeStamp) VALUES (val1, val2, ... @TimeNow);

Then getting yesterday's data is trivial:

SELECT ...

FROM MyTable

WHERE DateStamp = DATEADD(d,-1,GETDATE());

rhythmk SSCertifiable Points: 7271 More actions · Answer 2

Grass (5/14/2014)
Hi all,
I am trying to insert data from one table to another table which is easy. But i have two situations where first i have to load all the previous data from the source then once i loaded all previous data then i only want to retrieve data from previous day. this process will be run schedule every night to extract previous data data only. my question is what SQL condition should i use for both queries (one query to extract all previous data and second query will extract previous data and will run the second step on wards every night).Hope everyone understand the question. Please reply.

Sorry, but i did not get your question 🙁

It is confusing me.Can you please provide your requirement in bullet points with table definition.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Grass Ten Centuries Points: 1149 More actions · Answer 3

1- simple select query to extract the data from source and insert into destination table.

2- the query should run first time and load all the data from the source.

3- query run after the first time and onwards should only extract data for the same day (the query will run every night as schedule job to extract data from source for the same day only. note that the query ran first time will need to extract all the old data from the source as mentioned in first point.)

4- i think the same query can be put in to two different IF conditions based on the source table date column (note: source table date column is used to insert record based on the system date)

Please reply.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Can you please post the CREATE TABLE script for the source table, including any constraints/defaults/indexes? Cheers.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Grass Ten Centuries Points: 1149 More actions · Answer 5

here:

CREATE TABLE [dbo].[CUSTOMER](

[CUST_ID] [numeric](31, 0) NOT NULL,

[CUST_TYP_CD] [varchar](15) NOT NULL,

[CNTRY_ID] [numeric](31, 0) NULL,

[CUST_NO] [varchar](30) NULL,

[CNTIRT_CD] [varchar](15) NULL,

[CRST_CD] [varchar](15) NULL,

[CUST_SGNTR_FILE_NM] [varchar](80) NULL,

[AUDIT_USER_ID_CREA] [numeric](31, 0) NULL,

[AUDIT_USER_CREA_DTM] [datetime] NOT NULL,

[AUDIT_USER_ID_UPDT] [numeric](31, 0) NULL,

[AUDIT_USER_UPDT_DTM] [datetime] NULL,

[VOID_IND] [char](1) NOT NULL,

CONSTRAINT [XPK_CUST] PRIMARY KEY CLUSTERED

(

[CUST_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[CUSTOMER] ADD DEFAULT ('n') FOR [VOID_IND]

GO