Solved

redo identity column

Posted on 2013-06-10
4
307 Views
Last Modified: 2013-06-18
I have a new set of tables for invoicing and we are inserting invoices manually from word and excel files. Right noow we are just entering them as we get them and will need to sort them by date and then have the new invoice numbers in that sequence.

How would I do that?
0
Comment
Question by:Shawn
4 Comments
 
LVL 22

Accepted Solution

by:
Om Prakash earned 250 total points
ID: 39236810
you can add one new column with identity and turn if off.

insert the data using below query

insert into table_name (your_id_col)
SELECT ROW_NUMBER() OVER (ORDER BY date_column) AS Id FROM yourTable

and once this is done, turn on rhe identity
0
 
LVL 13

Assisted Solution

by:sameer2010
sameer2010 earned 250 total points
ID: 39236885
Create the table with IDENTITY column COL1 and then run the query to insert into the same:
i.e. INSERT INTO NEW_TABLE(COL2, COL3) SELECT COL2, COL3 FROM YOUR_MANUAL_TABLE ORDER BY MANUAL_INVOICE_DATE ASC
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39237046
0
 
LVL 1

Author Comment

by:Shawn
ID: 39243424
thanks everyone. I've been put on another project but should have time to test Friday.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
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.

856 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