This allows you to add text data to a table.
You call the SP multiple times until all text data is added.
The first call is with @Action = 'I' to insert the record.
Following calls are with @Action = 'A' until all data has been added

If the PK on the table is an identity then the first call to add the record should return that value in an output parameter for subsequent calls.


Create table test (i int, j int, t text)
go
create unique index ix on test
	(i, j)
go

create proc nr_AddTextRec
@i int ,
@j int ,
@t varchar(8000) ,
@Action varchar(1)	-- 'I insert, A append
as
declare @ptr binary(16)

	if @Action = 'I'
	begin
		insert	test
			(
			i ,
			j ,
			t
			)
		select 	@i ,
			@j ,
			@t
	end
	if @Action = 'A'
	begin
		select 	@ptr = textptr(t) 
		from	test
		where	i = @i
		and	j = @j
		
		updatetext test.t @ptr null 0 @t
	end

go

exec nr_AddTextRec 1, 1, 'asadf', 'I'
exec nr_AddTextRec 1, 1, 'jjjjj', 'A'
exec nr_AddTextRec 1, 1, 'kkkkk', 'A'
declare @s varchar(8000)
select @s = replicate('a',6000)
exec nr_AddTextRec 2, 1, @s, 'I'
exec nr_AddTextRec 2, 1, @s, 'A'
exec nr_AddTextRec 2, 1, 'jjjjj', 'A'
exec nr_AddTextRec 2, 1, 'kkkkk', 'A'
exec nr_AddTextRec 2, 1, @s, 'A'


select i,j, substring(t, 1, 8000) from test
select i,j, substring(t, 8001, 8000) from test
select i,j, substring(t, 16001, 8000) from test