Solved

redo identity column

Posted on 2013-06-10
4
306 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 34
SQL Server 2012 r2 - Make Temp Table Query Faster 5 42
Live Storage Vmotion VMs with shared VMDK 10 57
SQL Improvement  ( Speed) 14 27
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

770 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