?
Solved

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

Posted on 2007-11-27
13
Medium Priority
?
392 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.
Suggested Courses

764 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