[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2007-11-27
13
Medium Priority
?
394 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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 1200 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 800 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

649 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