August 16, 2010 at 7:15 am
Hi ,
I am trying to split characters from a row and insert them one by one in new rows .
I am inserting data from a csv file first .Then using substring function for populating the temp table .
Now for ex : i have data like
code vlcode
1200 T
1210 AB
1220 XYZ
I want this data to inserted as
code vlcode
1200 T
1210 A
1210 B
1220 X
1220 Y
1220 Z
There is a composite primary key on these two columns
I am using following code for this , but i am able to insert only one character.
I have attched the tabel structure and sample data .
If you need anything else , i will do the needful .
CREATE Table Temp_vl
(code varchar(20),Vlcode varchar(20))
insert into Temp_Vl (code,Vlcode)
select SUBSTRING(col_verr,1,7),LTRIM(RTRIM(REPLACE(SUBSTRING(col_verr,73,16),'.',' '))) from dump
where SUBSTRING(col_verr,73,16) LIKE '%[a-z]%' AND SUBSTRING(col_verr,73,16) != ' '
--select * from Temp_vl
DECLARE @countOfChar int
DECLARE @RowCntForOuterLoop int
DECLARE @MaxRows int
DECLARE @rcForInnerLoop int
DECLARE @count int
Declare @STR as Varchar(50)
Select @RowCntForOuterLoop = 1
SELECT @rcForInnerLoop = 1
select @MaxRows = COUNT(*) from Temp_vl
--- To count number of characters
Select @STR=REPLACE((LTRIM(RTRIM(Vlcode))),' ','') from Temp_vl
SELECT @countOfChar = LEN(@str)from Temp_vl
--select REPLACE((LTRIM(RTRIM(Vlcode))),' ','')from Temp_vl
--- Outer loop which gets every new row from table
WHILE @RowCntForOuterLoop <= @MaxRows
BEGIN
--- Inner Loop which gets single characters
WHILE @rcForInnerLoop <= @countOfChar
BEGIN
INSERT INTO vl (code ,Vlcode)
SELECT CBVcode ,SUBSTRING(Vlcode ,@rcForInnerLoop,1) From Temp_vl
SET @rcForInnerLoop = @rcForInnerLoop + 1
END
---SET @countOfChar = @countOfChar + 1
-- print @countOfChar
SET @RowCntForOuterLoop = @RowCntForOuterLoop + 1
END
Thanks in advance
August 16, 2010 at 8:47 am
Hi ,
Have i missed anything here while posting , please let me know if that is the case...
Regards
aditya
August 16, 2010 at 8:57 am
Here is the proper sample data
CREATE Table VL
(Code varchar(20) ,not null PRIMARY KEY clustered
vlcode varchar(20) not null PRIMARY KEY clustered)
--===== Insert the test data into the test table
INSERT INTO Vl
(code ,vlcode)
SELECT '334612A ','T' UNION ALL
SELECT '334616 ','ABC' UNION ALL
SELECT '334616 ','PST4' UNION ALL
SELECT '334616 ','VRS' UNION ALL
SELECT '334616 ','DDR' UNION ALL
SELECT '334616 ','TUH89' UNION ALL
SELECT '334616 ','SS' UNION ALL
SELECT '334616 ','P'
August 16, 2010 at 9:07 am
I didn’t check your code. Instead here is a demonstration of how to get all the characters in one select statement (you can use it later on in insert-select statement).
use tempdb
go
--Creating the table that holds the string
CREATE Table Vl
(Code varchar(20) not null,
vlcode varchar(20) not null)
go
alter table Vl add constraint PK_Vl primary key clustered (Code, vlcode)
--===== Insert the test data into the test table
INSERT INTO Vl
(Code ,vlcode)
SELECT '334612A ','T' UNION ALL
SELECT '334616 ','ABC' UNION ALL
SELECT '334616 ','PST4' UNION ALL
SELECT '334616 ','VRS' UNION ALL
SELECT '334616 ','DDR' UNION ALL
SELECT '334616 ','TUH89' UNION ALL
SELECT '334616 ','SS' UNION ALL
SELECT '334616 ','P';
--I've used a CTE that creates the numbers, but if you have
--a table with numbers, you can use that table instead of the CTE.
--It works because for each record in Temp_vl table, you get many
--records ftom MyNumbers and in each one there is a different value
--for i. for each vlcode, the values of i are 1 to the number of characters that
--you have in Vlcode table, so each time you get a different charactar
--from Vlcode.
--If you don't understand how it works,you can unremark the line
--in the query in order to see the values of Vlcode, i and the length of Vlcode.
with MyNumbers as (
select 1 as i
union all
select i+1
from MyNumbers
where i < 12)
select vlcode, substring(vlcode,i,1)
--,Vlcode, i, len(Temp_vl.Vlcode)
from MyNumbers inner join Vl on MyNumbers.i <= len(Vl.vlcode)
order by vlcode, i
go
--Clean up
drop table Vl
By the way it is good that you write a code to create the table and insert data into it, but you should test it before posting it. Your code had syntax errors.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 16, 2010 at 9:33 am
Hi ,
Thanks for your suggestion .
It is something new for me ... I would definately like to implement this thing .You have also given a very good explanation ..
But unfortunately i am not able to understand all the things .Hence i cant implement it for now .
If you can help me in finding the loophole in my code , i would be very thankful ...
regards
Aditya
August 16, 2010 at 10:19 am
Nobody around !
August 16, 2010 at 1:15 pm
We can split out the 20 individual one character components using the SUBSTRING function and CROSS APPLY to pass vlcode into these values. Using the WHERE clause, we identify only the characters we need by checking the length of vlcode
SELECT vlcode, SplitChar
FROM Vl
CROSS APPLY
(
VALUES
(1, SUBSTRING(vlcode, 1, 1)),
(2, SUBSTRING(vlcode, 2, 1)),
(3, SUBSTRING(vlcode, 3, 1)),
(4, SUBSTRING(vlcode, 4, 1)),
(5, SUBSTRING(vlcode, 5, 1)),
(6, SUBSTRING(vlcode, 6, 1)),
(7, SUBSTRING(vlcode, 7, 1)),
(8, SUBSTRING(vlcode, 8, 1)),
(9, SUBSTRING(vlcode, 9, 1)),
(10, SUBSTRING(vlcode, 10, 1)),
(11, SUBSTRING(vlcode, 11, 1)),
(12, SUBSTRING(vlcode, 12, 1)),
(13, SUBSTRING(vlcode, 13, 1)),
(14, SUBSTRING(vlcode, 14, 1)),
(15, SUBSTRING(vlcode, 15, 1)),
(16, SUBSTRING(vlcode, 16, 1)),
(17, SUBSTRING(vlcode, 17, 1)),
(18, SUBSTRING(vlcode, 18, 1)),
(19, SUBSTRING(vlcode, 19, 1)),
(20, SUBSTRING(vlcode, 20, 1))
) AS Z (N, SplitChar)
WHERE N < LEN(vlcode) + 1
August 16, 2010 at 2:24 pm
Aspg (8/16/2010)
Nobody around !
Well, that's not really true...
I, personally, decided not to reply since you're asking for a fix to your loop solution knowing there are alternatives available.
So, instead of fixing your nested loop problem I'd like to recommend you to take the time and read the TallyTable article referenced in my signature. You'll find an excellent explanation of the basic split string concept Adi recommended.
The main reason I'm not going to promote the nested loop approach is performance. The second reason is readability and the third one is the general concept of data selection from a relational database.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply