Solved

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

Posted on 2014-03-13
4
428 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
  • 2
4 Comments
 
LVL 40

Accepted Solution

by:
Sharath earned 500 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 40

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 142

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

773 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