Insert into table based on max value from another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert into table based on max value from another

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-02 : 12:11:14

I am trying to insert records from a temp table into a live table. Sounds simple enough....

I want to populate the destination table with the max value from another tables column, adding 1 for each record.

Similar to the following code (obviously not working)...

Any ideas, hopefully this makes sense.

INSERT INTO LIVE_TABLE (Create_TS, Batch_No)
SELECT GETDATE(), (select MAX(Batch_No) + 1 from ANOTHER_LIVE_TABLE)
FROM #TEMP_TABLE

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-02 : 13:03:19

Neither of the columns you are inserting into the LIVE_TABLE come from the #TEMP_TABLE. So wouldn't this work for you?

INSERT INTO  LIVE_TABLE
  (
    Create_TS,
    Batch_No
  )
SELECT
	GETDATE(),
	MAX(batch_no)+1
FROM
	ANOTHER_LIVE_TABLE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 13:03:32

[code]
INSERT INTO LIVE_TABLE (Create_TS, Batch_No)
SELECT GETDATE(), (select MAX(Batch_No) from ANOTHER_LIVE_TABLE) + ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM #TEMP_TABLE
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-02 : 13:18:06

Visakh16, how would your example handle a column which does not allow nulls?
Right now, I get the following when run:

"'Batch_No', table 'TEST.SOME_LIVE_TABLE'; column does not allow nulls. INSERT fails."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 13:20:01

might be this?


INSERT INTO LIVE_TABLE (Create_TS, Batch_No)
SELECT GETDATE(), COALESCE((select MAX(Batch_No)  from ANOTHER_LIVE_TABLE),0) + ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM #TEMP_TABLE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-08-03 : 07:35:59

Thanks to all.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 07:38:27

is that solved?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources