Query help

Question

Query help

jdc

Old Hand

Points: 367
More actions
June 18, 2019 at 5:48 pm

#3654893

I have to insert data into archive table called Archive_table from Table A based on a date column in Table C.
Table A is parent and it has child table, Table B and id1 column in Table A is foreign key in Table B.
Table B has another parent table, Table C where ID2 is foreign key in Table B from Table C.
Table C has date column Businessday, which I need to use for archiving 90 day old data from Table A and insert into Archive_table.
Please advise how I can achieve this.

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

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

rVadim Hall of Fame Points: 3969 More actions · Answer 1

Something like this:

INSERT INTO Archive_table (<column_list>)
  SELECT (<column_list>)
  FROM TableA AS A
    INNER JOIN TableB AS B
      ON A.ID1 = B.ID1
    INNER JOIN TableC AS C
      ON B.ID2 = C.ID2
  WHERE C.Businessdate < DATEADD(DAY, -90, CURRENT_TIMESTAMP)

If you provide DDL (CREATE TABLE) and sample data more people will likely be willing to help.

--Vadim R.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

rVadim wrote:

Something like this:

INSERT INTO Archive_table (<column_list>)
  SELECT (<column_list>)
  FROM TableA AS A
    INNER JOIN TableB AS B
      ON A.ID1 = B.ID1
    INNER JOIN TableC AS C
      ON B.ID2 = C.ID2
  WHERE C.Businessdate < DATEADD(DAY, -90, CURRENT_TIMESTAMP)

If you provide DDL (CREATE TABLE) and sample data more people will likely be willing to help.

If you want to run this regularly & avoid inserting the same row more than once, it is likely that this code will need to be refined.

I second the comment about providing DDL & sample data.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

rVadim Hall of Fame Points: 3969 More actions · Answer 3

Phil Parkin wrote:

If you want to run this regularly & avoid inserting the same row more than once, it is likely that this code will need to be refined.

Yes, of course, it's just a basic template. Thank you, Phil.

--Vadim R.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Your original post clearly defined what you wanted to do. It also acts as pseudo-code for what needs to be written. When you run into something like this, at least try the very steps you have written in the form of a SELECT.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jdc Old Hand Points: 367 More actions · Answer 5

jdc

Old Hand

Points: 367

June 20, 2019 at 5:43 pm

#3655869

Thanks everyone for your valuable inputs.