Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

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
0
solraccheffy
Asked:
solraccheffy
  • 3
1 Solution
 
David ToddSenior DBACommented:
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

0
 
appariCommented:

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
0
 
appariCommented:

take backup of original tables and try it on sample temp tables
0
 
solraccheffyAuthor Commented:
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.
0
 
appariCommented:
then try this

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

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

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


Update table1 set Column3 = ID
from ProvStates
where ProvStates.Name = Column3
and ProvStates.CountryID = Column1
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now