Better SQL practices

I have a table that gets updated each night.  I will update or insert into the tables depending on if there is a record there or not.  All data will be overwritten with the exception of the ID field which is randomly generated and the MattersID field. On my first pass I set QMBDIT, QMCorp and QMLLC to 'N'.  Then I look in the Matters table and decide what fields will be changed to 'Y'.  The MattersScheduling.ID  is not stored anywhere else.

Here is my table: MattersScheduling

ID - Randomly generated
MattersID - the ID field of the Matters table
QMBDIT - Will be Y or N depending on what is checked in the Matters table
QMCorp - Will be Y or N depending on what is checked in the Matters table
QMLLC - Will be Y or N depending on what is checked in the Matters table

My question:

1)  Would it be better practice to set all necessary fields to 'N' and UPDATE/INSERT?
2) Delete all entries in the MattersScheduling and do an INSERT?


Thank you for your help.
huerita37Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
"Delete all entries in the MattersScheduling and do an INSERT?"
You will loose the sequence generated ID in this case and if is needed..plus it will be a lot of heavy IO potentially due to DELETE/INSERT depending on the volumes but...on the other hand whatever is in the source table at that time will go into destination and will be 100% clean.

UPDATE/INSERT (what about DELETES as you don't want to leave them out of sync -right?) can be done slightly different and here are two options:

1. Transactional Replication (push subscription) for instance so you don'e need to worry about code/data being out of sync.
2. You could have 3 triggers on parent table for INSERT, UPDATE, DELETE and populate a queue with the actual ID (PKKEY) of the row that was touched and the action like ('U','D','I' for instance) then process only this subset in a daily SQL scheduled job by parsing the queue and pushing to the destination table the "action" as it was logged in order in the queue table.


Also if there are no 'text' like columns in SQL you could use the EXCEPT and INTERSECT functions on all columns in order to "merge" two tables data into one.

HTH..
0
Scott PletcherSenior DBACommented:
I'd recommend an UPDATE for existing rows followed by an INSERT for new rows.

And definitely cluster the MattersScheduling table on the MattersID.  Btw, you should drop the random ID column unless it serves an actual purpose on this table, which so far no purpose has been shown.


UPDATE ms
SET QMBDIT = <y or n depending ...>,
    QMCorp  = <y or n depending ...>,
    QMLLC = <y or n depending ...>
FROM MattersScheduling ms
INNER JOIN Matters m ON m.MattersID = ms.MattersID

INSERT INTO MattersScheduling ( ... )
SELECT ...
FROM Matters m
WHERE NOT EXISTS(
    SELECT 1
    FROM MattersScheduling ms
    WHERE ms.MattersID = m.MattersID
    )
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
huerita37Author Commented:
I was trying to simplify my post.  I am understanding what you are saying but I am not sure how to apply the 'WHERE NOT EXISTS()'

Here is my query so far.

INSERT INTO MattersQSCHEDULING
(MattersQSCHEDULING, Matters, QMIDITORIDITADM, QMNVBDT, QMNVTRST, QMTMA, QMBDIT, QMCORPLLCLP
)
SELECT NEWID(), M.Matters, CQ.QCIDITORIDITADM, CQ.QCNVBDT,CQ.QCNVTRST,CQ.QCTMA,CQ.QCBDIT,CQ.QCCORPLLCLP 
FROM ContactsQSCHEDULING CQ
INNER JOIN MattersContacts MC ON CQ.contacts = MC.Contacts 
INNER JOIN Matters M ON MC.Matters = M.Matters
INNER JOIN CONTACTS C ON CQ.contacts = C.CONTACTS
WHERE c.CONTACTCLASS NOT IN ('InActive', 'Closed')
		AND m.AreaOfLaw IN ('TMA', 'IDIT', 'BDIT', 'NVBDT', 'NVTRST', 'IDIT ADMIN', 'CORP', 'LLC', 'LP', 'ILIT ADMIN', 'EATMA')
		AND (mc.IsMain = 'Y' AND mc.MtoCClass IN ('Primary', 'Client', 'Client-Master') AND m.Status NOT IN ('InActive', 'Closed'))

Open in new window


Would I add

AND NOT EXISTS (
SELECT 1 
FROM MattersQSCHEDULING MSQ 
INNER JOIN MattersContacts MC ON MSQ.Matters = MC.Matters
INNER JOIN Matters M ON MC.Matters = M.Matters ) 

Open in new window


For reference this is my UPDATE which seems to be working fine.
UPDATE MattersQSCHEDULING 
SET QMIDITORIDITADM = CQ.QCIDITORIDITADM, 
	QMNVBDT =  CQ.QCNVBDT, 
	QMNVTRST = CQ.QCNVTRST,
	QMTMA = CQ.QCTMA, 
	QMBDIT = CQ.QCBDIT, 
	QMCORPLLCLP = CQ.QCCORPLLCLP , 
	QMBD = cq.QCBD, 
	QMCONT1 = cq.QCCONT1, QMCONT2 = cq.QCCONT2, QMCONT3 = cq.QCCONT3, QMCONT4 = cq.QCCONT4, QMEA = cq.QCEA, QMEATMA = cq.QCEATMA, QMEO = cq.QCEO,
	QMEP = cq.QCEP, QMILITADMIN = cq.QCILITADMIN, QMPEOPLETOINVIT = cq.QCPEOPLETOINVIT, QMRESPATTYTOATT = cq.QCRESPATTYTOATT
FROM ContactsQSCHEDULING CQ
INNER JOIN MattersContacts MC ON CQ.contacts = MC.Contacts 
INNER JOIN Matters M ON MC.Matters = M.Matters
WHERE MattersQSCHEDULING.matters = MC.Matters

Open in new window

0
huerita37Author Commented:
Thank you for your help.  I am being pointed in a good direction.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.