Data Splitting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Data Splitting

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2011-01-03 : 02:59:41

Hi,

I have a table A as mentioned below

Table_A


ColA
1|E001|Test1
2|E002|Test2
3|E003|Test3
4|E004|Test4

I need to split the colA into a tableB which has a column sno,empid,empname

I used function to split a column ColA into three different values
and storing the same into TableB

In peformance wise the function is too slow.

Can i have any alternative way to use the data split concept?

Sachin.Nand

2937 Posts

Posted - 2011-01-03 : 03:14:59

Why was the table designed that way in the first place ?

Also post your funtion so that someone does not posts the same function which for you is already to slow.

PBUH

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-03 : 03:27:30

Check the following code .. hope this helps!

Select
SUBSTRING(ColA,1,Charindex('|',ColA)-1) sno
,SUBSTRING(ColA,Charindex('|',ColA)+1,(Charindex('|',ColA,CHARINDEX('|',ColA)+1))-(Charindex('|',ColA)+1)) empid
,Reverse(SubString(REVERSE(ColA),1,Charindex('|',REVERSE(ColA))-1)) empname
from Table_A

Cheers!

Kristen
Test

22859 Posts

Posted - 2011-01-03 : 04:32:10

"Why was the table designed that way in the first place ?"

Maybe its just a staging table. If this was a one-off fix I doubt performance would be a concern.

I agree with MIK_2008 that if you are currently using a "general purpose" splitter function the SUBSTRING / CHARINDEX method - whilst it looks like "miles and miles of code" - will probably be a lot faster.

If you are sure that you always have XXX|XXX|XXX pattern you can code the solution to avoid having to use the REVERSE function, (which I think will be a bit faster), but try this first and see how it goes

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-03 : 04:39:22

If the pattern is same, use parsename function

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-01-03 : 05:02:40

I have asked a related question here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=154732

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2011-01-03 : 06:08:31

whether parsename function will work with pipe simple?..

could you show some example?

quote:
Originally posted by madhivanan

If the pattern is same, use parsename function

Madhivanan

Failing to plan is Planning to fail

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-03 : 06:30:27

I hav an idea, to insert datas from text file to table.

For example:-
Create one text file format '.cvs'
FileName:-
********
sampledata.cvs
--------------

1|E001|Test1
2|E002|Test2
3|E003|Test3
4|E004|Test4

and write one procedure.

Example:-
CREATE PROCEDURE proceName (@upd nvarchar(300))
AS BEGIN
SET NOCOUNT ON;
SET @sql = N'BULK INSERT tableB FROM ''' + @upd + '''
WITH(FIRSTROW = 2,DATAFILETYPE=''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''\n'');';
EXEC(@sql);
END;

Execute this statement:-
**********************
EXEC proceName'C:\sampledata.cvs'

***end****
This above procedure to insert bulk datas in single..

this is one way to can load data from take to table.. time taken is very less..

Just give a try..!

Regards,
MAG
SQL newer

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2011-01-03 : 06:48:09

hi MageshkumarM,
thanks for your imediate reply..

our scenario is to move from one table to another table within
the database..can you give some idea about how to do the same
process by replacing the csv into sql table (as a source)..

quote:
Originally posted by MageshkumarM

I hav an idea, to insert datas from text file to table.

For example:-
Create one text file format '.cvs'
FileName:-
********
sampledata.cvs
--------------

1|E001|Test1
2|E002|Test2
3|E003|Test3
4|E004|Test4

and write one procedure.

Example:-
CREATE PROCEDURE proceName (@upd nvarchar(300))
AS BEGIN
SET NOCOUNT ON;
SET @sql = N'BULK INSERT tableB FROM ''' + @upd + '''
WITH(FIRSTROW = 2,DATAFILETYPE=''char'', FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''\n'');';
EXEC(@sql);
END;

Execute this statement:-
**********************
EXEC proceName'C:\sampledata.cvs'

***end****
This above procedure to insert bulk datas in single..

this is one way to can load data from take to table.. time taken is very less..

Just give a try..!

Regards,
MAG
SQL newer

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-03 : 07:22:48

where did you loading the datas..

for Table_A

Regards,
MAG
SQL newer

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-03 : 07:30:27

replace the column name, table name and destination table name as per your requirement in the below code and check the performance if its ok in your case e.g. table to table conversion within SQL server

Select
SUBSTRING(ColA,1,Charindex('|',ColA)-1) sno
,SUBSTRING(ColA,Charindex('|',ColA)+1,(Charindex('|',ColA,CHARINDEX('|',ColA)+1))-(Charindex('|',ColA)+1)) empid
,Reverse(SubString(REVERSE(ColA),1,Charindex('|',REVERSE(ColA))-1)) empname
Into [Databasename].Dbo.[TableName]
from Table_A

Cheers!

Kristen
Test

22859 Posts

Posted - 2011-01-03 : 07:39:29

quote:
Originally posted by sent_sara

hi MageshkumarM,
thanks for your imediate reply..

our scenario is to move from one table to another table within
the database..can you give some idea about how to do the same
process by replacing the csv into sql table (as a source)..

Why? Did you try MIK_2008's suggestion?

"whether parsename function will work with pipe simple?..
could you show some example?"

See the link I posted to the other thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=154732

Kristen
Test

22859 Posts

Posted - 2011-01-03 : 07:50:41

quote:
Originally posted by MageshkumarM

where did you loading the datas..

for Table_A

I'm assuming you are suggesting "splitting" the data during the import from a file? Personally I would not do it that way. IMHO better to import the data "as is" to a staging table then sort it out.

What if there is some incorrect data in that column? An extra "|" character, or one missing? The split-during-import is going to either error or convert it wrongly. Much better to import the data "raw", with as little validation as possible, and then all the various scenarios that need to be catered for (either now or, more often IME, as they are encountered in the real-world over time) can be accommodated purely within the SQL which can be made fault-tolerant / robust.

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-03 : 08:36:30

Hi,
This procedure using to split the datas, from the table in a column data..

Procedure:-
*********

Create PROCEDURE [dbo].[Update_Split]
AS
BEGIN
SET NOCOUNT ON;

Declare @str varchar(300),@x int,@id varchar(20),@empid varchar(30), @empname varchar(50), @len int;

Declare cursor_code cursor FOR
select COL from datas;

OPEN cursor_code
Fetch NEXT from cursor_code into @str

while @@FETCH_STATUS = 0
begin
set @x = (select CHARINDEX('|',@str));
set @id = SUBSTRING(@str,0,@x);
set @str = SUBSTRING(@str,@x+1, LEN(@str))
set @x = (select CHARINDEX('|',@str));
set @empid = SUBSTRING(@str,0,@x);
set @empname = SUBSTRING(@str,@x+1, LEN(@str))
insert into col_splits values(@id,@empid,@empname);
print 'inserted'
Fetch NEXT from cursor_code into @str
end
close cursor_code
deallocate cursor_code

print ' updated';
end

Execute:-
********
EXEC Update_Split

Table_A
*******
1|E001|Test1
2|E002|Test2
3|E003|Test3
4|E004|Test4

After procedure execute...

Output:-
TABLE_B
*******
id empid empname
1 E001 Test1
2 E002 Test2
3 E003 Test3
4 E004 Test4
-----END------
I created single update procedure to split all data from the TABLE_A to update in TABLE_B

could u try this...

Regards,
MAG
SQL newer

Kristen
Test

22859 Posts

Posted - 2011-01-03 : 11:31:42

The use of the CURSOR will be why your Procedure is slow.

Are you having some difficulty using the example that MIK_2008 gave you?

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2011-01-04 : 01:55:22

Better this query to solve this problem..

Select
SUBSTRING(ColA,1,Charindex('|',ColA)-1) sno
,SUBSTRING(ColA,Charindex('|',ColA)+1,(Charindex('|',ColA,CHARINDEX('|',ColA)+1))-(Charindex('|',ColA)+1)) empid
,Reverse(SubString(REVERSE(ColA),1,Charindex('|',REVERSE(ColA))-1)) empname
Into [Databasename].Dbo.[TableName]
from Table_A

Regards,
MAG
SQL newer

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-04 : 02:26:37

quote:
Originally posted by MageshkumarM

Better this query to solve this problem..

Select
SUBSTRING(ColA,1,Charindex('|',ColA)-1) sno
,SUBSTRING(ColA,Charindex('|',ColA)+1,(Charindex('|',ColA,CHARINDEX('|',ColA)+1))-(Charindex('|',ColA)+1)) empid
,Reverse(SubString(REVERSE(ColA),1,Charindex('|',REVERSE(ColA))-1)) empname
Into [Databasename].Dbo.[TableName]
from Table_A

In other words, the query that MIK_2008 posted earlier?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources