You can use a more efficient numbers table that returns numbers up to 1000:

;WITH Nbrs ( Number ) AS (

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

...

Here's another way to do it while avoiding the use of DISTINCT:

SELECT Province, District

,FPS=STUFF((

SELECT ',' + b.FP

...

Post deleted. Coffee has not yet arrived.

t.brown 89142 (11/16/2012)

---

I had assumed (incorrectly as it turns out) that this problem would have a standard SQL solution as its a classic two file problem read file A,...

Here's an interesting bit of code you can try:

CREATE TABLE #Doctors

(ID INT IDENTITY, Name VARCHAR(100), Practice VARCHAR(100), [Views] INT)

CREATE TABLE #DoctorInfo

(Name VARCHAR(100),...

Michael Meierruth (11/16/2012)

---

Jeff, I was always under the impression that you are based somewhere in the New York area or at least the EDT time zone. Have you moved? Because...

Jeff Moden (11/15/2012)

---

dwain.c (11/15/2012)

---

Jeff - Working through it now but its pretty deep and I expect it will take me awhile.

Great explanations though!

I'm going to try to see if I...

I think this is the "normal" catch all query form for this.

SELECT *

FROM #Companies

WHERE (@CompanyID IS NULL OR CompanyID = @CompanyID) AND

(@CompanyGroupID IS NULL...

mickyT (11/15/2012)

---

It's a bit ugly ... but you could do something like

;with companyNotNull as (

SELECT * FROM #Companies WHERE CompanyID = @CompanyID

),

companyNull as (

Select CompanyID FROM #CompanyGroups WHERE CompanyGroupID =...

opc.three (11/15/2012)

---

Linked Servers are useful for server-to-server communication but if you're just needing to communicate from one database to another on the same instance then Linked Servers would not be...

You might still be able to do it by JOINing the derived table a to whatever it is you need to get. It might not be as complex as...

Note that with the suggested approach, you'll be limited to returning columns that exist in the Doctors table. If you need stuff out of those other JOINs you'll be...

This is just a guess mind you but perhaps something like this would work:

SELECT DoctorID, LastName, FirstName

FROM (

UPDATE Doctors

SET NumImpressionsInSearches =...

Of course there will be carriage returns at the end of each line of numbers.

Try this:

SELECT LEN(REPLACE(REPLACE(@SQL, CHAR(10), ''), CHAR(13), ''))

Here's a method using the rCTE approach to calculating a running total:

CREATE TABLE #TblTest (

id int not null identity(1,1) primary key,

value int not null

);

INSERT INTO #TblTest (value) VALUES...