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

• hayzer

  Say Hey Kid

  Points: 713

  More actions

  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

• Rajesh Jonnalagadda

  Ten Centuries

  Points: 1032

  More actions

  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

• kokkula

  SSC-Addicted

  Points: 495

  More actions

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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  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