SQL Insert Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Insert Problem

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-12-07 : 09:34:41

I have two tables (A and B )that have the same number of items.

A new item is inserted into table A. Is there a SQL statement that can detect that there is a new record and then insert it into table B?

If so what would that look like? Thanks in advance.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-07 : 09:45:07

This can be handled using an insert trigger. Here is an example of 2 tables ( A and B ) and a trigger TR_InsertAToB. When an insert occurs on A the values are automatically inserted into Table B:

create table A ( value char(20) )
create table B ( value char(20) )
GO

CREATE TRIGGER TR_InsertAToB
ON	A
FOR INSERT

AS

insert into b (value)
select value
from inserted
GO


insert into A select 'COPY'
union all select 'INTO'
union all select 'TABLE'
union all select 'B'
union all select 'USING'
union all select 'TR_InsertAToB'

select * from A
select * from B

drop table A
drop table B

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-12-07 : 11:16:29

Thanks for the idea! However, I don't think I can impliment it. I'm working with Visual FoxPro free tables instead of SQL tables. I can't use triggers unless I move the free tables to a database container. I'm not sure I can do this because the commercial software package that uses these tables are used by needs to be free tables.

Previously I was looking for some sort of statement like:

I was looking for a INSERT INTO TABLE_B(ID, UNITS) SELECT ID, UNITS FROM TABLE_A

However I was stuck on how to insert just the new records from TABLE_A. Any further ideas would be greatly appreciate.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-12-07 : 14:02:18

You mean something like this?

INSERT INTO Table_B (ID, Units) SELECT ID, Units FROM Table_A WHERE ID NOT IN (SELECT ID FROM Table_B)

This query should insert all rows into Table_A, which exist in Table_B but not in Table_A. However, it does not answer your original question (if I understood it right), that is: it cannot actually detect a new row has been inserted.

Owais

We make a living out of what we get, but we make a life out of what we give.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources