Solved

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

Posted on 2007-11-27
13
383 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:jackjohnson44
Comment Utility
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
Comment Utility
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]
Comment Utility
you might want to explain the "whole" purpose of what you are trying to do... that will help us giving adequate suggestions..
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:jackjohnson44
Comment Utility
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
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Full Text Search string 5 32
t-sql complement 8 29
Save sql query result in sql server 15 22
How to query date ranges with SQL 6 21
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now