SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Merging 2 tables into one..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Andy241
Starting Member

United Kingdom
1 Posts

Posted - 07/25/2014 :  05:37:37  Show Profile  Reply with Quote
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
Posting Yak Master

172 Posts

Posted - 07/25/2014 :  06:51:24  Show Profile  Reply with Quote
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.


Go to Top of Page

MichaelJSQL
Posting Yak Master

172 Posts

Posted - 07/25/2014 :  06:55:39  Show Profile  Reply with Quote
-- 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000