dwain.c (8/29/2012)

---

Yeah, so now what I haven't tested it with is:

Since you can use both OUTPUT and OUTPUT INTO within the same INSERT, UPDATE, DELETE or MERGE, can you also...

dwain.c (8/29/2012)

---

ChrisM@Work (8/29/2012)

---

Why not? Seems very unreasonable.

You're using SQL Server...

Why not? Seems very unreasonable.

You're using SQL Server 2008 -...

The easiest way I could replicate this behaviour was by using the ANSI_DEFAULTS setting:

SET ANSI_DEFAULTS Off -- change to ON to raise error

DROP TABLE #SAP_CT_Codes

CREATE TABLE #SAP_CT_Codes (old_store_reference VARCHAR(8))

INSERT INTO...

hardus.lombaard (8/29/2012)

---

update a

set a.Flag = 0

from table1 a

WHERE

a.OLD_NUMBER in (select number from table2) and

a.NEW_NUMBER not in (select number from table2)

The above query sets the flag column to 0...

dwain.c (8/28/2012)

---

I am intrigued by ChrisM@Work's comment about possible concurrency issues, so I will submit this suggestion so that he may shoot holes in it based on his statement:

CREATE TABLE...

The expressions for title and forename will both generate a negative number for the length...

Thanks Andy, that will do fine. Incorporating into Anthony's dataset;

DECLARE @Account TABLE (AccountNo INT, Name VARCHAR(100))

INSERT INTO @Account VALUES

(60000000,'Mrs Rayford Hufflepuff18'),

(60000001,'Miss Forest Hufflepuff18'),

(60000061,'Miss Bex Ignore'),

(60000068,'Mr Jason Twirl'),

(60000088,'Mrs Katuta Sunger'),

(60000111,'Mrs Corene...

Run the query I...

Can you post the query, Andy? There are a couple of simple...

You can detect name strings with less than three words with a slight modification of the original query:

SELECT

AccountNo,

Name,

space1.pos,

space2.pos

--Title= LEFT(Name,space1.pos-1),

--Forename= SUBSTRING(Name,space1.pos+1,space2.pos-space1.pos-1),

--Surname= SUBSTRING(Name,space2.pos+1,8000)

FROM @Account

CROSS APPLY (SELECT CHARINDEX(' ', Name,1))...

Andy, if you set this up as a DDL script CREATE TABLE ... and INSERT INTO TABLE ..., then paste...

amarkhowe (8/28/2012)

---

SELECT

AccountNumber,

Name,

Name = LEFT(Name,space1.pos-1),

Name = SUBSTRING(Name,space1.pos+1,space2.pos-space1.pos-1),

Name = SUBSTRING(Name,space2.pos+1,40)

FROM dbo.AccountBase

CROSS APPLY (SELECT CHARINDEX(' ', Name,1)) space1 (pos)

CROSS APPLY (SELECT CHARINDEX(' ', Name,space1.pos+1)) space2 (pos)

But the above creates an error

Msg 537,...

SELECT

CASE

WHEN (GROUPING([MAT]) = 1) THEN 'Tot'

ELSE [MAT] END

AS MAT,

--[myNUmber]

COUNT (*) AS [myNUmber]

FROM (

SELECT DISTINCT

CASE

WHEN LEFT (TZZ, 2) IN ('1D','1F') THEN 'MAO'

WHEN LEFT (TZZ, 2) IN ('1G','1H') THEN 'MAE'

WHEN...

amarkhowe (8/28/2012)

---

Yes they would be so example:

Full Name

MR Peter ASHWORTH

would end up as

Full Name TitleFirst...