Jeff Heilman
asked on
SQL Server 2005 Stored Procedure to insert records where ID is greater than Max ID
Hello,
I'm pretty new at SQL Server. What I'm trying to do I imagine is pretty simple. I have 2 identical tables, TableA and TableB. I want to insert the records from TableA into TableB where the ID is greater than the max ID already in TableB
Thanks for your help!
I'm pretty new at SQL Server. What I'm trying to do I imagine is pretty simple. I have 2 identical tables, TableA and TableB. I want to insert the records from TableA into TableB where the ID is greater than the max ID already in TableB
INSERT INTO [Server1].DB1.dbo.tblOrderItemSize
(QCID, JoxID, Parent, Whs, OrderNum, LineNum, [Assembly], GlassSize, SashSize, FrameSize, AssyLoc, QNADesc, DateStamp, GridType, GridLayout)
SELECT QCID, JoxID, Parent, Whs, OrderNum, LineNum, [Assembly], GlassSize, SashSize, FrameSize, AssyLoc, QNADesc, DateStamp, GridType,
GridLayout
FROM tblImportSize AS tblImportSize_1
WHERE (Whs = 140) AND (QCID > MaxID)
((SELECT MAX (QCID)AS MaxID
FROM [Server1].DB1.dbo.tblOrderItemSize)))
Thanks for your help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Being on mobile makes it difficult to write long sql....
I would retrieve the max Id into a variable
Select @var =max(id) from table
Anduse row_number function
Insert into tableb select @var + row_number() over (order by somefield) , ... from tablea
I would retrieve the max Id into a variable
Select @var =max(id) from table
Anduse row_number function
Insert into tableb select @var + row_number() over (order by somefield) , ... from tablea
ASKER
Thanks, this worked great!
Open in new window