If you run this

select *

from MyTable

where exists (select * from MyTable t

where t.ObjectId=MyTable.ObjectId

and t.DataKey=MyTable.DataKey

and t.IdentCol>MyTable.IdentCol)

it should show you the rows that are to be deleted. The query simply returns rows...

delete

from MyTable

where exists (select * from MyTable t

where t.ObjectId=MyTable.ObjectId

...

Maybe this?

select hostname from master.dbo.sysprocesses where spid=@@spid

Also for variable width string,

if substring(@s,3,7) like '%[^0-9]%'

print 'Not Numeric'

Use LIKE

declare @s varchar(9)

--set @s='ab1234567'

set @s='ab123456x'

if @s not like '__[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

print 'Not Numeric'

Another way...

WITH OrderedCTE AS (

SELECT ID,

KEYID,

VALUE_STR,

ROW_NUMBER()OVER(PARTITION BY KEYID ORDER...

ASCII '0A' is '\ n' not '\r', try changing the ROWTERMINATOR

Can enrollment dates overlap, such as this?

Insert Into tbl_Cli_Insu(ClientID, eff_dt, exp_dt) Values (9, '20080101', '20080301')

Insert Into tbl_Cli_Insu(ClientID, eff_dt, exp_dt) Values (9, '20080201', '20080501')

Insert Into tbl_Cli_Insu(ClientID, eff_dt, exp_dt) Values (9, '20080401',...

It's not really clear how LineNo joins, but this gives the correct result.

WITH Questions(Contact,[LineNo],LineNoMax,Question) AS (

SELECT a.Contact,a.[LineNo],MIN(b.[LineNo])-1,a.Question

FROM #Question a

LEFT OUTER JOIN #Question b ON b.Contact=a.Contact AND b.[LineNo]>a.[LineNo]

GROUP BY a.Contact,a.[LineNo],a.Question)

SELECT a.Contact,a.Question,b.Answer

FROM...

See if this helps

DECLARE @Jobs TABLE(Street VARCHAR(10), Seq INT)

INSERT INTO @Jobs(Street, Seq)

SELECT 'Street A',1 UNION ALL

SELECT 'Street A',2 UNION ALL

SELECT 'Street A',3 UNION ALL

SELECT 'Street A',4 UNION ALL

SELECT 'Street A',5...

See this thread

http://www.sqlservercentral.com/Forums/FindPost693820.aspx

For example

WITH CTE AS (

SELECT Doctor_Id,

Tests,

ROW_NUMBER() OVER(PARTITION BY Doctor_Id...

Using EXCEPT will give you pairs in Table1 that aren't in Table2

SELECT [Cust#],CustName FROM Table1

EXCEPT

SELECT [Cust#],CustName FROM Table2

Maybe this?

with cte as (

select Col1

, Col2

, Col3

, Col4

from MyTable

)

select case when @input_variable = 1 then sum(Col1)

when...

You could use the SIGN function, but I can't think why you would use this in preference to "<0"

You may find it easier to use an OUTPUT clause in the INSERT statement

.

.

INSERT INTO [dbo].[tbl_Users] ( [lotsOfColumns] )

OUTPUT inserted.userid ...