On May 18, 5:07*pm, Erland Sommarskog <esq...@sommars kog.sewrote:
Snow (cs180y...@gmai l.com) writes:
I want to find that the ntext column data string have more than 2000
characters. I need to truncate those strings to the segments with 200
character, then put those segments along with their table_name and
column_name to another table. Maybe need to use cursor? If so, how to
use it?
Your help is highly appreciated.
It's difficult to work with ntext columns more than one a time, so it
sounds like you need to use a cursor. But your description is far too
terse for me to want to give an example. Could you post:
1) *CREATE TABLE statements for your table(s).
2) *INSERT statements with sample data.
3) *The desired result given the sample.
Since it's unpractical to post strings with 2000 characters, you
could pretent that the limit is 50 characters or whatever when you
compose the example.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
Hello:
Thanks for the reply. Actually problem is ntext hold 4000 char. I
need to split ntext data into the segnments with 4000 char, and then
put those segnments to the another table. For example: The ntext data
string has 12000 char. It will be split to 3 segments. The another
table has the char field: long_seg. The column has segnment1(4000
char), segnment2(4000 char), segnment 3(4000 char) which comes from
the splitting ntext data sting with 12000 char. Another field:
segment_nbr holds segment number : 1, 2, and 3.
Here is the table to get the splitting segments.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[tbl_longdata]') and OBJECTPROPERTY( id, N'IsUserTable') = 1)
drop table [dbo].[tbl_longdata]
GO
CREATE TABLE [dbo].[tbl_longdata] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[table_name] [char] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[col_name] [char] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[tableId] [int] NULL ,
[segment_nbr] [int] NULL ,
[long_seg] [nvarchar] (4000) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
Here is the simple cursor test one table: tbl_deptTri.
DECLARE @id_cur int
DECLARE @deptTriDes ntext
Declare @segment_nbr_cu r int
DECLARE @segment_nbr_ne w INT
DECLARE @deptTriDes_cur ntext
DECLARE @stringpos INT
DECLARE TableCursor CURSOR FOR
SELECT [id], [deptTriDes]
FROM tbl_deptTri
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @id_cur, @deptTriDes_cur
WHILE @@FETCH_STATUS = 0
BEGIN
SET @segment_nbr_ne w = 0
SET @segment_nbr_cu r = CEILING(DATALEN GTH(@deptTriDes _cur)/8000)
WHILE @segment_nbr_ne w <= @segment_nbr_cu r
BEGIN
SET @segment_nbr_ne w = @segment_nbr_ne w + 1
SET @stringpos = (@segment_nbr_n ew - 1)*4000 + 1
INSERT INTO tbl_LongData ([table_name],[col_name],[tableID],
[segment_nbr],[long_seg])
VALUES (
'tbl_deptTri',
'deptTriDes',
@id_cur,
@segment_nbr_ne w,
SUBSTRING(@dept TriDes_cur,@str ingpos,4000)
)
END
FETCH NEXT FROM TableCursor INTO @id_cur, @deptTriDes_cur
END
CLOSE TableCursor
DEALLOCATE TableCursor
However, I got the error: The text, ntext, and image data types are
invalid for local variables.
what am I missing here?
Thanks
