• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

sql server script that will change column type, do an insert, then change it back

I have a database I am converting into another fomat.  There is a sort number, and I want to make it unique in the new db.  I want to alter my script to change the column from numberic(10,2) to int, and auto increment, then after my insert statement change it back to numberic(10,2)

so

alter table, set column to autoincrement

my sql statement, which works

alter table change sortorder to numeric (10,2)

How can I do this with code?  Just the alter table lines?
0
jackjohnson44
Asked:
jackjohnson44
  • 5
  • 4
  • 3
  • +1
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello jackjohnson44,

You can't actually create an auto increment column on a numeric(10,2) column

In order to create an identity column, it is better to add a new column with the identity property


ALTER TABLE TableName
ADD newColumn numeric(10,0)  identity



Aneesh R
0
 
imitchieCommented:
please do as follows:

update a
set NumericID = a.r
from Tbl a inner join
(select NumericID, row_number() over (order by uid) r from uu) a
on a.NumericID = u.NumericID
0
 
imitchieCommented:
correction

update a
set NumericID = a.r
from Tbl a inner join
(select NumericID, row_number() over (order by NumericID) r from Tbl) a
on a.NumericID = u.NumericID


replace Tbl with the table name, and NumericID with the column you want to renumber. this SQL does it all in one step, without adding/removing columns
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jackjohnson44Author Commented:
Thanks, but I can't allow nulls into the column, and it is currently supposed to be unique.  I don't see how I can do the insert without having values in there.
0
 
jackjohnson44Author Commented:
is there a way to reoder the current table I am pulling from?
I can do that first then do a straight insert.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might want to explain the "whole" purpose of what you are trying to do... that will help us giving adequate suggestions..
0
 
jackjohnson44Author Commented:
actually, what imitchie said would work, I misunderstood.  Unfortunately the code didn't work.  

I am not sure if I did anything wrong, but here is what I had:

update a
set TestplansectionSort = a.TestPlanSectionName
(select TestplansectionSort, row_number() over (order by TestplansectionSort, TestPlanSectionName) as r
from TestPlanSection) as a
on a.TestplansectionSort = u.TestplansectionSort
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the code you posted is incomplete...
while I still wait for the explanations, let me suggest this code:


update a
set TestplansectionSort = a.r
from TestPlanSection u
join (select TestPlanSectionName, TestplansectionSort
      , row_number() over (order by   TestplansectionSort, TestPlanSectionName) as r
       from TestPlanSection
) as a
on a.TestPlanSectionName = u.TestPlanSectionName
and a.TestplansectionSort = u.TestplansectionSort

Open in new window

0
 
jackjohnson44Author Commented:
i am trying to import records from one table in a bad database without constraints on this sort column resulting in duplicate values into another database with the same sort column that will no accept duplicates.  Since there are so many duplicate sort numbers, I need to renumber then to make them unique.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, that is fully understandable.
the code I posted just above should do the job, then...
0
 
imitchieCommented:
i'm curious, is it unique by both testplansectionsort+TestPlanSectionName ?
update a
set TestplansectionSort = a.r
from TestPlanSection u
join
(select TestplansectionSort, row_number() over (order by TestplansectionSort, TestPlanSectionName) as r from TestPlanSection) as a
on a.TestplansectionSort = u.TestplansectionSort
and a.TestPlanSectionName = u.TestPlanSectionName

Open in new window

0
 
imitchieCommented:
hmm... i wasn't watching. not only did angel already cover this, but I left out testplansectionname from the inner query..
0
 
jackjohnson44Author Commented:
it works great.  It  is unique by testplansectionsort+TestPlanSectionName.  Acutally it is unique by each by itself.  Which does not hold true for the orig database.  I had to append (1), (2), (3) to each duplicate name.  Cleaning this thing is quite a pain.  Thanks a lot for your help.  I used angelII's code above.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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