hi..i need help for my exam, I retrived data from my sms inbok and
insert to table.The record just like this:
ID1,12x100,56x12,90x90..,how to pars the table in to:
ID First Second
ID1 12 100
ID1 56 12
ID1 90 90
Thank for your help...
===================
Using a table of numbers
http://sqlserver2000.databases.aspfaq.com/why-should-i-consi der-using-an-auxiliary-numbers-table.html
CREATE TABLE MyTable(ID VARCHAR(4), Data VARCHAR(100))
INSERT INTO MyTable(ID, Data) VALUES('ID1','12x100,56x12,90x90')
SELECT ID,
PARSENAME(REPLACE(Data,'x','.'),2) AS First,
PARSENAME(REPLACE(Data,'x','.'),1) AS Second
FROM (
SELECT ID,
SUBSTRING(Data ,
Number,
CHARINDEX(',' ,
Data+',' ,
Number)-Number) AS Data
FROM MyTable
INNER JOIN Numbers ON Number BETWEEN 1 AND LEN(Data)+1
AND SUBSTRING(','+Data,Number,1) = ',') X
ORDER BY ID
you the best ,thx.
if data inserted to table about 100 data/sec,is the script run well in
this condition, and no data lost or error??
neolempires2@gmail.com wrote:
> if data inserted to table about 100 data/sec,is the script run well in
> this condition, and no data lost or error??
The best way to find out is to actually set up a test.
I've seen the following method used in production systems; it avoids
forcing the insert process to wait for an immediate response, and also
allows trying again later in case of problems.
* insert the data into an intermediate table with a column indicating
order of insertion
* separate process, launched on a schedule or by a human operator:
- note the most recent record that exists
- process all rows up to and including that one, either deleting
them or marking them as already processed
- any rows inserted mid-process are left for the next launch