Query to dump a data from one table to another same structure table at a specific position and column value

Here are two solutions (one per scenario):

Solution 1:

Insert the data from one table into the other, generating new eid values on the way in (works for SQL 2005 and above).

Here I am assuming that eid is the driving value in each table and that the id column has no meaning other than to be a primary key.

USE tempdb
GO

IF EXISTS (SELECT * FROM sys.tables AS T WHERE name = 'TableA')
    BEGIN
        DROP TABLE dbo.TableA
    END;

CREATE TABLE dbo.TableA (id int IDENTITY (1,1) PRIMARY KEY CLUSTERED, eid int, NAME varchar(20), sal int);

IF EXISTS (SELECT * FROM sys.tables AS T WHERE name = 'TableB')
    BEGIN
        DROP TABLE dbo.TableB
    END
CREATE TABLE dbo.TableB (id int IDENTITY (1,1) PRIMARY KEY CLUSTERED, eid int, NAME varchar(20), sal int);

INSERT INTO dbo.TableA
        (eid, NAME, sal)
SELECT 101,'paul',1200 UNION ALL
SELECT 102,'pat',1400 UNION ALL
SELECT 103,'mat',1500 
;

INSERT INTO dbo.TableB (eid, NAME, sal)
SELECT 101,'abc',1400  UNION ALL
SELECT 102,'xyz',1300  UNION ALL
SELECT 103,'pqr',1450  UNION ALL
SELECT 104,'lmn',1100  UNION ALL
SELECT 105,'rrr',1700  UNION ALL
SELECT 106,'xxr',1450  UNION ALL
SELECT 107,'sss',1000
;


/* Scenario 1: Merge TableB into TableA updating incoming eid values to MAX(eid)+1 from TableA*/
INSERT INTO dbo.TableA
        (eid, NAME, sal)
SELECT  (SELECT MAX(eid)
         FROM   dbo.TableA) + ROW_NUMBER() OVER (ORDER BY eid),
        NAME,
        sal
FROM   dbo.TableB AS TB
;
SELECT * FROM dbo.TableA

Next comes scenario 2:

Insert the values from one table into another, between rows in the target table, renumbering eid values that are "pushed down" the target table.

This solution runs some changes inside a transaction and rolls the transaction back - you will want to change the rollback into a commit once you are sure that the data is being changed as you want it to.

/* Scenario 2: Merge TableB into TableA inserting between eid values */
USE tempdb
GO

IF EXISTS (SELECT * FROM sys.tables AS T WHERE name = 'TableA')
    BEGIN
        DROP TABLE dbo.TableA
    END;

CREATE TABLE dbo.TableA (id int IDENTITY (1,1) PRIMARY KEY CLUSTERED, eid int, NAME varchar(20), sal int);

IF EXISTS (SELECT * FROM sys.tables AS T WHERE name = 'TableB')
    BEGIN
        DROP TABLE dbo.TableB
    END
CREATE TABLE dbo.TableB (id int IDENTITY (1,1) PRIMARY KEY CLUSTERED, eid int, NAME varchar(20), sal int);

INSERT INTO dbo.TableA
        (eid, NAME, sal)
SELECT 101,'paul',1200 UNION ALL
SELECT 102,'pat',1400 UNION ALL
SELECT 103,'mat',1500 
;

INSERT INTO dbo.TableB (eid, NAME, sal)
SELECT 101,'abc',1400  UNION ALL
SELECT 102,'xyz',1300  UNION ALL
SELECT 103,'pqr',1450  UNION ALL
SELECT 104,'lmn',1100  UNION ALL
SELECT 105,'rrr',1700  UNION ALL
SELECT 106,'xxr',1450  UNION ALL
SELECT 107,'sss',1000
;

DECLARE @InsertAfter int 
SET @InsertAfter = 102 /* The eid in TableA after which the data from TableB should be inserted into */


BEGIN
        BEGIN TRANSACTION
        UPDATE TA
        SET eid = eid + (SELECT COUNT(*) AS InsVal FROM dbo.TableB AS TB)
        FROM dbo.TableA AS TA
        WHERE eid >= @InsertAfter
        ;      
        INSERT INTO dbo.TableA
        (eid, NAME, sal)
        SELECT  @InsertAfter + ROW_NUMBER() OVER (ORDER BY id),
                NAME,
                sal
        FROM    dbo.TableB AS TB
        ;
        SELECT * FROM dbo.TableA
        ROLLBACK TRANSACTION
END

That should allow you to either add all the data to the end of the table or in between current values.