Trigger two tables from two different databases to insert data (SQL Server)

Question

What the title says, I want to insert data from a database to another database. The first database "DB1" is the source and the second database "DB2" is the target.

The relevant tables are defined as follows:

DB1 => tbl_Target

==================================
Id | Date | Name | Age | Num_Aucts
==================================

DB2 => tbl_Source

======================
Name | Age | Num_Aucts
======================

Well, tbl_Source contains 40 rows of data. I need to transfer these rows into tbl_Target. But how you can see tbl_Target has two additional columns Idand Date. Id will set automatically. The important column is Date. In this column I want to set the currently date. In this case from today. How can I define this in a trigger frunction in SQL Server with T-SQL?

I have begun in this direction:

USE DB1
GO

CREATE TRIGGER trg_Insert_tblSource ON tbl_Source
FOR INSERT AS
BEGIN
    INSERT INTO DB2.dbo.tbl_Target ([Date], [Name], [Age], [Num_Aucts])
        SELECT ??? // How to get the current date?

Can anyone help me? Do I need a stored procedure?

you can use GetDate() to get current date of server

syed mohsin
– syed mohsin

2016-04-22 09:34:12 +00:00
Commented Apr 22, 2016 at 9:34 — syed mohsin
– syed mohsin, Commented Apr 22, 2016 at 9:34
@syedmohsin But where I have to define it?

yuro
– yuro

2016-04-22 09:34:58 +00:00
Commented Apr 22, 2016 at 9:34 — yuro
– yuro, Commented Apr 22, 2016 at 9:34

diiN__________ · Accepted Answer · 2016-04-22 11:38:04Z

2

I don't know if I fully understood your question. But if you just want to transfer the data from one table to another, you don't need a trigger. You could achieve this with a simple INSERT...SELECT:

INSERT INTO DB2.dbo.tbl_Target (
   [Date],
   [Name],
   [Age],
   [Num_Aucts]
)
SELECT GETDATE(), [Name], [Age], [Num_Aucts]
FROM yourDB.dbo.tbl_Source

If you want to use a trigger, write the following in the BEGIN block:

INSERT INTO DB2.dbo.tbl_Target (
   [Date],
   [Name],
   [Age],
   [Num_Aucts]
)
SELECT GETDATE(), [Name], [Age], [Num_Aucts]
FROM inserted

The trigger gets fired each time you insert data into tbl_Source. But the existing data doesn't get inserted into tbl_Target with your trigger.

edited Apr 22, 2016 at 11:38

answered Apr 22, 2016 at 9:34

diiN__________

7,7197 gold badges49 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

yuro Over a year ago

I know but how can I define it in my case?

yuro Over a year ago

I've tried your solution, but it doesn't insert the data in DB2 table.

yuro Over a year ago

I see I have created the trigger. Well, how can I use this trigger to execute the code?

diiN__________ Over a year ago

As soon as you insert data into tbl_Source, your trigger fires and inserts the data into tbl_Target. I think you should first learn about triggers and how they are used.

yuro Over a year ago

Ok.. in my target table is the inserted data. But it's curious. The data is more than one time inserted.

syed mohsin · Accepted Answer · 2016-04-22 09:40:47Z

0

you can use GetDate() to get current date of server

USE DB1
GO

CREATE TRIGGER trg_Insert_tblSource ON tbl_Source
FOR INSERT AS
BEGIN
    INSERT INTO DB2.dbo.tbl_Target (
       [Date],
       [Name],
       [Age],
       [Num_Aucts]
    )
    SELECT GETDATE(), [Name], [Age], [Num_Aucts]
    FROM inserted;
END
GO

answered Apr 22, 2016 at 9:40

syed mohsin

2,9382 gold badges27 silver badges48 bronze badges

1 Comment

yuro Over a year ago

I've tried also your post, but the table in DB2 doesn't filled with the data from DB1.

Collectives™ on Stack Overflow

Trigger two tables from two different databases to insert data (SQL Server)

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related