?
Solved

SQL Server 2005 Stored Procedure to insert records where ID is greater than Max ID

Posted on 2014-03-13
4
Medium Priority
?
441 Views
Last Modified: 2014-03-13
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

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)))

Open in new window


Thanks for your help!
0
Comment
Question by:jeff-h
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39927514
like this.
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 > (SELECT MAX (QCID)AS MaxID 
                 FROM [Server1].DB1.dbo.tblOrderItemSize) 

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 39927521
you can add table alias in the sub-query as well.
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 > (SELECT MAX (t1.QCID) 
                 FROM [Server1].DB1.dbo.tblOrderItemSize t1) 

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39927522
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
0
 

Author Closing Comment

by:jeff-h
ID: 39927620
Thanks, this worked great!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question