0

I am writting an program in C#, that use SQL Server database.

My program include a blank database (sample database) that allow users can create a new database.

For example:

  • User 1 use my program to create a new database (DB1) on his computer, then he import/enter some data into DB1
  • User 2 use my program to create a new database (DB2) on his computer, then he import/enter some data into DB2

Now, I have both two database (DB1, DB2). Do you know how to merge/combine two databases to have unique database that contain all users' data ?

Can you help me ?

please see my demo screenshot diagram

taibc
  • 897
  • 2
  • 15
  • 39
  • Users are creating databases on their computer or in a main database server? Is this program using any internet connection? – Batu.Khan Apr 07 '14 at 10:14
  • Users create databases on their computer. And I have backup files of their databases – taibc Apr 08 '14 at 00:11

2 Answers2

1

If the use of the procedures applicable to your application you might use the MERGE statement to combine your data. To learn more about merging follow http://msdn.microsoft.com/en-us/library/bb510625.aspx topic

Added OUTPUT usage sample, it shows how to get inserted row's PK to insert in other related table.

CREATE TABLE #teachers(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](15) NOT NULL
)
CREATE TABLE #pupils(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](15) NOT NULL
)
CREATE TABLE #createdTeachers ([ID] [int])
CREATE TABLE #createdPupils ([ID] [int])

insert into #teachers output inserted.ID into #createdTeachers values ('teacher#2') 
insert into #teachers output inserted.ID into #createdTeachers values ('teacher#1') 
insert into #pupils output inserted.ID into #createdPupils values ('pupil#2')
insert into #pupils output inserted.ID into #createdPupils values ('pupil#1')

select t.ID as NEW_TEACHER_ID,p.ID as NEW_PUPIL_ID from #createdTeachers as t , #createdPupils as p

I did not prepare sample, which describes your diagram fully but necessary concepts are shown. You might use "select t.ID as NEW_TEACHER_ID,p.ID as NEW_PUPIL_ID from #createdTeachers as t , #createdPupils as p" to insert teacherId, pupilId etc into Teacher_Pupil table

akorenchikov
  • 67
  • 6
  • SQL 2005 don't support Merge statement. Do you have any other ideas ? – taibc Apr 10 '14 at 06:17
  • 1
    You might use ORM (such as EntityFramework ) to ease merging.The other approach is to combine inserts/updates ( http://www.programmersedge.com/post/2010/07/22/SQL-Server-2005-T-SQL-Mimic-MERGE-with-OUTPUT.aspx#.U0ZM9PmSx8F, http://stackoverflow.com/questions/12621241/can-i-use-the-merge-statement-in-sql-server-2005 ). Also you can find OUTPUT command usefull to keep created PrimaryKeys for related tables (http://technet.microsoft.com/en-us/library/ms177564.aspx ) – akorenchikov Apr 10 '14 at 07:51
  • Hi akorenchikov, Could you please see simple diagram example: [link](http://www.mediafire.com/view/myfiles/#6ra2ikrt3l4azj9). (Note that: Id fields have identity increment = 1). I use insert into ... select to combine data for tables: pupil, teacher, course. But, how can I combine data for Teacher_Pupil table ? – taibc Apr 15 '14 at 02:44
  • Hi akorenchikov, thank you very much. I am finding a solution to replace teacherId, pupilId from (out put) to old teacherId, pupilId values of Teacher_Pupil table in another database. Do you have any ideas ? – taibc Apr 15 '14 at 09:43
0

Merging to database is mentioned here Please refer : http://msdn.microsoft.com/en-us/library/ms140052.aspx

user3506246
  • 1
  • I am wanting use C# code to execute SQL statements. Can you help me ? – taibc Apr 10 '14 at 06:19
  • I tried to use Import/Export Data, but can't copy all data. For example: I have 3 tables: t1, t2, t3, and t2 have realationship with t1 and t3. After merge only t1 and t3 have correct data, t2's data can't be copied correctly – taibc Apr 10 '14 at 06:52