SQL join and then insert in 3rd table issue

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

SQL join and then insert in 3rd table issue

Author

Topic

Johnnybax1
Starting Member

5 Posts

Posted - 2012-03-04 : 00:34:13

Gurus Please help....
Need to write a nightly task
Join Table A and Table B and insert in Table C (if the record doesn't exist in Table C already)
Select Top 5 quantity from A and B join only and insert in table C

Table A
--------------------
UniqueId
SummaryId
ParentId
Quantity
Orders
--------------------------------------------
Table B

SummaryId
ItemId
Quantity
Orders
-----------------------
Table C
--------------------
UniqueId
ParentId
ItemId
SortOrder

you have no idea how thankful i will be if someone can solve this ?
this tables have close to 100K records.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-04 : 08:22:11

The description you have posted raises a lot of questions that need to be answered - In the query below, I have made many assumptions and indicated those assumptions:

INSERT INTO TableC
(UniqueId, ParentId,ItemId, SortOrder)
SELECT DISTINCT TOP (5) -- do you want distinct on all, or only on some columns?
	a.UniqueId,
	a.ParentId,
	b.ItemId,
	0 AS SortOrder -- What is the criterion for deciding the sort order?
FROM
	TableA a
	INNER JOIN B ON -- you may need LEFT or OUTER JOIN depending on your requirements
		a.SummaryId = b.SummaryId -- do you also need to join on Quantity?
WHERE
	NOT EXISTS
	(
		SELECT * FROM TableC c 
		WHERE c.UniqueId = a.UniqueId
			AND b.ItemId = c.ItemId -- do you need this join also?
	)
--ORDER BY  -- How do you decide the TOP 5? what should be the sorting order?
--	a.ParentId

Johnnybax1
Starting Member

5 Posts

Posted - 2012-03-04 : 10:06:15

Thanks

Any ideas for improving the performance of this query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-04 : 10:39:28

quote:
Originally posted by Johnnybax1

Thanks

Any ideas for improving the performance of this query.

whats the issue with current query? can you check the execution plan and see the costly steps

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Johnnybax1
Starting Member

5 Posts

Posted - 2012-03-06 : 20:18:17

Insert into table C works fine once but after it complains
Violation of PRIMARY KEY constraint--- primary doesnt get auto incremented? for second round of inserts

Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-07 : 19:04:45

is the column which is declared as Primary key an identity column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources