insert ReadText Data into temp table

Hi,

I want to insert result of readtext into a temp table.
I am working on crystal report . Crystal report text objtect has a text 
limit thats why  I want to split data stored in db in multiple rows.
the column filed is text type .
Please help me.

Thanks,
Hemant 


0
Hemant
5/20/2010 1:03:48 PM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
1027 Views

Similar Articles

[PageSpeed] 42

On Thu, 20 May 2010 18:35:34 +0530, "Hemant" <Hemant@nomail.com>
wrote:

>Hi,
>
>I want to insert result of readtext into a temp table.
>I am working on crystal report . Crystal report text objtect has a text 
>limit thats why  I want to split data stored in db in multiple rows.
>the column filed is text type .
>Please help me.
>
>Thanks,
>Hemant 
>
Hemant 

I assume that if you have a limit to the text size in your report then
you want to split the text column into parts. You could use SUBSTRING
to do this.

e.g.

USE tempdb
GO

CREATE TABLE txttbl ( id int not null identity, textdata text )

INSERT INTO txttbl ( textdata ) SELECT REPLICATE('ABC',8000)
GO


SELECT ID
	, SUBSTRING(textdata,1,250) AS str1
	, SUBSTRING(textdata,251,250) AS str2
	, SUBSTRING(textdata,501,250) AS str3
FROM txttbl

John
0
John
5/20/2010 4:00:23 PM
Thanks John

this give data in columns i need data in rows.
i have done this but know i want to pass text column to below sp what to do 
this?

Create PRocedure   SplitTextToString
(

    @myString text,
    @CharLimit int
)
AS
BEGIN
Declare
@ReturnTable TABLE
(
    -- Add the column definitions for the TABLE variable here
    [id] [int] IDENTITY(1,1) NOT NULL,
    [part] text NULL
)
 if(@CharLimit > 4000)
 set @CharLimit = 3999

    declare @StrLen int
    select @StrLen = Datalength(@myString)
    if(@StrLen > @CharLimit)
  begin
   declare @IsExit bit
   declare @Substr nvarchar(4000)
   declare @StartPoint int
   declare @EndPoint int
   declare @strPass int
   set @StartPoint = 1
   set @EndPoint = @CharLimit
   set @IsExit = 0
   set @strPass = 0
   while (@IsExit = 0)
    begin
     select @Substr = substring(@myString,@StartPoint,@EndPoint)
     insert into @ReturnTable
     select @Substr
     set @StartPoint =@StartPoint+@EndPoint

     set @strPass = @strPass + @CharLimit
     if(Datalength(@myString)-@strPass < @CharLimit)
      begin
       set @EndPoint = @EndPoint+ Datalength(@myString)
          set @IsExit = 1
      end
     else
      begin
       set @EndPoint =  @CharLimit
      end
    end

   begin
    insert into @ReturnTable
    select substring(@myString,@StartPoint,@EndPoint)
   end
 end
 else
  begin
   insert into @ReturnTable
   select @myString
  end

select * from @ReturnTable
END

thaks,
Hemant
"John Bell" <jbellnewsposts@hotmail.com> wrote in message 
news:lgmav5hu4hvu2eqnullvkm023fmq8i3map@4ax.com...
> On Thu, 20 May 2010 18:35:34 +0530, "Hemant" <Hemant@nomail.com>
> wrote:
>
>>Hi,
>>
>>I want to insert result of readtext into a temp table.
>>I am working on crystal report . Crystal report text objtect has a text
>>limit thats why  I want to split data stored in db in multiple rows.
>>the column filed is text type .
>>Please help me.
>>
>>Thanks,
>>Hemant
>>
> Hemant
>
> I assume that if you have a limit to the text size in your report then
> you want to split the text column into parts. You could use SUBSTRING
> to do this.
>
> e.g.
>
> USE tempdb
> GO
>
> CREATE TABLE txttbl ( id int not null identity, textdata text )
>
> INSERT INTO txttbl ( textdata ) SELECT REPLICATE('ABC',8000)
> GO
>
>
> SELECT ID
> , SUBSTRING(textdata,1,250) AS str1
> , SUBSTRING(textdata,251,250) AS str2
> , SUBSTRING(textdata,501,250) AS str3
> FROM txttbl
>
> John 


0
Hemant
5/21/2010 5:52:52 AM
Hi Hemant

Once you have the columns you could use UNPIVOT to create the rows
http://pratchev.blogspot.com/2009/02/unpivoting-multiple-columns.html
or use UNION where you select each substring separately. You could
also look at http://www.sommarskog.se/arrays-in-sql.html and the fixed
length methods which may be adaptable to your needs.

John

On Fri, 21 May 2010 11:23:33 +0530, "Hemant" <Hemant@nomail.com>
wrote:

>Thanks John
>
>this give data in columns i need data in rows.
>i have done this but know i want to pass text column to below sp what to do 
>this?
>
>Create PRocedure   SplitTextToString
>(
>
>    @myString text,
>    @CharLimit int
>)
>AS
>BEGIN
>Declare
>@ReturnTable TABLE
>(
>    -- Add the column definitions for the TABLE variable here
>    [id] [int] IDENTITY(1,1) NOT NULL,
>    [part] text NULL
>)
> if(@CharLimit > 4000)
> set @CharLimit = 3999
>
>    declare @StrLen int
>    select @StrLen = Datalength(@myString)
>    if(@StrLen > @CharLimit)
>  begin
>   declare @IsExit bit
>   declare @Substr nvarchar(4000)
>   declare @StartPoint int
>   declare @EndPoint int
>   declare @strPass int
>   set @StartPoint = 1
>   set @EndPoint = @CharLimit
>   set @IsExit = 0
>   set @strPass = 0
>   while (@IsExit = 0)
>    begin
>     select @Substr = substring(@myString,@StartPoint,@EndPoint)
>     insert into @ReturnTable
>     select @Substr
>     set @StartPoint =@StartPoint+@EndPoint
>
>     set @strPass = @strPass + @CharLimit
>     if(Datalength(@myString)-@strPass < @CharLimit)
>      begin
>       set @EndPoint = @EndPoint+ Datalength(@myString)
>          set @IsExit = 1
>      end
>     else
>      begin
>       set @EndPoint =  @CharLimit
>      end
>    end
>
>   begin
>    insert into @ReturnTable
>    select substring(@myString,@StartPoint,@EndPoint)
>   end
> end
> else
>  begin
>   insert into @ReturnTable
>   select @myString
>  end
>
>select * from @ReturnTable
>END
>
>thaks,
>Hemant
>"John Bell" <jbellnewsposts@hotmail.com> wrote in message 
>news:lgmav5hu4hvu2eqnullvkm023fmq8i3map@4ax.com...
>> On Thu, 20 May 2010 18:35:34 +0530, "Hemant" <Hemant@nomail.com>
>> wrote:
>>
>>>Hi,
>>>
>>>I want to insert result of readtext into a temp table.
>>>I am working on crystal report . Crystal report text objtect has a text
>>>limit thats why  I want to split data stored in db in multiple rows.
>>>the column filed is text type .
>>>Please help me.
>>>
>>>Thanks,
>>>Hemant
>>>
>> Hemant
>>
>> I assume that if you have a limit to the text size in your report then
>> you want to split the text column into parts. You could use SUBSTRING
>> to do this.
>>
>> e.g.
>>
>> USE tempdb
>> GO
>>
>> CREATE TABLE txttbl ( id int not null identity, textdata text )
>>
>> INSERT INTO txttbl ( textdata ) SELECT REPLICATE('ABC',8000)
>> GO
>>
>>
>> SELECT ID
>> , SUBSTRING(textdata,1,250) AS str1
>> , SUBSTRING(textdata,251,250) AS str2
>> , SUBSTRING(textdata,501,250) AS str3
>> FROM txttbl
>>
>> John 
>
0
John
5/21/2010 10:03:26 AM
Reply:

Similar Artilces:

How do I insert Roman Numerals?
New to Word 2007 would someone please explain to me how to insert Roman Numerlas in my document? "ckj" <ckj@discussions.microsoft.com> wrote in message news:6CF0C580-BA6E-463D-B4FD-E6A6FDD9E814@microsoft.com... > New to Word 2007 would someone please explain to me how to insert Roman > Numerlas in my document? Just type them... I II III IV V VI VII VIII IX X XI etc etc. Type a number e.g. 21, select it and run the following macro Dim oRng As Range Set oRng = Selection.Range oRng.Fields.Add oRng, 34, oRng.Text & " \*Roman"...

Not plot a data series in a chart but still display in data table
MS Excel 2007 I've somehow managed to display only two data series on my line chart. They still display in the data table. How did this happen? More importantly, how do I reverse it. I don't want to recreate chart. I see the data series not plotted on chart in the Chart Elements and when I select the series, the source data is highlighted; it's in the data table. How do I make it visible? Thanks! Miriam HAve you perhaps simply formatted the data series as "no line, no marker"? If so, you can format series, add some color. -- Best Regards, Lu...

Insert trigger
Looking for some advice on SQL 2005. I have a table that will usually be populated by an SSIS package. I want to set the "loaddate" column to the current time after a record is inserted. Should i do this via trigger or should i just build a step in the SSIS package to update the column after the file loads? If trigger is the way to go, what is the syntax to create the after insert trigger? Thanks in advance. You can create a default constraint on the table set to CURRENT_TIMESTAMP. That will handle the automatic date assignment without any need for coding. -...

Automatioc spill over data to a new Excel sheet from Xml source?
I have an XML source file with data for more than 256 columns. Since one sheet can contain a max of 256 cols, Is there a way to make Excel, create an additional sheet to keep the excess data? Preferrably using Xml map / template? ...

How do I get only the data I want to be shown?
I have approximately 2000 rows of data all with strange data in it. Each row starts with positions of stars in sexagesimal format. I want to filter through these so that I only have the ones I want (approx. 600), followed by the data in the row that corresponds to the position. I have the list of the ones I want in a seperate column (i.e. there positions). How do I get them? I hope this makes sense. Thanks! Two approaches come to mind. 1) Use an advanced filter. Click in the larger table and select Data > Filter > Advanced Filter. The 'database' should be the larger t...

How to retrieve data which contain "/" in it
Hi, I try to pull a data from access with contain a speacial character "/" in it but what i get is it not return a single data, so how do i wanna retrieve it? below is statement I use it but it return nothing. SELECT LDDATA.COR, LDDATA.CON, LDDATA.PCODE, LDDATA.QTY, LDDATA.BOXNO, LDDATA.CASENO, LDDATA.RSNOS, LDDATA.RSNOE, LDDATA.NOFRS FROM LDDATA WHERE (((LDDATA.COR)="A11/LC/000002/11/06")) ORDER BY LDDATA.CON, LDDATA.BOXNO; On Tue, 22 May 2007 21:53:02 -0700, osmanar <osmanar@discussions.microsoft.com> wrote: >Hi, >I try to pull a data from access with...

How do I prevent duplicate entry of a sku in a data table?
I want to keep folks from entering the same sku into a table (i.e. like data validation does for other parameters). Assume that you want to restrict the users from typing the duplicate values in A Column. Place the cursor in A1 cell and press Cntrl+Spacebar which will selecte the A Column. Keep in mind that the active cell should be A1 cell. (Active cell will have a white background after selection also) Data >>Validation>>Settings>>Validation Criteria>>Allow>>Custom>> and in Formula paste this =COUNTIF(A:A,A1)=1 Data>>Validatio...

Grouping Data
I have a subform that shows a usual set of data such as: Name1 Product1 Name1 Product2 Name1 Product3 Name2 Product4 The client wants me to be able to do the following: Name1 Product1 Product2 Product3 Name2 Product4 I.e. merging data cells where they are the same value Preferably with Name1 being centered across the products. Within a straight access form I would use a group via crosstab however this is an access adp on SQL Server. Can anyone help? In a report you can hide duplicates. -- KARL DEWEY Build a little - Test a little "BecksWatkinson@gmail.com...

inserting hrs and minutes
I have a cell in my time card that displays total weekly time -ex- "40:15" is there a way to make it more like this...40hrs,15mins -- Message posted from http://www.ExcelForum.com Use a custom format hh"hrs",mm"mins" -- Regards, Peo Sjoblom "-Brian-H- >" <<Brian-H-.110wgs@excelforum-nospam.com> wrote in message news:Brian-H-.110wgs@excelforum-nospam.com... > I have a cell in my time card that displays total weekly time -ex- > "40:15" is there a way to make it more like this...40hrs,15mins ? > > > ...

insert an interactive excel file into word web page
I'm trying to insert a excel file into a word document with text, and then save it was as a web page, but I want to keep the excel part interactive. Any ideas? ...

Updating a table
I have query that relates to a table and i want to be able to automatically insert a comment into a range. I.E Table column is called Report name. I want to open the query and have it ask me the name of the report. When i enter it i want it to auutomatically enter the name in the column so that all the other information that i add afterweards is associated with the report. I then want this info to save when i exit. Can anybody help? ...

Insert | File > Attachmnet-Button Drop Down ;What is the difference between Insert and Insert As Attachmnet
re: "Outlook2003, File-Insert-Options" On making new-email with Attachment-File(s), ** File Menu | Insert | File >>> (Brows and select File to insert ) then we can see the button "Insert", and write side Drop Down Arrow lower-right side of Dialog Box; If it clicked, we can see three options as follows: ** Insert Insert as text Insert as Attachment I can not recognize/understand the difference between "Insert" and "Insert as Attachment" *** What is the difference between Insert and Insert As Attachment ? I would appreciate y...

Inserting Hyperlinks in a Protected Sheet
Hi I run Excel 2000 and I have a protected worksheet that I share wit users in my organisation. I want to allow the insertion of a hyperlin to a specific file type within a specified directory on our server. 3 Questions: 1.Protection on disables the insert hyperlink command. Can this b overcome with worksheet activate code? 2.Can I limit the types of files (preferably by requiring the file t meet a mask format eg "z-*.xls")? 3.Can I limit the directory that can be linked, by referring to pathname stored in a cell on the active sheet? Would appreciate your suggestions. Thanks S...

Insert with a where condition
Hi, sql 2005 I have an insert statement that is ignoring the where condition. That is, I want to insert records when they do not already exist in the destination table. INSERT INTO dbo.tblmnuGroupPerm ( gId ,mtfID ,... ) SELECT @gID ,mtfID ,... FROM dbo.locmnuTabFunction AS ltf WHERE ltf.mtfID NOT IN ( SELECT gp.mtfID FROM dbo.tblmnuGroupPerm AS gp WHERE gp.gId=@gID AND gp.Deleted=0 ) Any ideas or recommendations appreciated :-) Many thanks, Jonathan It's OK... <oops "redFace">I did not correctly se...

showing data values on chart
in excel when mouse stand on chart,excel is showing the data values of that point.how can to do this action in access? Exactly the same. When you hover the mouse over a data point for a little amount of time, a yellow bow should come up describing what's in your data point. -- Regards, Steve "afshin" wrote: > in excel when mouse stand on chart,excel is showing the data values of that > point.how can to do this action in access? Where you insert chart?i insert chart on report,and i can't to see data value on data point. "Steve"...

How to copy specific columns until end of data
I'm trying to import data from 2 workbooks into a summary (combination) workbook. The trouble I'm having, is I want to import only certain columns and have excel know to stop at the last row with data. For example, from Workbook1 I want to import columns A, B, C, F, and G (from Sheet2). I want it to copy up to the last row containing any data--right now that's row 1046, but next month it could be row 1267. In workbook2 I'm fine with it copying the CurrentRegion--all columns are important. BUT, it doesn't include the title/category heads in the first row (Salesperson, ...

Calculation of weather data help
Hi, I have a data file (at http://www.weatherweb.net/cliall.dat) which contains daily weather data from 1881 in the format: dd,mm,yy,max temp,min temp, rainfall, sunshine, wind speed, wind gust, pressure What I would like to do is to calculate means for max, min, wind speed, wind gust and pressure, and also total rainfall and sunshine for each month. I would like the output in the format: mm,yy,mean meax, mean min, total rain, total sunshine, mean wind, mean wind gust, mean pressure Can anyone give any assistance as to how I might do this in excel to create a sngle file contain all th...

"Lookup" data type
I´m using the deployment manager to create a new lookup filed chema but when I click on the dropdown box to select the data type, the "lookup" data type doesn´t appear. I´ve got the customization manual and that data type is supposed to appear in the list. How can I add it? Deployment Manager in CRM 1.* does not let you add a Lookup data type "Talei" <Talei@discussions.microsoft.com> wrote in message news:4FE8843C-3BF5-48FC-AE20-16EB2B3FA914@microsoft.com... > I�m using the deployment manager to create a new lookup filed chema but when > I ...

Insert
I want to overtype in a Publisher text box. I find I can only insert. The "insert" key doesn't do anything. ...

excel data to outlook
Hi, In my daily job, I need to flash some new data into my excel and use the excel result to send out mails to report daily result. Is all numbers I need to report in mails. Wonder if you can tell me if there is any tools to make the data into text on my outllook. I tried forming some string using: ="Today we booked "&Sheet1!A4&" MM vs."&Sheet1!A5&" MM expiry" but when I copied this cells into my outlook, format is rare and pasted as table / cells where I prefer to paste as text format ( which can be read by boss with blackberry). I asked ar...

Editing data from 2 different tables
Hi All, Could someone please explain to me if it is possible to edit data from 2 different tables that are being showed in the same form? I created a form which shows fields from 2 different tables and I can not edit the fields. I would like to be able to change/include data using my form. Thank you. The two tables need to be related in some manner and joined in a query for the RecordSource of your form. Elaine Parlato wrote: >Hi All, >Could someone please explain to me if it is possible to edit data from 2 >different tables that are being showed in the same form? > >I cr...

Exporting Data From Query Into Excel
How can I have this process automatically done to have the data from a query after it's been ran to be exported into Excel in a specified location? -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 I have done some research on this method. While this method looks promising, there's one limitation. It states one MUST leave the "Range" argument as blank, when exporting data. The other thing I'm looking for, putting data from a query into a worksheet, which the name of the worksheet would be different from the name of the query. Based on the various things ...

how to insert data in a table
Hi Exprets; I am creating an access database in which I want to insert data in already created table. Kindly help. Regards, Vikky Vikky <love.excel@gmail.com> wrote in news:1194124711.012302.269990 @e34g2000pro.googlegroups.com: > Hi Exprets; > > I am creating an access database in which I want to insert data in > already created table. > > Kindly help. > > Regards, > > Vikky > Data from where? Do you want to import it from excel, from a text file, copy it from another table or type it in manually? -- Bob Quintal PA is y I've altere...

Uploaded Checking acct data
Using 2005 Just successfully uploaded all 2004 (YTD) for main checking account. That said, why does the calender not reflect the payments? My calendar shows nothing just "Add New" Is there some steps that I need to do or is the calendar information updated only if I, step-by-step, pay bills in the system? TIA Dennis The data that you have uploaded will be in the account register, not in the bills/deposits area which populates the calendar. To get it in the calendar, you'll need to add it to the bills/deposits area. You can do this by either entering it manually or by...

How do you insert page numbers larger than 1000?
I have my purchase orders set up as a Publisher document. When our organization upgraded from Publisher 2000 to Publisher 2002, the new version set parameters on the page numbers. This was one of those things that worked just fine in the previous version... Does anyone know how to turn it off or change it? Hi mregen (mregen@discussions.microsoft.com), in the newsgroups you posted: || I have my purchase orders set up as a Publisher document. When our || organization upgraded from Publisher 2000 to Publisher 2002, the new || version set parameters on the page numbers. This was one of those...