Click here to Skip to main content
14,929,864 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How do I can generate serial no for each refno no and insert them to a different
(table B) .Invoice number will be generate based on Table B( - MAX()+1 from Table B) ? MS SQL 2012.

Table A

packname                            refno
----------------------------------- -----------
Demo 250 Channels                   1
qqq                                 1
qqq                                 3
qqq                                 3
Demo 250 Channels                   1
Demo 250 Channels                   1

Result I want
Table B

packname                            refno      InvoiceNo
----------------------------------- --------------------
Demo 250 Channels                   1                1
qqq                                 1                2
qqq                                 1                3
qqq                                 3                1
Demo 250 Channels                   3                2
Demo 250 Channels                   5                1

What I have tried:

SELECT packname, refno,
FROM Table1

This process run every day and fetch some record and insert into Table2 so when we insert in table2 first time then its OK, but when we run this on second time then its generate duplicate number
Updated 11-Aug-16 21:11pm
The Praveen Singh 10-Aug-16 5:17am
use temp table with auto identity colomn
Tomas Takac 10-Aug-16 5:34am
In your example there is difference in refno between table A and B. Second "ORDER BY (SELECT 1)" looks highly suspicious - you should use a real column, perhaps some datetime field. Finally I don't understand how this should work. You are inserting from A to B and generating the InvoiceNo. But what happen with the records in A, do they get deleted and next time you are only inserting the newly added records since last run? Or A and B have the same date, except of the InvoiceNo of course?

1 solution

Try this

SELECT packname, refno,ROW_NUMBER() OVER (PARTITION BY refno ORDER BY Packname) InvoiceNo
FROM Table A

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900