I just did this..

select distinct

extension

from tblEmpData

where LEN(extension) > 3 and extension not like '6%' and DEPARTMENT like 'Service%'

union all

select distinct

case

when agent_id is not null then...

Because there is also a extension field, sorry guys copied the wrong query. I am trying to put extension, agent_ID and agent_ID into extension, sometimes one extension will have more...

Still didn't work...Not getting all of the agent_id2's

I created one cte, and then union all the select of agent_ID2..Like this

;with

csServiceExtAgentID(Extension) as

(SELECT DISTINCT

CASE WHEN [AGENT_ID] is not null then AGENT_ID end as extension

FROM [AA_Helper].[dbo].[tblEmpData]

...

No...that just concatenates the 2 I want it to create it as a second row

11111

22222

33333

Thank You, this explained just what I needed..

I need to be able to insert more than just one column, I have 6 staging tables that I have formatted the data to conform to these values...

I've created stored...

That did the trick, putting the single ticks around the number.

Thank You

Check the permissions of the account executing the report, you are more than likely using your credentials to execute the report, you may have to give the report account access...

I created the master contacts table to eliminate the remote call, It does have dups, only if the Company Name or Contact name is spelled differently. I am however still...

It ran in 31 seconds, WAY FASTER! Here's the data. I have not implemented the Master Contacts table.

I did that and it worked I got a lot of dups, so I did a MAX(cStaging.ID), and grouped that query of course, to avoid error, but then I got...

When using your proc here I get this error???

Cannot insert the value NULL into column 'CompanyName', table 'tempdb.dbo.#contacts___________________________________________________________________________________________________________000000000173'; column does not allow nulls. INSERT fails.

wBob (7/3/2014)

---

The main problem with this proc is that you reference the CTE twice. SQL Server will process that twice because CTEs behave like views rather than temp tables....

Can a Index be on the Identity that is created on the insert?