Sql query help..

[Suhaas]

2 hours ago, Spartan said:

CREATE TRIGGER name_table
ON name
AFTER UPDATE,INSERT
AS 
BEGIN
    INSERT INTO name_change_history(
        id,
        old_name,
        new_name,
        date_of_change
        )
    SELECT 
        i.id,
        d.name,
        i.name,
        GETDATE()
    FROM inserted i
    LEFT JOIN deleted d
        ON i.id = d.id
    WHERE i.name != ISNULL(d.name, -1)
END;

 

This should work

Change “UPDATE” to “DELETE” in the above statement as per OP’s requirement 

[dasari]

3 hours ago, Sarvapindi said:

Hmm..actually that table gets loaded from a file...

How you are loading the file? Any etl job?

 

one more thing are you using truncate table and load the file? If yes triggers might not work... you need to do something in etl job...

[Sarvapindi]

5 minutes ago, dasari said:

How you are loading the file? Any etl job?

 

one more thing are you using truncate table and load the file? If yes triggers might not work... you need to do something in etl job...

Yes.. thru azure df... truncate n load

[Mr Mirchi]

3 hours ago, Sarvapindi said:

Sql experts randi va..oka qn

i have a table ..

Table A...   With 1000 records with columns below

id.   Name

this table will be updated everyday...so some records will be deleted or some new records will be inserted. I want to find what are the records thats r inserted and records that r deleted everyday...and load those into another table...

how to do

very simple...go with trigger.....single trigger would do the job....i am doing in my proj

[Mr Mirchi]

3 hours ago, Sarvapindi said:

Hmm..actually that table gets loaded from a file...

file ithe enti no issues....file ayina or back code numdi ayina insert or delete table meedhega trigger should do

[dasari]

3 minutes ago, Sarvapindi said:

Yes.. thru azure df... truncate n load

R u using ssis?

i am saying this instantly...not sure will it work for your problem...

First load the new data in tempdb...

Find the difference between your actual table and tempdb table ...move the differences to deleted table...

Apply truncate 

load the tempdb table to actual table..

 

But truly one thing I recommend, find the difference between delete and truncate statement...

[Sarvapindi]

9 minutes ago, Mr Mirchi said:

file ithe enti no issues....file ayina or back code numdi ayina insert or delete table meedhega trigger should do

We r not deleting from/inserting into table kada... data coming from file no

[usetime63]

read about slowly changing dimensions, hint, use merge for insert update.

 

never use trigger .

[soodhilodaaram]

4 hours ago, Sarvapindi said:

Azure datafactory..

did you create a surrogate key, you need to have a way to identify if a record is insert, delete or update

best way is to output the file as parquet first which contains all the records, load from parquet to the actual tables, based on what you want to load into which tables

this is a 2 step process

[soodhilodaaram]

7 minutes ago, usetime63 said:

read about slowly changing dimensions, hint, use merge for insert update.

 

never use trigger .

assuming him using azure data warehouse, inserts are pretty easy using polybase, updates are delete and inserts for better performance considerations

I = Inserts 

U - Delete old rec and insert new records

D - Delete 

[soodhilodaaram]

41 minutes ago, Sarvapindi said:

We r not deleting from/inserting into table kada... data coming from file no

this is about destination not source gootley

[soodhilodaaram]

9 minutes ago, usetime63 said:

read about slowly changing dimensions, hint, use merge for insert update.

 

never use trigger .

he can use trigger when using rubber 

[jajjanaka_jandri]

5 hours ago, Sarvapindi said:

Sql experts randi va..oka qn

i have a table ..

Table A...   With 1000 records with columns below

id.   Name

this table will be updated everyday...so some records will be deleted or some new records will be inserted. I want to find what are the records thats r inserted and records that r deleted everyday...and load those into another table...

how to do

I think these two queries will suffice. Try and let me know.

 

Insert into NewRecordsTable select * from 
((SELECT id,name FROM TableA
MINUS
SELECT id,name FROM TableA_snapshot) union
(SELECT id,name FROM TableA
MINUS
SELECT id,name FROM TableA_snapshot));

insert overwrite TableA_snapshot select * from TableA;
        

[Quickgun_murugan]

6 hours ago, AndhraneedSCS said:

It doesn't matter how the table gets populated. 

 

Trigger will fire up on insert/delete/update on your table,. All your trigger will do is populate this data into another table (call it dummy) and identify if it is an insert/update/delete and continue the operation on the main table.

 

Your dummy table . will have all the information you may need and you just have to query it to see what inserts/updates/deletes ran on that table.

 

if this is a mission-critical table that has 100s of transactions per second through out the day, this approach can cause performance bottleneck. Otherwise, it will work just fine.

 

 

Triggers lo Magic tables access cheyyochu to track inserted and deleted records... Use them 

[reality]

Add load_indicator and Load_date columns to that table. Load indicator need to be populated with I, U, D...