August 25, 2008 at 10:36 am
here is my cursor for getting rows into a temp table ...
but the problem here is as i have my @sc varchar(8000) and i have many rows to be fetched i am getting only partial result what is that i can do .....
how can i insert single row into ttemp table and make it loop so that i can get all the results
Declare @tbl table(scode varchar(20))
Declare @sc varchar(8000)
declare ServiceCodes_Cursor Cursor
for
Select (Service_codes) from Table1(nolock)
where service_codes is not null--in( 'R6981')
open servicecodes_cursor
fetch next from servicecodes_cursor
into @sc
while @@fetch_status=0
Begin
IF ( Select Left(@sc,1) ) = ','
Select @sc = Substring(@sc, 2, Len(@sc) )
IF ( Select Right(@sc,1) ) = ','
Select @sc = Substring(@sc, 1, Len(@sc)- 1 )
WHILE ( CHARINDEX(',', @sc) ) > 0
Begin
insert into @tbl
select substring(@sc,1, CHARINDEX(',', @sc) - 1 )
Select @sc = Substring(@sc, CHARINDEX(',', @sc) + 1, 8000 )
End
fetch next from servicecodes_cursor into @sc
End
close servicecodes_cursor
deallocate servicecodes_cursor
select * from @tbl
-- select (scode),count(*) from @tbl
-- group by scode
-- order by 2 desc
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
August 25, 2008 at 10:54 am
A couple of things needed to help you....
1.) What result DO you get? It's kind of hard to know where to look for the problem if we don't even know what results you're getting and don't have any sample data to work with.
2.) Sample data. This is critical, as it's far too easy to assume that data meets a given criteria, only to find out that the actual data does not.
3.) Do some research - look at articles here on "Tally Table" by Jeff Moden. Such a technique can easily split delimited strings without a cursor.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2008 at 10:57 am
what exactly i need is to insert a value from a llocal variable into a temp table for each and evry record using Cursor can i know how can i do that ......
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
August 25, 2008 at 11:05 am
You haven't yet answered the questions posed before. A cursor is actually a lousy way to achieve your goal, and it's clear you haven't even made the attempt to research the tally table technique. You also haven't said exactly what results you're getting, so there's no way to know for sure that your string splitter is actually working correctly.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2008 at 11:09 am
HI Munson ...My Comma Splitter it is working i have already used it ..But now the results i am getting are some Codes like A435,b434,4kjf for each row --so i need to split it using that logic which works fine ...But as i have like 2000 records my variable is not taking all the records so i thought of using cursor so that for evry row once i split the data without comma then insert into temp table for each and evry rows instead of getting all thr rows at once into local variable ...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
August 25, 2008 at 11:32 am
Then the 8000 length you're giving your varchar variable is the limiting factor. As I indicated before, a cursor is a LOUSY way to achieve your objective. What data type and length is the source field?
I still think it will be far easier to use Jeff Moden's tally table string splitter, but either way, you may need to use varchar(MAX) as opposed to a specific number - provided you have at least SQL Server 2005.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2008 at 11:34 am
As i am using the sql server 2000 i cant set to max ...and the datatype of the column i am using is varchar(20) ....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
August 25, 2008 at 11:42 am
I'm not talking about the destination column, I'm referring to the source data column, where the original data is coming from. What size and type is that data column, BEFORE you put it's data into a single string?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2008 at 11:49 am
Srry i thought they it would be varchar(100) but it is varchar(4000) tats my source code datatype
Column name-- code varchar(4000) from table 1
so i am getting all the codes from table1
the codes are stored in comma separated format in that column ,so i am splitting those comma separated codes...i want all the codes to be displayed in a separate table for further querying it..but when i am storing these values in a variable i am unable to get total output as my variable has @sc varchar(8000) but my soucr column is varchar(4000) so what should i do now ...any alternative let me know ...is ther any way that i can use cursor and do that ....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
August 25, 2008 at 2:45 pm
Again, you need to look at Jeff Moden's tally table string splitter technique. It's set-based, so you just don't need a cursor for ANY reason. It's a tried and true method, and if you get stuck with it, post what you've tried and we can help fix it. The whole idea with the tally table is to have a table with nothing but numbers in it. In this case, the numbers up to the maximum length of any given input record, which apparently is 4000, so go to 5000 for good measure.
With the tally table created, a query that joins the tally table to the input table (Table1) without any JOIN keyword (just using commas) will have WHERE conditions to limit the output to the various substrings within the commas. It's probably THE best method for what you're trying to do. Please take the time to search for it in the forums here and go through it. What you'll learn is far more valuable than being handed a solution.
We'd much rather teach you to fish than hand you a fish, if you get my drift...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2008 at 2:54 pm
Yeah i went through it ..i have a ques does it work if i rename it as some other table name instead of dbo.tally ...is thats just the logic or even should i follow the name..
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
August 25, 2008 at 3:11 pm
Does this tally only applies for 2005 or can i make use of this in 2000 also ...PLz let meknow
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
August 25, 2008 at 3:22 pm
You can name it whatever you want. Just remember to edit the name as appropriate when you go in to adapt Jeff's code that references it. The tally table technique works in any version, just watch out for items that are SQL 2005 or above only, like CTE's (common table expressions - these begin with the keyword WITH - these can just become derived tables), ROW_NUMBER() functions, etc. All of those things can be modified to work with SQL 2000. The main thing is to get a table of numbers to exist, with the numbers from 1 to at least 5000. If you have to, you can insert the records in that table using a WHILE loop.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2008 at 3:28 pm
but when i executed this query in my DB i am not getting any output
--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = 'Element01,Element02,Element03,Element04,Element05'
--===== Create a table to store the results in
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(8000) --The string value of the element
)
--===== Add start and end commas to the Parameter so we can handle
-- single elements
SET @Parameter = ','+@Parameter +','
--===== Join the Tally table to the string at the character level and
-- when we find a comma, insert what's between that command and
-- the next comma into the Elements table
INSERT INTO @Elements
(Value)
SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)
FROM dbo.Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the comma
--
SELECT * FROM @Elements
--
i have taken this from jeff article
http://www.sqlservercentral.com/articles/TSQL/62867/
but i couldnt get the output o dont know why ??
could you please refer this even i created tally table tooo...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
August 25, 2008 at 3:36 pm
It's too late for me to look at it today, but I'll look at it in the morning and see what I can accomplish.
EDIT: Ignore that... I just found my tally table creation code. Look at the following:
-- Create an auxiliary table of numbers - method from T-SQL Querying by Itzik Ben-Gan
DECLARE @TALLY TABLE (N INT NOT NULL PRIMARY KEY)
DECLARE @max-2 AS INT, @rc AS INT
SET @max-2 = 5000
SET @rc = 1
INSERT INTO @TALLY VALUES(1)
BEGIN
INSERT INTO @TALLY SELECT N + @rc FROM @TALLY
END
INSERT INTO @TALLY
SELECT N + @rc FROM @TALLY WHERE N + @rc <= @max-2
--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = 'Element01,Element02,Element03,Element04,Element05'
--===== Create a table to store the results in
DECLARE @Elements TABLE (
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(8000) --The string value of the element
)
--===== Add start and end commas to the Parameter so we can handle single elements
SET @Parameter = ','+@Parameter +','
--===== Join the Tally table to the string at the character level and
-- when we find a comma, insert what's between that command and
-- the next comma into the Elements table
INSERT INTO @Elements (Value)
SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)
FROM @TALLY
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the comma
SELECT * FROM @Elements
And here's the result set I get:
NumberValue
1Element01
2Element02
3Element03
4Element04
5Element05
See how easy this is?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply