?
Solved

redo identity column

Posted on 2013-06-10
4
Medium Priority
?
315 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
[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
4 Comments
 
LVL 22

Accepted Solution

by:
Om Prakash earned 1000 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 1000 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 11

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

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.
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.
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
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

777 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