Insert new records b/c new foreign keys

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

General SQL Server Forums

New to SQL Server Programming

Insert new records b/c new foreign keys

Author

Topic

tiss0183
Starting Member

18 Posts

Posted - 2008-02-12 : 13:02:18

I have a Brokers table and Trans (transactions) table. When I insert new transactions, I run a stored procedure or trigger to update the Brokers table since a transaction may have a new Broker. Is there an simpler way to do this than my query below?

INSERT INTO Brokers (Brokers.broker_id)
SELECT X.broker_id
FROM (SELECT DISTINCT broker_id FROM Trans) As X
WHERE NOT EXISTS (SELECT Brokers.broker_id FROM Brokers WHERE Brokers.broker_id = X.broker_id)

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-12 : 13:06:44

create view broker as select distinct broker_id from Trans

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

tiss0183
Starting Member

18 Posts

Posted - 2008-02-12 : 16:09:05

That wouldn't replace the query though. Why would I need to create a View? I have to create quite a few similar queries and was hoping there was a shorter query.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-12 : 16:46:08

If you do this from a trigger then use the virtual table: Inserted rather than Trans.
If you do it from a scheduled job then add a where clause to only look at recent transactions so you don't re-query the entire trans table every time you run it.


insert	brokers (broker_id)
select	x.broker_id
from	Trans x
left join borkers b on b.broker_id = x.broker_id
where	b.broker_id is null
group by x.broker_id

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-12 : 18:23:49

Why not do the insert before adding into the Trans table? You really have to do it that way if the Trans table has a FK reference to the Brokers table.


insert into Brokers (broker_id)
select
	@broker_id
where
	not exists
	(select * from Brokers a where a.broker_id = @broker_id)

CODO ERGO SUM

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-13 : 08:12:50

quote:
Originally posted by tiss0183

That wouldn't replace the query though. Why would I need to create a View? I have to create quite a few similar queries and was hoping there was a shorter query.

No it will not replace the query, but if all you are doing is creating a distinct list then this is a lot easier. Now if you want a constraint on this table then insert into broker first

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

tiss0183
Starting Member

18 Posts

Posted - 2008-02-15 : 17:51:35

Two questions:
1) So you guys are suggesting it is better database practice to enter the primary keys first (brokers) and then enter the transactions in order to have a foreign key. I assume the way to do this is by inserting all new transactions into a Temporary table, inserting new brokers into Brokers, and then transfering the data from the Temporary table to the actual Trans table? However, this will take a long time (double). Is it common practice?

2) How does the query below work? Where is @broker_id coming from? How can there be no FROM clause?

insert into Brokers (broker_id)
select
@broker_id
where
not exists
(select * from Brokers a where a.broker_id = @broker_id)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-15 : 18:31:26

Is this a transactional system or a warehouse? Where does the Broker_ID get generated at/from? AS MVJ, mentioend it seems odd that you would not have a FK from your Transaction to the Broker. ANd it seems even more odd that you would have transactions froma broker that doesn;t even exist yet, if you are dealing with a transactioanl system. But, I suspect something else is going on.

This might help insert the values you want (untested):

INSERT 
	Brokers 
	(
		Broker_ID
	)
SELECT
	DISTINCT Broker_ID
FROM 
	Transactions
LEFT OUTER JOIN
	Brokers
	ON Transactions.Broker_ID = Brokers.Broker_ID
WHERE
	Brokers.Broker_ID IS NULL

-- OR

INSERT 
	Brokers 
	(
		Broker_ID
	)
SELECT
	Broker_ID
FROM
	Transactions AS T
WHERE
	NOT EXISTS (SELECT * FROM Brokers AS B WHERE B.Broker_ID = T.Broker_ID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources