Sql-server – Get new ID for inserted ID that comes from another table

sql serversql server 2014

Simple question but I'm a newbie. I have existing orders table 'Orders'(table1) and it has an OrderNumber as a column and the orders are number up 156.
I have a existing table called 'orders'(table 2) from another website. I have to insert these orders from table 2 into table one and the OrderNumber for the inserted new order has to be 157….and so on for all orders coming in. I have asked this question and get answers that make no sense. Anyone?

http://sqlfiddle.com/#!6/d423d/11/0

I got it to work but why did it increment with 50? It went from 156 to 206. I only want it to take the next number in sequence.

Best Answer

It incremented by 50 because you added "ordID" to @idmax and ordID = 50.

Maybe you wanted an incrementing number using row_number?

DECLARE @idmax INT
SELECT @idmax = MAX(OrderNumber) FROM OrderNumbers

INSERT INTO OrderNumbers
(OrderNumber,
 OrderGUID)

 SELECT
 row_number() over(order by ordID) +@idmax,
 NEWID()

 FROM orders



 SELECT * FROM OrderNumbers

Related Solutions

Sql-server – SQL Return Count of Answers with 0’s for Choices from another table

(copying my own answer from the same question on StackOverflow)

Your post already includes a good answer: "I am thinking I need to return a blank row for every answer that exists in the tblSurveyAnswerChoices then somehow join the other counts to that."

Here is one way to do so:

select choice, ifnull(countanswervalue,0) 
from tblsurveyanswerchoice
left join 
    (select answer_sequence, count(answer_sequence) as countanswervalue
    from tblslideresponses
    group by answer_sequence) 
    as countanswer on countanswer.answer_sequence=tblsurveyanswerchoice.sequence
/* the where clause is what you already posted */
where qid in (select question_id from tblidslide where id = @slide)

The use of left join ensures that any choice in the first table will be listed. By default, left join would return NULL for items not existing in the second table, but ifnull lets you replace NULL with 0.

NOTE: ifnull in MySQL should be replaced by isnull in TSQL

Sql-server – Fill Factor Setting and Performance

Have you considered updates? If you change a row and the new data does not fit in the same space the row would have to be moved to a new page. If you can rule this out, filling the pages to 100 percent will reduce the space the table uses and consequentially give you a slight performance improvement.

Best Answer

Related Solutions

Sql-server – SQL Return Count of Answers with 0’s for Choices from another table

Sql-server – Fill Factor Setting and Performance

Related Question