how to split a row item n insert it in another table

[vikvikash]

hi
i have split the data using the split command now i want to insert the split data to be inserted into another table. there r many records in the previous table i want each record to be split and inserted into the other table one by one.....



25 123454756741 $GPRMC,093729.0 00,A,1301.7595, N,08015.2751,E, 1.13,69.84,0802 10,,*3E,BAUTO 0 3/9/2010 3:07:29 PM

i want to split the underlined data using "," and insert it into another table...
there r many such records i want to split each and every records and insert it to another table

[Soniad]

Hi,

In SQL u can use CHARINDEX to split string.

check the following link :

http://www.databasejournal.com/features/mssql/article.php/3071531/Using-SQL-Servers-CHARINDEX-and-PATINDEX.htm

Regards,

"D"

[vikvikash]

Hi there,
i have split the underlined text using c# code now what i want is to insert it to another table but there would be several such records.
i want to split each record n insert it to another table.

[ck9663]

Is the text from a table? Or a form?

~~ CK

[vikvikash]

Hi CK

The text is from a table n not a form.

[ck9663]

Here's a pseudo-code...

1.. Parse your string on your SELECT statement using the comma as delimiter/identifier and the dollar sign as the marker to start the parsing.
2.Insert it using the INSERT INTO TABLE .... SELECT col1, ParsedColumnAli as FROM YourTable.

Happy Coding!!!

~~ CK

[nbiswas]

Solution to how to split a row item n insert it in another table

Try this:

Solution 1(Using XQuery Approach)

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
DECLARE @tblDestination(SplittedRecords varchar(100))



SET @str = '25 123454756741 $GPRMC,093729.000,A,1301.7595,N,08015.2751,E,1.13, 69.84,080210,,*3E,BAUTO 0 3/9/2010 3:07:29 PM'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
INSERT INTO @tblDestination
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)

OUTPUT

25 123454756741 $GPRMC
093729.000
A
1301.7595
N
08015.2751
E
1.13
69.84
080210

*3E
BAUTO 0 3/9/2010 3:07:29 PM

Hope this helps