I've sort of cobbled something that works, my next question is how do I return the value @alcount rather than print it?

declare @TmpName as varchar (2000)

declare @TmpName1 as varchar (2000)

declare...

thanks both of you, I give both a try and see what's easier, although I think I'd rather it was done inside a SP rather than clientside

I've worked it out and I've included it below, unless someone can show me a better way?

CREATE TABLE #Results (RowID int identity(1,1), Col1 varchar(max),col2 varchar(max), col3...

turns out what you suggested did work after all, I'd like to thank you for your help

Varchar(Max) isn't allowed in my situation either

Thanks Chris, I must admit I tried everything except that yesterday.. that worked a treat

That's fine Sean, I've done the same myself. As for my problem, although your solution looks good and as it stands would work with my table, I've put the amount...

is the following that I put in a thread earlier not good enough? it created a table with the desired data inputted. What I wanted is an output that gave...

That works well, thank you, but I need it to go across all the fields at once, adding all the data to a single table or recordset

My appologies, I've now created a script to make a table containing the data... I hope this helps

USE test

GO

-- Creating Test Table

CREATE TABLE testtable(field1 datetime, field2 VARCHAR(400),...

That's great thanks. I didn't just want code, I always want to know how it works too. Many thanks to everyone who contributed in the answers to my question

EDIT

please excuse me, I've gone back over the code and discovered a typo caused by me. Everything appears ok, thanks

thanks for the various solutions from you guys, I've tried the last piece of code 'as is' and got the following error

Msg 241, Level 16, State 1, Line 1

Conversion failed...

I do just delete or add columns, or sometimes I simply rename a column, although if this happens once or twice a year it's a lot

The table is part of a larger table and is used for other things, I just want to be able extract the piece of data I explained in my original...