asked on

script, move data from one table to another

I have a table with data that I want to put into other tables and create relationships between them as opposed to having 1 master table.
I have done a mock up below of what the scenario is.
In the first table listed below I want to move the data from Column1 to a new table called Countries (shown below) and replace the value of
column1 with the ID field. Apply the same concept with column3 to another table called PROVSTATES (shown below)

Currently the table Countries and PROVSTATES are empty and trying to determin the best way to move the values from the first table to there
respective table and assign the columns with the ID field instead.
Hopefully this makes sense.

Column1            Column2            Column3            Column4            Column5
Canada            xxxxx            Ontario            yyyyy            zzzzz
Canada            uuuuu            Quebec            iiiii            lllll
Canada            jjjjj            Manitoba      hhhhh            ooooo
Canada            ppppp            Manitoba      wwwww            kkkkk
USA            zzzzz            Arizona            kkkkk            ooooo
USA            xxxxx            IOWA            iiiii            qqqqq
USA            qqqqq            IOWA            hhhhh            kkkkk
Canada            ppppp            Ontario            wwwww            kkkkk

Countries
ID            Name
1            Canada
2            USA
3            Australia
4            Africa

PROVSTATES
ID
1            Ontario
2            Quebec
3            Manitoba
4            Nova Scotia
5            IOWA
6            Arizona

Table with ideal data

Column1            Column2            Column3            Column4            Column5
1            xxxxx            1            yyyyy            zzzzz
1            uuuuu            2            iiiii            lllll
1            jjjjj            3            hhhhh            ooooo
1            ppppp            3            wwwww            kkkkk
2            zzzzz            6            kkkkk            ooooo
2            xxxxx            5            iiiii            qqqqq
2            qqqqq            5            hhhhh            kkkkk
1            ppppp            1            wwwww            kkkkk

David Todd

Hi,

Try something like this to extract the countries and create an id.

select distinct column1 as country
into #countries
from dbo.FixedDriveData

select newid(), country
from #Countries

Although I've used newid() which gives a quick and dirty, you could create the table with an identity column, and then use an insert like this:

Create table Countries (
CountryID identity,
Country varchar( 256 )
)

insert Countries (country)
select country from #countries

HTH
David

appari

try something like this

change ID fields in both Countries and ProvStates tables to identity columns if they are not identity columns.

now execute the following SQLs.

insert into Countries(Name) select distinct Column1 from table1 where Column1 not in ( Select Name from Countries)

insert into ProvStates(Name) select distinct Column3 from table1 where Column3 not in ( Select Name from ProvStates )

Update table1 set Column1 = ID
from Countries
where Countries.Name = Column1

Update table1 set Column3 = ID
from ProvStates
where ProvStates.Name = Column3

appari

take backup of original tables and try it on sample temp tables

solraccheffy

ASKER

Arrr, I cant believe I missed this, but the last table is missing a field, the ProvStates table has a FK to the country table.

CountryID ID NAME
1 1 Ontario
1 2 Quebec
1 3 Manitoba
1 4 Nova Scotia
2 5 IOWA
2 6 Arizona

Hopefully this doesnt make this all to confusing now.

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial