Transfer data from one SQL Server table to another

Question

For example I have one SQL Server database that contains a table

Id          Address
1  England,London,someaddress
2  Germany,Berlin,someaddress2

And I have another SQL Server database that contains following table with scheme

 Id  Country City Address

I need transfer data from first database to second. Like this.

id  Country    City    Address
 1  England   London   someaddress
 2  Germany   Berlin   someaddress2

How can I do that?

Answer 1

You can do that by creating a User Define Function that detect Country , City, Address by Splitting and than insert into second table.

Split Function :

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (id int , items varchar(8000))       
as       
begin       
    declare @idx int
    declare @id int       
    declare @slice varchar(8000)       

    set @id = 1
    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
        begin
            insert into @temptable(id, Items) values( @id , @slice)       
            set @id = @id + 1
        end

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return       
end

then use in Insert Query like this :

INSERT INTO NewTbl
SELECT .... , 
     (SELECT ITEMS FROM dbo.Split(Address) where id = 1) as Country,
     (SELECT ITEMS FROM dbo.Split(Address) where id = 2) as City,
     (SELECT ITEMS FROM dbo.Split(Address) where id = 3) as Address,
...