Table A = Clarity (uniqe ID = CaseID; contains CSN)
Table B = Survey (unique ID = CSN)
Table C = QA (unique ID = caseID; contains CSN)
Table D = FullData
Goal:
“TableD” contains:
all “TableC”,
all “TableA” for which there is a “CaseID” and “CSN” in common with “Table C”
all “TableB” for which there is a “CSN” in common with “Table C”
Table is remade every evening. There are a lot of people who will be doing query research on “Table D.” I think it needs to be a table and not a view.
I was going to use:
Create TableD AS
Select *
From TableC
Left Join TableA
ON TableA.CaseID = TableC.CaseID AND TableA.CSN = TableC.CSN
Left Join TableB
ON TableC.CSN = TableC.CSN
I was going to use SQL Agent to make the script run every night. This seems too simple. Do I have to drop the table that was made the day before? Does anyone see an error? I am using Microsoft SQL Server.