Calculating values for Primary Key

• ajkarora

  SSChasing Mays

  Points: 601

  More actions

  June 2, 2017 at 1:06 pm

  #330763

  Hello All,
  I have two tables. The First table is staging table where I have data called MatCodeID, MatCode, MatDesc
  Table A
  

  Second table has the same fields but with values
  Table B 
  

  I need to insert new data from Table A to Table B. In table B MatCodeID is primary key. I am able to do this fine with there’s only one value in Table A. I used
  ((SELECT MAX((MatCodeID) + 1)  FROM Table B) )  to calculate value for MatCodeID. However, with multiple data it doesn’t work. Can someone help me out on how I can get this done? I would like to insert data for row 1 first and then calculate the next MatCodeID and insert the second data to Table B. Maybe a loop statement needs to be done?

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  June 2, 2017 at 1:42 pm

  #1945204

  You could always make MatCodeID an Identity field and not worry about it.

  Alternately, you can try something like this
  
INSERT INTO TableB (MatCodeID, MatCode, MatDesc)
SELECT
  MatCodeID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)
            + (SELECT MAX(b.MatCodeID) FROM TableB AS b)
, a.MatCode
, a.MatDesc
FROM TableA AS a;


  ---

  How to post data/code on a forum to get the best help.

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  June 2, 2017 at 1:57 pm

  #1945211

  Just based on what you've said here it sounds like making that column in the destination table an IDENTITY column would be the simplest.

  If there are reasons that doesn't work in your situation, and you still just need any arbitrary value for the MatCodeID, then you can just use ROW_NUMBER() to get an incrementing integer for each row in the staging table, and add that incrementing integer to the max ID.

  EDIT: Left this window open for a while, and DesNorton beat me to it. I really need to break this habit of starting responses and wandering off 🙂

  EDIT Part 2: Since it was quoted, I'll just mention that I removed the code from the post since DesNorton had already illustrated the concept, and mine took up a lot of space on the page 🙂

• ajkarora

  SSChasing Mays

  Points: 601

  More actions

  June 2, 2017 at 2:08 pm

  #1945213

  Jacob Wilkins - Friday, June 2, 2017 1:57 PM

  Just based on what you've said here it sounds like making that column in the destination column an IDENTITY column would be the simplest.

  If there are reasons that doesn't work in your situation, and you still just need any arbitrary value for teh MatCodeID, then you can just use ROW_NUMBER() to get an incrementing integer for each row in the staging table, and add that incrementing integer to the max ID.

  Something like this:

  CREATE TABLE #staging     (MatCodeID INT, MatCode VARCHAR(30), MatDesc VARCHAR(100));
CREATE TABLE #destination (MatCodeID INT, MatCode VARCHAR(30), MatDesc VARCHAR(100));

  INSERT INTO #staging (MatCodeID,MatCode,MatDesc)
SELECT NULL,'TESTMATCODE3','TESTMATCODE3'
UNION ALL
SELECT NULL,'TESTMATCODE4','TESTMATCODE4';

  INSERT INTO #destination (MatCodeID,MatCode,MatDesc)
SELECT 40208,'TESTMATCODE2','TESTMATCODE2'
UNION ALL
SELECT 40207,'TESTMATCODE','TESTMATCODE'
UNION ALL
SELECT 40206,'G6J-2FS-Y-DC5','Relay, Telecom, DPDT, 1A, 5V, Omron Elec.';

  WITH
staging_data AS
(
SELECT rn=ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
       *
FROM #staging
),
max_id AS
(
SELECT max_id=MAX(MatCodeID)
FROM #destination
)

  INSERT INTO #destination (MatCodeID,MatCode,MatDesc)
SELECT MatcodeID=rn+max_id,
       MatCode,
       MatDesc
FROM   staging_data
       CROSS JOIN
       max_id;

  SELECT *
FROM #destination;

  DROP TABLE #destination,#staging;

  Thank you so much! This worked.

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  June 3, 2017 at 9:08 am

  #1945251

  Just to establish some warm fuzzies that a concurrent update won't ever interfere with the correct calculation (unique sequential number) of the MatCodeID, I would add a WITH (TABLOCKX) to the INSERT clause of Jacob's good code.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)