Gosh, the goalposts have moved for a second time. It's not the LEFT JOIN - just put a DISTINCT after the SELECT. That will give the results you...

Benki Chendu (9/27/2016)

---

We have peer to peer replication configured in SQL 2014.

1. Could the new column be introduced in the publisher newly which isnt getting pushed to the subscriber. Creation/modification...

SELECT

s.Name

,s.City

,s.State

,r1.ReferenceName1

,r1.Address1

,r1.Address2

,r2.ReferenceName2

,r2.City AS R2_City

,r2.State AS R2_State

FROM #SourceData s

LEFT JOIN #ReferenceTable1 r1 ON LEFT(s.Name,CHARINDEX(' ',s.Name)) = LEFT(r1.ReferenceName1,CHARINDEX(' ',r1.ReferenceName1))

LEFT JOIN #ReferenceTable2 r2 ON LEFT(s.Name,CHARINDEX(' ',s.Name)) = LEFT(r2.ReferenceName2,CHARINDEX(' ',r2.ReferenceName2))

John

If you're converting to datetime in order to store in your table, you don't...

Lat

Why are you doing this row-by-row? Why not update everything in one go?

John

I've always thought that it would be good if you could shrink the file sizes as part of the restore. Unfortunately, you can't, and so you need the same...

Apologies - I misread. I answered as if the clustered index were on all columns instead of just on ID.

John

Mike

I know technically a table without...

Although if you're going down from 2008 R2 to 2008, that probably won't work. Don't know for sure, though - I've never tried it.

John

In which case, it's likely you'll need to accept some data loss to get this fixed. But we can't be sure of that until you do what it suggests...

Mike

No, because if you only have a non-clustered index, your table is a heap. Also, in the non-clustered index, the key is only ID instead of all of the...

Sanjay

Have you done what it suggests in the error message? Looks as if the problem is so serious that you can't back up the database. Copies of the...

Use PATINDEX to find where the text finishes and the date starts you can use '20[0-9][0-9]' or something like that as your pattern match. Once you've got that, it's...

Your CASE expression returns NEW or OLD, and you're casting it to bit, which isn't valid. Change your CASE expression so that it returns 1 or 0, or cast...

Looks fairly simple - you're just joining all three tables together, and using a MAX or MIN to get the top sale per invoice. Is there any particular part...