Solved

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

Posted on 2007-11-27
13
390 Views
Last Modified: 2013-11-30
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
Comment
Question by:jackjohnson44
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20363146
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20363205
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20363212
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:jackjohnson44
ID: 20363359
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
 

Author Comment

by:jackjohnson44
ID: 20363377
is there a way to reoder the current table I am pulling from?
I can do that first then do a straight insert.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20363418
you might want to explain the "whole" purpose of what you are trying to do... that will help us giving adequate suggestions..
0
 

Author Comment

by:jackjohnson44
ID: 20363451
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 20363473
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
 

Author Comment

by:jackjohnson44
ID: 20363484
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20363497
ok, that is fully understandable.
the code I posted just above should do the job, then...
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 200 total points
ID: 20363630
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
 
LVL 25

Expert Comment

by:imitchie
ID: 20363638
hmm... i wasn't watching. not only did angel already cover this, but I left out testplansectionname from the inner query..
0
 

Author Comment

by:jackjohnson44
ID: 20365760
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question