Insert Query

Question

Post reply

Insert Query

satishthota

SSC Eights!

Points: 959
More actions
April 22, 2009 at 8:48 pm

#384423

1) It had total one string means In one string more than one owner,father name, rationcardno(may be or may not be) comes
2) In a string First Ownername(Aakula Anjaiah) second one is father name(S/o Veeraiah) and third one is RationCardno(Wap 6825011205) and again follows owner name/father name/rationcardno
Table:Temp
OwnerName/FatherName/RationCardnO
Aakula Anjaiah S/o Veeraiah Wap 6825011205 Aakula Sada Nandam S/o Veeraiah Aakula Veeraiah S/o Bhumaiah Wap Aakula Shivadeenam S/o Veeraiah Wap 7825011257
I had Temp Table with one column as OwnerName/FatherName/RationCardnO
I want to insert above values into 3 fields of Temp1 table
my Temp1 table as 3 columns
OwnerName
FatherName
RationCardNo
Plz give me a query to insert

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

hayzer Say Hey Kid Points: 713 More actions · Answer 1

it might be better if you could give sample table and data where we can work with...

but seeing the sample data you provided, it seems that there is no normal sequence where we can separate into fields.

i bet you are copying raw data coming from copyrighted material..:-P

Rajesh Jonnalagadda Ten Centuries Points: 1032 More actions · Answer 2

You need to have some seperator after one person details so that we can process data or insert into table

Try this,

this can be used when the column contains one person details per row

Insert Into TableName (OwnerName, RationNo, FatherName)

SELECT SUBSTRING(Col1,1,CHARINDEX('/',Col1) -3) AS OwnerName,

CASE WHEN ISNUMERIC(SUBSTRING(REVERSE(Col1),1,CHARINDEX(' ',REVERSE(Col1))-1)) = 1 THEN REVERSE(SUBSTRING(REVERSE(Col1),1,CHARINDEX('paw ',REVERSE(Col1))+3)) ELSE NULL END AS RationNo,

CASE WHEN ISNUMERIC(SUBSTRING(REVERSE(Col1),1,CHARINDEX(' ',REVERSE(Col1))-1)) = 1 THEN REVERSE(SUBSTRING(REVERSE(Col1),CHARINDEX('paw ',REVERSE(Col1))+3,CHARINDEX('/',REVERSE(Col1))-CHARINDEX(' ',REVERSE(Col1))-5))

ELSE REVERSE(SUBSTRING(REVERSE(Col1),1,CHARINDEX('/',REVERSE(Col1))-2)) END AS FatherName

FROM(

SELECT [OwnerName/FatherName/RationCardNo] AS Col1

FROM SubTest

) A

kokkula SSC-Addicted Points: 495 More actions · Answer 3

Hello,

If all values are in a single row then there should be a seperator between each details. Here you did not mentioned that. Space connot be a seperator in your case. If you let know the detail seperator then its helpful in solving ur issue.

Hope its clear...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Please can you supply a source table create script with inserts for a few rows? If you're unsure about how to do this, read the link in my sig.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden