asked
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
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
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.
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!??
@innovator thanks for your co-operation. As you can see, accepting the answers is beneficial to your karma score too!
@innovator - you've still got 11 questions with un-accepted answers. Hope you can find the time to review these. Thanks.
4 People are following this question.
