Merging 2 tables into one.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Merging 2 tables into one..

Author

Topic

Andy241
Starting Member

1 Post

Posted - 2014-07-25 : 05:37:37

Hi All, firstly apologies if this is in the wrong section..

The program I use stores data into sql tables, my issue is - I have 2 different database backups - each with missing data in a few specific tables - and you've guessed it each backup is missing the data which the other one has!

I need to find away to merge the tables into one (as they are both from the same program both table names and everything else will be the same)

I probably sound like a SQL noob but iv just started playing around with it!

Any help/guidance would greatly be appreciated, thanks!

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-25 : 06:51:24

You can restore both backups to different copies of the database. Let's say Database1 and Database2.

If the table structures are the same, you can use the following methodology:

CREATE TABLE #TableFromDatabaseA
(
ID int IDENTITY(1,1),
SomeVarchar varchar(10),
SomeInt int,
SomeDateTime datetime
)

INSERT INTO #TableFromDatabaseA
VALUES('XXXXXX',9,'7/25/2014 04:25:12'),('DHYEUEUN',1,'7/25/2014 09:18:41'),('ABGDHDH',2,'7/25/2014 03:55:26')

CREATE TABLE #TableFromDatabaseB
(
ID int IDENTITY(5,1),
SomeVarchar varchar(10),
SomeInt int,
SomeDateTime datetime
)

INSERT INTO #TableFromDatabaseB
VALUES('TTTYYTY',16,'7/25/2014 02:19:57'),('YRULOST',51,'7/25/2014 06:03:19'),('ARETNUE',4,'7/25/2014 04:21:00')

-- everything from table A that is not in Table B - table B will be the target
SELECT * FROM #TableFromDatabaseA
EXCEPT
SELECT * FROM #TableFromDatabaseB

-- using Identity, so must set identity insert on

SET IDENTITY_INSERT #TableFromDatabaseB ON

INSERT INTO #TableFromDatabaseB(ID,SomeVarchar,SomeInt,SomeDateTime )
SELECT * FROM #TableFromDatabaseA
EXCEPT
SELECT * FROM #TableFromDatabaseB

SET IDENTITY_INSERT #TableFromDatabaseB OFF

SELECT * FROM #TableFromDatabaseB

-- of course you will have to use 3 part naming when you are selecting from your source if you are running from your target. So if your SQL windows current context was Database2 and disregarding the temp tables, the SQL would be more like this.

SET IDENTITY_INSERT #TableFromDatabaseB ON

INSERT INTO TableFromDatabaseB(ID,SomeVarchar,SomeInt,SomeDateTime )
SELECT * FROM Database1.dbo.TableFromDatabaseA
EXCEPT
SELECT * FROM TableFromDatabaseB

SET IDENTITY_INSERT TableFromDatabaseB OFF

SELECT * FROM TableFromDatabaseB

You could also write select statements with where not exists clauses. You would need that if you have a primary key column(like the ID identity column in the example) that have overlapping values. Obviously, you would need give new Identity values when merging the tables in which case the SQL would look more like the following

INSERT INTO TableFromDatabaseB(SomeVarchar,SomeInt,SomeDateTime )
SELECT SomeVarchar,SomeInt,SomeDateTime FROM Database1.dbo.TableFromDatabaseA
EXCEPT
SELECT SomeVarchar,SomeInt,SomeDateTime FROM TableFromDatabaseB

if your tables are not the same structure (different number and or type columns), it can be more tricky. so this method assumes you are trying to move data from 1 copy of the database to another that have identical structures.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-25 : 06:55:39

-- Correction : forgot to remove 1 # from the example

From this point

-- of course you will have to use 3 part naming when you are selecting from your source if you are running from your target. So if your SQL windows current context was Database2 and disregarding the temp tables, the SQL would be more like this.

SET IDENTITY_INSERT #TableFromDatabaseB ON : should be SET IDENTITY_INSERT TableFromDatabaseB ON

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources