Solved

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

Posted on 2007-11-27
13
386 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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 142

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 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Querying data from 3 SQL tables 2 31
convert null in sql server 12 33
Query to capture 5 and 9 digit zip code? 4 20
SQL Count issue 24 14
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

776 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