Post reply

Insert Query

  • 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

  • April 22, 2009 at 10:57 pm

    #982654

    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

  • April 22, 2009 at 11:22 pm

    #982662

    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

  • April 22, 2009 at 11:58 pm

    #982667

    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...

  • April 23, 2009 at 2:03 am

    #982695

    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

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

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