• Okay that is one horribly slow way of doing that. Now i dont have a script for doing this myself so creating one from scratch i have dropped some stuff that should be there. Like handling of different datatypes. Possibly you would want to exclude some columns (like identity). But this wouldnt effect performance (or would make it faster... less columns :-)).

    But i came up with this.

    declare @table varchar(128)

    select @table = 'YourTableHere'

    declare @Columns table (column_id integer primary key, name varchar(128), type varchar(128))

    create table #result (RowNr integer primary key, str varchar(max))

    insert into @Columns (column_id, name, type)

    select ordinal_position, Column_name, data_type from information_schema.columns where table_name = @table

    --In order to be able to sort the data exactly the same way every time we get the PK so we can sort on that

    declare @pk varchar(max)

    select @pk = (select ', ' + c.name

    from sys.key_constraints as k

    join sys.tables as t on t.object_id = k.parent_object_id

    join sys.index_columns as ic on ic.object_id = t.object_id and ic.index_id = k.unique_index_id

    join sys.columns as c on c.object_id = t.object_id and c.column_id = ic.column_id

    where t.name = @table and k.type = 'PK'

    order by ic.key_ordinal

    for xml path(''), TYPE).value('.', 'varchar(max)')

    select @pk = SubString(@pk, 3, Len(@pk))

    --Build the start of the insert string

    declare @insertstring varchar(max)

    select @insertstring = (select name + ', ' from @Columns order by column_id for xml path (''), TYPE).value('.', 'varchar(max)')

    select @insertstring = 'insert into ' + @table + ' (' + SubString(@insertstring, 1, Len(@insertstring) - 2) + ') values ('

    --Okay time to build the result one column at a time

    declare @i integer

    declare @sql varchar(max)

    set @i = 0

    while exists (select * from @Columns where column_id > @i)

    begin

    set @i = @i + 1

    --First time we have to insert instead of update

    if @i = 1

    begin

    select @sql = ';with cte as (select row_number() over (order by ' + @pk + ') RowNr, Convert(varchar(max), ' + name + ') str from ' + @table + ') ' +

    'insert into #Result (RowNr, str) select RowNr, str from cte'

    from @Columns where column_id = @i

    exec (@sql)

    end

    --Add the next column to the result

    if @i > 1

    begin

    select @sql = ';with cte as (select row_number() over (order by ' + @pk + ') RowNr, Convert(varchar(max), ' + name + case when type = 'datetime' then ', 121' else '' end + ') str from ' + @table + ') ' +

    'update r set str = r.str + '', '' + IsNull('''''''' + cte.str + '''''''', ''NULL'') from #result r join cte on cte.RowNr = r.RowNr'

    from @Columns where column_id = @i

    exec (@sql)

    end

    end

    select RowNr, @insertstring + str + ')' from #result

    drop table #result

    Have to excuse the poor formating. Now i did do a WHILE instead of a cursor. Either one i think would be okay in this circumstance since the nr of columns are so few.

    The resulting insert command between the posted procedure and the above code are almost identical (differs a bit in formating).

    The HUGE difference is time. I ran both against a table with about 5000 rows (so a small one) with about 35 columns. And the procedure takes about 6min to finish and the above code about 7s (with result returned to the client).

    I almost guarantee that there are even faster solutions (because mine are normally not among the fastest... but fast enough for me :-)). So i eagerly await that 😀

    /T