How to insert record into table other database

[solo1234]

Hello all,

We are busy with a trigger in sql server 2000. The results of a select we want to insert into a table in an other database. Can someone give us script how to do this?

In a trigger is it not allowed to use the "Use" command!!

Nice regards,
Michelle.

Example:
use xx
declare @information varchar(100)
set @information = 'ok'
insert into [other_database].tbl_1 (information) values (@information)

 

[chrissie1]

nsert into [other_database].[red]dbo[/red].tbl_1 (information) values (@information)

Christiaan Baes
Belgium

My Blog

 

[solo1234]

Hello chrissie1,

Thanks a lot!!

Michelle.

 

[SQLSister]

Michelle, since you are using a trigger, I would submit that most inserts using the values clause are a BAD idea in a trigger.

Why? Because a trigger processes data in sets. If the insert was of 20000 records, your trigger must handle all 20000 not just the last one (or is it the first one that gets populated, I forget). Trigger have two psuedo tables that are only available in the trigger called inserted and deleted. It is better to use those in a select clause instead. Sp for instance suppose you want to create a record in a child table whenever your trigger kicks off.

Chrissie, I've never used the nsert command, what does it do?



You will have a problem if multiple records are inserted if you do something like:

declare @field1 int
select @field1 = idfield from inserted

insert table2 (field1, insertdate)
values(@field1, getdate())

You would want to do something like this instead

insert table2 (field1, insertdate)
select idfield, getdate() from inserted

This would insert all 20000 new records. This is just a simplified example, but hopefully it gives you the idea. The third thign you could do is something like the top stament but inside a cursor. This would be an extremely poor idea. It would work but if someone does a large insert it would be very slow and would probably create locking issues on the database while it runs. I just worked to fix a trigger that someone used a cursor in and changed the time to run for 40000 records from 47 minutes to 32 seconds, so you can see that cursors are in genral a bad idea especially in a trigger.







"NOTHING is more important in a database than integrity." ESquared

 

[chrissie1]

Chrissie, I've never used the nsert command, what does it do?

You are just jealous I got a star and you didn't.


Christiaan Baes
Belgium

My Blog

 

[SQLSister]

Chrissie, you didn't get a star.

"NOTHING is more important in a database than integrity." ESquared

 

[chrissie1]

You can't blame a man for trying.

Christiaan Baes
Belgium

My Blog