0

I have 4 tables. Table1 ID is the primary key and Table2 ID the foreign . Table3 and Table4 are basically archives of Table1 and Table2 and have the same primary key and foreign key and columns.

Table 1:

ID | Date | Amount

Table 2:

ID | Type

I had to archive table 1 data to table 3 based on a defined value in Date which I did using this:

Declare @Date date
set @Date = '2021-11-18'
Begin transaction

INSERT INTO dbo.Table3 (ID, Date, Amount)
SELECT *
FROM dbo.Table1
WHERE Date=@Date

Executing this code resulted in adding this to Table3:

52 | 2021-11-18 | 12
53 | 2021-11-18 | 14

The question is how do I now archive table2 data to table4? I only need the data from table2 that matches with how we filtered the Date. Basically, copy all table2 data to table4 that matches the ID in table3.

notsoexperienced
  • 3
  • 2
  • 2
    This doesn't make sense. What do you mean that the `ID` in table 1 is the `PRIMARY KEY` of table 2? A `PRIMARY KEY` can only exist in the table it is defined it. What you describe is vague at best. Take the time to elaborate on your problem; probably without using meaningless names like "Table 1" and "table 3". Also include *consumable* sample data and the expected results you are after for said sample data. – Thom A Nov 25 '21 at 22:26
  • @Larnu Thank you. I agree it was vague. I updated the question. – notsoexperienced Nov 25 '21 at 22:51
  • 1
    While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Nov 25 '21 at 23:16

1 Answers1

0

What I understand from your problem statement is you need to insert in Table4 from Table 2 based on filtered data set in Table 3(which is filtered on date).

So what you can do is once data is inserted in your Table3. You can join Table 3 with Table2 based on Id's since Table 1 is archive of Table 3 and Table 2 Id is foreign key from Table 1 Id. So indirectly Table 2 Id can have same relationship with your Table 3 Id.

INSERT INTO dbo.Table4 (ID, Type)
SELECT T2.ID, T2.Type
FROM dbo.Table2 T2
Inner Join dbo.Table T3
On T2.ID= T3.ID
ishant kaushik
  • 891
  • 6
  • 18