INSERT INTO dbo.TableA

( Sequence, Title, Signature, IsDefault, UserName )

SELECT A_Last.Sequence + B.row_num, A.Title, A.Signature, 'Y' AS IsDefault, B.UserName

FROM (

SELECT B2.*, ROW_NUMBER() OVER(ORDER BY B2.UserName) AS row_num

...

--

GilaMonster (8/24/2016)

---

Filestream isn't a datatype. It's an attribute of a varbinary data type column. Bigint is definitely not considered as large object, it's a grand total of 16-bytes in size.

Your...

Afaik, there is no way to get that number back from SQL. In theory, SQL could have had to cascade thru a dozen tables (or more).

I give up. You keep randomly changing things. And this message:

"The variable name '%.*ls' has already been declared. Variable names must be unique within a query batch or...

SET @SQLstmt =

N'DECLARE @RCount int ' +

' SELECT [LCPRBSELF]' +

' FROM [' + @rowDSServer+ ']' + '.'

+ '['...

DOH, sorry, typo on my part. Should be:

values('DP'),('SP')

[rather than values('DP','SP')]

Edit: added bold to the parens I added to highlight them better.

Floating point values are only approximate values rather than exact values. If you need to make an equals comparison, you need to use decimal values.

Your variable in the code is named @Rcount. You need to use that name in the @params clause:

EXEC sys.sp_executesql @stmt = @SQLstmt

, @params = N'@Rcount INT OUTPUT'

select MBR_LAST_NAME, MBR_FIRST_NAME, BIRTH_DT, SERVICE_DT,

SERVICE_TYPE_CD, case when SERVICE_TYPE_CD = 'DP' then DP ELSE SP END AS BLOOD_PRESSURE

from (

select MBR_LAST_NAME, MBR_FIRST_NAME, BIRTH_DT, SERVICE_DT,...

select MBR_LAST_NAME, MBR_FIRST_NAME, BIRTH_DT, SERVICE_DT,

MAX(case when SERVICE_TYPE_CD = 'DP' then <value> end) AS DP,

MAX(case when SERVICE_TYPE_CD = 'SP' then <value> end)...

Those are excellent points. Thus, the other rule you should follow when creating indexes is to always explicitly specify CLUSTERED or NONCLUSTERED. Again, clustering is far too critical...

To avoid pulling the entire remote table columns over every time the query runs, you need to add an index to the remote table that will identify rows updated or...

1) No. The primary key and clustering key are not necessarily directly related. The clustering index is the single most critical factor for performance. And it should...

Ed Wagner (8/17/2016)

---

Jeff Moden (8/17/2016)

---

But they have direct access to the audit table? That doesn't make sense to me...