none
Insert from one table to other based on max ()? what if destinaton table is empty? RRS feed

  • Question

  • Hi Experts,

    I've a table named Pers, In this table I've a column Sno.

    I am inserting into Pers table from other table by checking the max(Sno.) + 1 but for the first time when Pers table is empty, this insert is not working.

    how to make insert to work please help.

    Thanks in advance

    --Noorbi

    Monday, March 25, 2013 6:05 AM

Answers

  • try

    ISNULL(MAX(Sr.No),0)  + 1 


    Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by SQL2012BI Monday, March 25, 2013 7:17 AM
    Monday, March 25, 2013 6:08 AM
  • Try the below query
    SELECT ISNULL(MAX(Sr.No),0)  + 1 FROM Pers



    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    • Marked as answer by SQL2012BI Monday, March 25, 2013 7:17 AM
    Monday, March 25, 2013 6:14 AM

All replies

  • try

    ISNULL(MAX(Sr.No),0)  + 1 


    Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by SQL2012BI Monday, March 25, 2013 7:17 AM
    Monday, March 25, 2013 6:08 AM
  • Try the below query
    SELECT ISNULL(MAX(Sr.No),0)  + 1 FROM Pers



    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    • Marked as answer by SQL2012BI Monday, March 25, 2013 7:17 AM
    Monday, March 25, 2013 6:14 AM
  • SELECT ISNULL(MAX(SNo),0)+1 FROM Pers INSERT INTO Pers(Po,FlID,SNo,[LID],CVal) SELECT SPO.Po,106,(ISNULL(MAX(POM.SNo),0)+1),2,SPO.CVal FROM PO SPO (NOLOCK) JOIN Pers POM (NOLOCK) ON POM.PO = SPO.Po WHERE SPO.VmI= 0 GROUP BY SPO.PO,SPO.CVal

    Hi Paresh,

    Individual SELECT is working and giving 1 as output but while inserting it is saying 0 rows affected.

    Please see the above code.

    Monday, March 25, 2013 6:16 AM
  • You may change the LID to identity column and forget about the increment mechanism. That would do the trick for you.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Junaid_Hassan Monday, March 25, 2013 6:23 AM
    • Unproposed as answer by SQL2012BI Monday, March 25, 2013 6:26 AM
    Monday, March 25, 2013 6:19 AM
  • Hi Latheesh,

    I think you mean here SNo. no, we cannot do that. 

    Because for one PO we may have many CVal and here we are giving sNo for these PO and CVal combination. 

    here, if we have same Sno in Pers what we have in PO for the same PO then we should look at the max SNo in Pers and add 1 to it while inserting.

    now for the first time this Pers table is empty and my insert code get executed first then insert won't happen, thats the issue.

    Monday, March 25, 2013 6:23 AM