none
Insert data into a SQL table from another SQL table which has data in a specific format .

    Question

  • Please help me with the below scenario .

    Table "A":

    Part#              shippinginfo1                           

    ABC12             1:9.99:5.99|2:11.99:7.99                                   

    DEF34             4:3.44:5.44|6:8.99:9.99

    I want to populate Table "B" from Table "A" as follows :

    Part#         ship_method      ship_cost               handg_cost                     

    ABC12        1                         9.99                          5.99                              

    ABC12        2                        11.99                          7.99

    DEF34        4                         3.44                           5.44

    DEF34         6                        8.99                            9.99

    Wednesday, September 11, 2013 6:08 PM

Answers

  • Check this article (bottom part):

    http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/passing-multiple-ranges-to-stored-proced


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by guest369 Thursday, September 12, 2013 7:12 PM
    Wednesday, September 11, 2013 10:36 PM
    Moderator
  • create table A (Part# varchar(50), shippinginfo1 varchar(50) )
    Insert into A values ('ABC12','1:9.99:5.99|2:11.99:7.99')  ,('DEF34','4:3.44:5.44|6:8.99:9.99')                                                
     
     SELECT distinct Part#,
      S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS ship_method  ,
      S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS ship_cost,
        S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS handg_cost
     
    FROM
    (
    SELECT *,CAST (N'<H><r>' + Replace(REPLACE(shippinginfo1, ':', '</r><r>'),'|','</r><r>') + '</r></H>' AS XML) AS [vals]
    FROM A) d
     
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
     UNION ALL
      SELECT distinct Part#,
     
    	 S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS splitVal4,
    	  S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS splitVal5,
    	   S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS splitVal6
    FROM
    (
    SELECT *,CAST (N'<H><r>' + Replace(REPLACE(shippinginfo1, ':', '</r><r>'),'|','</r><r>') + '</r></H>' AS XML) AS [vals]
    FROM A) d
     
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
    Order By Part#, ship_method
     
     drop table A

    • Marked as answer by guest369 Thursday, September 12, 2013 7:12 PM
    Thursday, September 12, 2013 6:55 PM
    Moderator

All replies

  • Try this,

    create table #tableA(Part#        varchar(100),      shippinginfo1 varchar(100))
    
    insert into #tableA values( 'ABC12','1:9.99:5.99|2:11.99:7.99')
    ,('DEF34','4:3.44:5.44|6:8.99:9.99')
    
    ;with cte as
    (
    select Part#,items,STUFF(items,charindex(':',items),len(items),'')as ship_method      
    ,STUFF(items,1,charindex(':',items),'') cost
     from #tableA 
    cross apply dbo.Split(shippinginfo1,'|')
    )
    select Part#,ship_method,STUFF(cost,charindex(':',cost),len(cost),'')ship_cost
    ,STUFF(cost,1,charindex(':',cost),'') handg_cost from cte

    The code for split function can be taken from http://stackoverflow.com/questions/14911167/split-function-in-sql-server-2008


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, September 11, 2013 6:49 PM
  • CREATE FUNCTION dbo.Split
    (
    	@RowData nvarchar(2000),
    	@SplitOn nvarchar(5)
    )  
    RETURNS @RtnValue table 
    (
    	Id int identity(1,1),
    	Data nvarchar(100)
    ) 
    AS  
    BEGIN 
    	Declare @Cnt int
    	Set @Cnt = 1
    	While (Charindex(@SplitOn,@RowData)>0)
    	Begin
    		Insert Into @RtnValue (data)
    		Select 
    			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
    		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    		Set @Cnt = @Cnt + 1
    	End
    	
    	Insert Into @RtnValue (data)
    	Select Data = ltrim(rtrim(@RowData))
    	Return
    END

    I used the above split function and output didn't get as expected.
    • Edited by guest369 Wednesday, September 11, 2013 7:21 PM
    Wednesday, September 11, 2013 7:21 PM
  • Please help me with the below scenario.

    Table "A":

    Part#      shippinginfo1

    ABC12     1:9.99:5.99|2:11.99:7.99

    DEF34      4:3.44:5.44|6:8.99:9.99

    I want to populate Table "B" from Table "A" as follows :

    Part#           ship_method           ship_cost            handg_cost

    ABC12                 1                          9.99                    5.99

    ABC12                 2                          1 1.99                 7.99

    DEF34                  4                         3.44                    5.44

    DEF34                  6                         8.99                     9.99

    Wednesday, September 11, 2013 10:22 PM
  • Check this article (bottom part):

    http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/passing-multiple-ranges-to-stored-proced


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by guest369 Thursday, September 12, 2013 7:12 PM
    Wednesday, September 11, 2013 10:36 PM
    Moderator
  • Did you try the sample data posted by me?

    If this doesnt work for the actual data, you may need to tweak the query further, as we dont aware of the actual data.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 12, 2013 1:03 AM
  • Duplicate thread http://social.msdn.microsoft.com/Forums/en-US/b0434ea4-4a49-4290-8958-dde201b5919c/insert-data-into-a-sql-table-from-another-sql-table-which-has-data-in-a-specific-format-

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 12, 2013 1:04 AM
  • Yes, I tried with the sample data you posted it didn't work. I already replied to your post. I used the below split function. Please advise. 

    CREATE FUNCTION dbo.Split
    (
    	@RowData nvarchar(2000),
    	@SplitOn nvarchar(5)
    )  
    RETURNS @RtnValue table 
    (
    	Id int identity(1,1),
    	Data nvarchar(100)
    ) 
    AS  
    BEGIN 
    	Declare @Cnt int
    	Set @Cnt = 1
    	While (Charindex(@SplitOn,@RowData)>0)
    	Begin
    		Insert Into @RtnValue (data)
    		Select 
    			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
    		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
    		Set @Cnt = @Cnt + 1
    	End
    	
    	Insert Into @RtnValue (data)
    	Select Data = ltrim(rtrim(@RowData))
    	Return
    END

    Thursday, September 12, 2013 3:10 AM
  • Did you see my response and did you try XML solution posted in that article?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, September 12, 2013 5:21 PM
    Moderator
  • create table A (Part# varchar(50), shippinginfo1 varchar(50) )
    Insert into A values ('ABC12','1:9.99:5.99|2:11.99:7.99')  ,('DEF34','4:3.44:5.44|6:8.99:9.99')                                                
     
     SELECT distinct Part#,
      S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS ship_method  ,
      S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS ship_cost,
        S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS handg_cost
     
    FROM
    (
    SELECT *,CAST (N'<H><r>' + Replace(REPLACE(shippinginfo1, ':', '</r><r>'),'|','</r><r>') + '</r></H>' AS XML) AS [vals]
    FROM A) d
     
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
     UNION ALL
      SELECT distinct Part#,
     
    	 S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS splitVal4,
    	  S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS splitVal5,
    	   S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS splitVal6
    FROM
    (
    SELECT *,CAST (N'<H><r>' + Replace(REPLACE(shippinginfo1, ':', '</r><r>'),'|','</r><r>') + '</r></H>' AS XML) AS [vals]
    FROM A) d
     
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
    Order By Part#, ship_method
     
     drop table A

    • Marked as answer by guest369 Thursday, September 12, 2013 7:12 PM
    Thursday, September 12, 2013 6:55 PM
    Moderator
  • @Naomi.. Yes, I tried it and perfectly worked. Good article. Thanks .

    Also.. Thanks Li..

    Thursday, September 12, 2013 7:12 PM