December 7, 2009 at 5:04 pm
For some reason, this code is returning a retarded amount of rows.
Hasnt been a problem previously, anyone got any insight?
So.
Trying to create a basic staging table for other operations/stored procedures.
Done it a few times.
Target is an SQL2000 server but query is originating on a SQL2005 server.
Problem is, this thing keeps returning a ridiculous amount of rows for absolutely no reason I can fathom.
Any insight would be helpful!
why am I getting multiple rows for this simple query?
----------------------------
INSERT INTO Staging_Tables ( Filenumber, dateloaded, lastname, firstname, sex, stage, homephone, workphone, cellphone, email, current_status, Current_Status_Date, et et et)
SELECT c.filenum, getdate(), cl.lastname, cl.firstname, cl.sex, cl.state,
p
.homePhone, p.workPhone, p.cell_phone, p.eMail, c.currStatus,
c
.currStatusDate, c.dnis, c.client_source_id, csl.client_source,
csl
.client_source_group, cl.client_id, cl.address_id, cl.phone_id,
ml
.lead_type, UPPER(e.loginName), 3, ad.fulfillment
FROM Client_Manager_Production.dbo.t_contact c
JOIN Client_Manager_Production.dbo.t_client cl
ON cl.client_id = c.primary_client_id
JOIN Client_Manager_Production.dbo.t_client_phone p
ON p.phone_id = cl.phone_id
JOIN Client_Manager_Production.dbo.Client_Source_Library csl
ON csl.client_source_id = c.client_source_id
JOIN Client_Manager_Production.dbo.media_lookup ml
ON ml.source = c.source
JOIN Client_Manager_Production.dbo.t_task_tracker t
ON t.filenum = c.filenum
AND t.recType = 4
JOIN Client_Manager_Production.dbo.m_emp_header e
ON t.empNum2 = e.empnumber
JOIN DialerDB.Dialer_Master.dbo.Master_call_list mc
on mc.filenumber = c.filenum
JOIN client_manager_production.dbo.t_contact_app_data ad
on c.filenum = ad.filenum
AND Currstatus IN ('App Hold', 'App complete', 'app complete no paramed')
December 7, 2009 at 5:33 pm
Forget about the INSERT statement until you get the SELECT working correctly. You really should place this code into a stored procedure. That way most anything can call the sproc and it will fire off correctly.
Begin by grouping all the like tables together in the select statement. Using the table aliases, group the table.columns together for easier troubleshooting. Then begin by commenting out most every table in the JOIN clause. Then start adding in the JOINs one at a time, and adding in the table.column for that alias one at a time. Fire off the SELECT statement after adding a JOIN. You will soon discover the one JOIN that is causing all the problems. When you do, your troubleshooting is over.
Andrew SQLDBA
December 7, 2009 at 5:40 pm
I agree with Andrew here. There is at least one table causing your results to grow. Narrow it down as Andrew explained and work on the select statement first.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply