Thanks very much for all your comments. I have read the information at the link and I think that it will be very useful.

Once again Thanks

Sorry misinformation.

The server has 32 Gig and is 64 Bit

Please close off this thread I have sorted myself by using a temp table

Yep that works ok. I realise that there is only a small part of the SP there but if I put it all on it would be I think too...

New code etc

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--=====...

Before anyone says anything I must apologise as I am new to this game. I have just read the way you should post things here and realise I should have...

Ok Here goes

Field1Field2Field3Field4Field5Field6Field7Field8Field9Field10Field11

Batch1Invoice1Post120.00VAT1.75Dummy1XXXProduct1UOM

Batch1Invoice1Post114.33VAT1.23Dummy2XXXProduct1UOM

Batch1Invoice1Post112.44VAT2.45Dummy3XXXProduct2UOM

Batch1Invoice1Post115.44VAT3.12Dummy4XXXProduct2UOM

Batch1Invoice1Post1123.56VAT4.56Dummy5XXXProduct3UOM

Batch1Invoice2Post120.00VAT1.75Dummy1XXXProduct1UOM

Batch1Invoice2Post114.33VAT1.23Dummy2XXXProduct1UOM

Batch1Invoice2Post112.44VAT2.45Dummy3XXXProduct2UOM

Batch1Invoice2Post115.44VAT3.12Dummy4XXXProduct2UOM

Batch1Invoice2Post1123.56VAT4.56Dummy5XXXProduct3UOM

Batch1Invoice2Post120.00VAT1.75Dummy1XXXProduct1UOM

Batch1Invoice2Post114.33VAT1.23Dummy2XXXProduct1UOM

Batch1Invoice3Post112.44VAT2.45Dummy3XXXProduct2UOM

Batch1Invoice3Post115.44VAT3.12Dummy4XXXProduct2UOM

Batch1Invoice3Post1123.56VAT4.56Dummy5XXXProduct3UOM

Records being used for input

Output as of now :

D9999JMBatch1Invoice1Post120.00VAT1.75 AAA00009999991 ...

Sorry - It is not a problem as such more of a query

The result is basically a header record, detail records then a footer record in a 2nd table....

After making a slight change everything is brilliant

Thanks for your help

Have a good weekend

The solution looks fine, I just need the product and description to show as well and I think we are there.

I have tweeked the code and have put it inside a cursor which should read the first 3 chars and then change to which ever it should be.

Once I have...

Absolutely brilliant but they have now thrown a curved ball and state that some of the phone numbers are from other countries and so the zero needs also to stay...

Yes I think that will be the way as I am basically trying to get the area code into the brackets (removing the 0) leaving the rest of the phone...

Thanks for that it does what I want for the example phone number.

I also have other phone numbers though they might be

+44 (0) 1908 123 456

or

+44 (0) 121...

Always a zero

Thanks for the quick reply