x

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

Hi All,

Please help me with the below mentioned scenarios->

Table structure->

Table A(int id identity ,Eid int,name varchar(20),sal int)
Table B(int id identity ,Eid int,name varchar(20),sal int)

Data is ->

Table A

1,101,'paul',1200
2,102,'pat',1400
3,103,'mat',1500

Table B->

1,101,'abc',1400
2,102,'xyz',1300
3,103,'pqr',1450
4,104,'lmn',1100
5,105,'rrr',1700
6,106,'xxr',1450
7,107,'sss',1000

Scenarios->

Now I need to dump data from Table B into table A using two ways

Scenario 1) I needs to dump all data from Table B into table A at last row of Table A but condition is that the the Eid column of first row of Table B should contain value with Max of Eid column value + 1 of Table A

For Ex->

from above data Eid column of table A has max value as '103' so the first record of table B should inserted into table B with Eid column value as 104 and subsequently all records of table B should be inserted in the same way.

O/P required ->

1,101,'paul',1200
2,102,'pat',1400
3,103,'mat',1500
4,104,'abc',1400 -- Note : Eid of this was earlier '101' in Table B
5,105,'xyz',1300
6,106,'pqr',1450
7,107,'lmn',1100
8,108,'rrr',1700
9,109,'xxr',1450
10,110,'sss',1000

===========

Scenario 2) I needs to dump all data from Table B into table A at a specific row of Table A but condition is that the the Eid column of first row of Table B should contain value of (+1) the value of Eid column value of Table A at that specific row

For Ex->

Suppose I need to dump data into table A at row with Eid column value as as '102' so the first record of table B should inserted into table A with Eid column value as '103' and subsequently all records of table B should be inserted in the same way and after that the other records of table A should be updated acoordingly

For above data,If I insert data at row with eid as '102' of Table A all data from table B should be insered after that specific row and after that insert operation ,remaining data from table A like rows that were earlier like eid of '103' should be updated with max of value of eid columns + 1 that was calculated after the insert operation

Hope I was able to clarify my query.

O/P required->

Table A

1,101,'paul',1200
2,102,'pat',1400
3,103,'abc',1400
4,104,'xyz',1300
5,105'pqr',1450
6,106,'lmn',1100
7,107,'rrr',1700
8,108,'xxr',1450
9,109,'sss',1000
10,110,'mat',1500 -- Note : Eid of this was earlier '103' in Table A

Immediate response would be much appreciated.

Regards, Innovator.

Query to recover accidentally deleted data

sql,recors,record
more ▼

asked Mar 23, 2012 at 03:08 AM in Default

innovator gravatar image

innovator
335 15 15 17

So far you have asked a lot of questions on this forum and have had a large number of answers but have not marked any of them as having helped you. This wont encourage people to keep offering solutions to questions you pose. Would you be able to find some time to review the answers that you have had on other questions and mark appropriate one as helpful by clicking the tick beside them please? It isnt much to ask for all the assistance that is offered across this site.

Thanks
Mar 23, 2012 at 11:27 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

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.
more ▼

answered Mar 23, 2012 at 08:58 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

firstly i would like to thank @william for ur precious time and effort!

sorry @kev,actually earlier i was not familiar with site's procedure to mark as correct,but later i started to mark my questions as answered! u can se my previous few questions where i have marked them as to helpfull,and surely i have thanked each and every people who commented on my post,also would keep in mind to answer the questions henceforth, by the way thnx u guys for helping me!??
Mar 23, 2012 at 12:28 PM innovator
@innovator thanks for your co-operation. As you can see, accepting the answers is beneficial to your karma score too!
Mar 23, 2012 at 12:34 PM Kev Riley ♦♦
@innovator - no worries, I hope you get your data issues fixed.
Mar 23, 2012 at 12:42 PM WilliamD
ya i got my queries resolved,thnx again!
Mar 23, 2012 at 12:45 PM innovator
@innovator - you've still got 11 questions with un-accepted answers. Hope you can find the time to review these. http://ask.sqlservercentral.com/users/5563/innovator.html Thanks.
Mar 23, 2012 at 12:53 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x726
x84

asked: Mar 23, 2012 at 03:08 AM

Seen: 2222 times

Last Updated: Mar 23, 2012 at 02:19 PM