Select Query to Temp Table then Link.

[Schaeffrcc]

I have a database that has many tables and a massive table that links it all together called Case_History. I have been trying for a while to get the correct data out of this history table because of duplication when Client move from here to there and back I have resigned myself to breaking it down into may parts. Below is the code for the First Part, it extracts the DISTINCT Case_Seq that I need. This Select Query works great and I believe it give me the correct data. Now I want to put that data into a Temp table so I can link in the other tables to get the other data that I need to get from these Case_Seq.

Select DISTINCT case_table.case_seq

FROM ((agent RIGHT JOIN (client LEFT JOIN client_agent_link ON client.client_seq = client_agent_link.client_seq) ON agent.agent_seq = client_agent_link.agent_seq) RIGHT JOIN case_table ON client.client_seq = case_table.client_seq) LEFT JOIN case_history ON case_table.case_seq = case_history.case_seq

WHERE (((case_history.case_type_seq)=2 Or (case_history.case_type_seq)=6 Or (case_history.case_type_seq)=23 Or (case_history.case_type_seq)=9 Or (case_history.case_type_seq)=13 Or (case_history.case_type_seq)=14) AND ((case_history.case_open_date)>='5/1/2003') AND ((client_agent_link.ca_link_to_date) Is Null) AND ((agent.agent_initials)<>'SCL' And (agent.agent_initials) Not Like '%0%'));

I need to put this data into a Temp table called CaseSeq with a Column named Case_Seq of type INT and then link that temp table to other data tables via the Case_Seq field in the CaseSeq Table.

Please help,

Thomas.

 

[NoCoolHandle]

If I am reading you corectly all you should need to do is add the into synatx to your query..

e.g.
Select DISTINCT case_table.case_seq
[blue]INTO #TEMPTABLENAME[/blue]
FROM ((agent RIGHT JOIN (client LEFT JOIN client_agent_link ON client.client_seq = client_agent_link.client_seq) ON agent.agent_seq = client_agent_link.agent_seq) RIGHT JOIN case_table ON client.client_seq = case_table.client_seq) LEFT JOIN case_history ON case_table.case_seq = case_history.case_seq

HTH

Rob

 

[Schaeffrcc]

So I have tried the suggestion and using the following syntax and I get the following error.

CREATE TABLE #CaseSeq
(Case_Seq int)

Select DISTINCT case_table.case_seq
INTO #CaseSeq
FROM ((agent RIGHT JOIN (client LEFT JOIN client_agent_link ON client.client_seq = client_agent_link.client_seq) ON agent.agent_seq = client_agent_link.agent_seq) RIGHT JOIN case_table ON client.client_seq = case_table.client_seq) LEFT JOIN case_history ON case_table.case_seq = case_history.case_seq

WHERE (((case_history.case_type_seq)=2 Or (case_history.case_type_seq)=6 Or (case_history.case_type_seq)=23 Or (case_history.case_type_seq)=9 Or (case_history.case_type_seq)=13 Or (case_history.case_type_seq)=14) AND ((case_history.case_open_date)>='5/1/2003') AND ((client_agent_link.ca_link_to_date) Is Null) AND ((agent.agent_initials)<>'SCL' And (agent.agent_initials) Not Like '%0%'));

Error:
Server: Msg 2714, Level 16, State 2, Line 1
There is already an object named '#CaseSeq' in the database.

What am I missing?

Next step would be linking this temp table to other data tables.

Thanks for your help,

Thomas.

 

[SQLSister]

If you create the temp table first, the you use the insert statement, not select into to put the data in the table.

Insert #CaseSeq
(Case_Seq int)
Select DISTINCT case_table.case_seq

FROM ((agent RIGHT JOIN (client LEFT JOIN client_agent_link ON client.client_seq = client_agent_link.client_seq) ON agent.agent_seq = client_agent_link.agent_seq) RIGHT JOIN case_table ON client.client_seq = case_table.client_seq) LEFT JOIN case_history ON case_table.case_seq = case_history.case_seq

WHERE (((case_history.case_type_seq)=2 Or (case_history.case_type_seq)=6 Or (case_history.case_type_seq)=23 Or (case_history.case_type_seq)=9 Or (case_history.case_type_seq)=13 Or (case_history.case_type_seq)=14) AND ((case_history.case_open_date)>='5/1/2003') AND ((client_agent_link.ca_link_to_date) Is Null) AND ((agent.agent_initials)<>'SCL' And (agent.agent_initials) Not Like '%0%'))

 

[Schaeffrcc]

SQLSISTER

I tied your Syntax and now get the following error.

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'int'.

What am I missing?

Thanks,

Thomas.

 

[Rhys666]

Try...
Insert #CaseSeq
(Case_Seq)
Select DISTINCT case_table.case_seq

...should fix it...


Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!