Moving Data from One DB to Another

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 2005 Forums

Transact-SQL (2005)

Moving Data from One DB to Another

Author

Topic

prakash.c
Starting Member

6 Posts

Posted - 2008-06-30 : 03:29:48

Hi All,

Here is my requirement

I have 2 DataBase DB1 and DB2
I have to move data from DB1.Table1 to DB2.Table1

And my DB1.Table1 Structure is as follows

------------------------------------
Id - Parent ID - Description
------------------------------------
1 - 0 - AAA
2 - 0 - BBB
3 - 1 - CCC
4 - 2 - DDD
5 - 3 - EEE
------------------------------------

And the condition is, I have Data already present in DB2.Table1 like

------------------------------------
Id - Parent ID - Description
------------------------------------
1 - 0 - AAX
2 - 0 - BBX
3 - 2 - CCX
4 - 1 - DDX
5 - 4 - EEX
------------------------------------

While moving Data from DB1.Table1 the record

1 - 0 - AAA

must the place in DB2.Table2 as

6 - 0 - AAA

And the correspondent ParentId of DB1.Table1 must be changed to 6

as shown below

DB1.Table1

3 - 1 - CCC

must be changed to

3 - 6 - CCC

How to do this....?

i think i'm clear with my issues, can anyone help me to solve this problem.

Thanks in advance.

Prakash.C

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 03:48:19

[code]DECLARE @db1 TABLE
(
ID int,
ParentID int,
Description varchar(3)
)
INSERT INTO @db1
SELECT 1 , 0 , 'AAA' UNION ALL
SELECT 2 , 0 , 'BBB' UNION ALL
SELECT 3 , 1 , 'CCC' UNION ALL
SELECT 4 , 2 , 'DDD' UNION ALL
SELECT 5 , 3 , 'EEE'

DECLARE @db2 TABLE
(
ID int identity(1, 1),
ParentID int,
Description varchar(3)
)
INSERT INTO @db2 (ParentID, Description)
SELECT 0 , 'AAX' UNION ALL
SELECT 0 , 'BBX' UNION ALL
SELECT 2 , 'CCX' UNION ALL
SELECT 1 , 'DDX' UNION ALL
SELECT 4 , 'EEX'

INSERT INTO @db2 (ParentID, Description)
SELECT CASE WHEN ParentID <> 0 THEN NULL ELSE ParentID END, Description
FROM @db1 db1
WHERE NOT EXISTS
(
SELECT *
FROM @db2 x
WHERE x.Description = db1.Description
)

UPDATE db2
SET ParentID = db2p.ID
FROM @db2 db2
INNER JOIN @db1 db1 ON db2.Description = db1.Description
INNER JOIN @db1 db1p ON db1.ParentID = db1p.ID
INNER JOIN @db2 db2p ON db2p.Description = db1p.Description
WHERE db2.ParentID IS NULL

SELECT *
FROM @db2

/*

ID ParentID Description
----------- ----------- -----------
1 0 AAX
2 0 BBX
3 2 CCX
4 1 DDX
5 4 EEX
6 0 AAA
7 0 BBB
8 6 CCC
9 7 DDD
10 8 EEE

(10 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

prakash.c
Starting Member

6 Posts

Posted - 2008-06-30 : 04:50:48

Hi Khtan,

Great, U'r almost correct, But it doesn't works if the Description is Same in the Both the table

Like in DB1.Table1

1 - 0 - AAA

And in DB2.Table1

1 - 0 - AAA

how to make for this khtan.

Thanks
Prakash.C

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 05:16:29

then how do you identified if the AAA in DB1.Table1 is not existed in DB2.Table1 ?

KH
[spoiler]Time is always against us[/spoiler]

prakash.c
Starting Member

6 Posts

Posted - 2008-06-30 : 05:21:30

Hi Khtan

Description is independent for the tables, we can not match based on it.
The thing is we have to append the records from DB1.Table1 to DB2.Table1, on the condition that
Parent child relation for the DB1.Table1 records must not mis-match in the DB2.Table1.
(Need to Protect the Parent - Child Relation)

Thanks
Prakash.C

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 09:03:27

[code]DECLARE @db1 TABLE
(
[ID] int,
ParentID int,
[Description] varchar(3)
)
INSERT INTO @db1
SELECT 1 , 0 , 'AAA' UNION ALL
SELECT 2 , 0 , 'BBB' UNION ALL
SELECT 3 , 1 , 'CCC' UNION ALL
SELECT 4 , 2 , 'DDD' UNION ALL
SELECT 5 , 3 , 'EEE'

DECLARE @db2 TABLE
(
[ID] int identity(1, 1),
ParentID int,
[Description] varchar(3)
)
INSERT INTO @db2 (ParentID, [Description])
SELECT 0 , 'AAX' UNION ALL
SELECT 0 , 'BBX' UNION ALL
SELECT 2 , 'CCX' UNION ALL
SELECT 1 , 'DDX' UNION ALL
SELECT 4 , 'EEX'

DECLARE @ids TABLE
(
[ID] int,
[Description] varchar(3)
)

INSERT INTO @db2 (ParentID, [Description])
OUTPUT INSERTED.ID, INSERTED.[Description] INTO @ids
SELECT CASE WHEN ParentID <> 0 THEN NULL ELSE ParentID END, [Description]
FROM @db1 db1

UPDATE db2
SET ParentID = db2p.ID
FROM @db2 db2
INNER JOIN @db1 db1 ON db2.[Description] = db1.[Description]
INNER JOIN @db1 db1p ON db1.ParentID = db1p.[ID]
INNER JOIN @ids db2p ON db2p.[Description] = db1p.[Description]
WHERE db2.ParentID IS NULL

SELECT *
FROM @db2

/*
ID ParentID Description
----------- ----------- -----------
1 0 AAX
2 0 BBX
3 2 CCX
4 1 DDX
5 4 EEX
6 0 AAA
7 0 BBB
8 6 CCC
9 7 DDD
10 8 EEE

(10 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

prakash.c
Starting Member

6 Posts

Posted - 2008-07-01 : 00:14:54

Hi Khtan,

thank u very for your help. I have done it in this way, check it weather it will do or not

Declare C1 Cursor for Select Id, ParentId, Description from Table1

Declare @Id as BigInt
Declare @PId as BigInt
Declare @Desc as NVarchar(50)
Declare @NewId as BigInt
Declare @Count as Int

Select @Count = ISNULL(MAX(Id),0) + 1 from Table2

Open C1

Fetch Next from C1 Into @Id, @PId, @Desc

While @@Fetch_Status = 0
Begin

Select @NewId = ISNULL(MAX(Id),0) + 1 from Table2

IF @PId IS NOT NULL
Insert Into Table2 (Id, ParentId, [Description])
Values (@NewId, ((@Count - 1) + @PId), @Desc)
ELSE
Insert Into Table2 (Id, ParentId, [Description])
Values (@NewId, @PId, @Desc)

Fetch Next from C1 Into @Id, @PId, @Desc
End

Close C1

Deallocate C1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 00:33:18

if my solution is working for you, it should be faster as it set based compare to loop based

KH
[spoiler]Time is always against us[/spoiler]

prakash.c
Starting Member

6 Posts

Posted - 2008-07-01 : 00:42:44

Yes Khtan,

U'r solution is also ok, but we cannot give chance for a condition that one or more description may be same in current table or other table.

but with my solution, we can avoid those problem

thanks
Prakash.C

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 00:44:38

quote:
Originally posted by prakash.c

Yes Khtan,

U'r solution is also ok, but we cannot give chance for a condition that one or more description may be same in current table or other table.

but with my solution, we can avoid those problem

thanks
Prakash.C

the last solution i posted does not assumed that. Take a look

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 00:48:26

see this. I changed some of the data in db1 to be same as db2.

DECLARE @db1 TABLE
(
	[ID]		int,
	ParentID	int,
	[Description]	varchar(3)
)
INSERT INTO @db1
SELECT	1 , 0 , 'AAA'	UNION ALL
SELECT	2 , 0 , 'BBB'	UNION ALL
SELECT	3 , 1 , 'CCX'	UNION ALL
SELECT	4 , 2 , 'DDD'	UNION ALL
SELECT	5 , 3 , 'EEX'

DECLARE @db2 TABLE
(
	[ID]		int identity(1, 1),
	ParentID	int,
	[Description]	varchar(3)
)
INSERT INTO @db2 (ParentID, [Description])
SELECT	0 , 'AAX'	UNION ALL
SELECT	0 , 'BBX'	UNION ALL
SELECT	2 , 'CCX'	UNION ALL
SELECT	1 , 'DDX'	UNION ALL
SELECT	4 , 'EEX'

DECLARE @ids TABLE
(
	[ID]		int,
	[Description]	varchar(3)
)

INSERT INTO @db2 (ParentID, [Description])
OUTPUT	INSERTED.ID, INSERTED.[Description] INTO @ids 
SELECT	CASE WHEN ParentID <> 0 THEN NULL ELSE ParentID END, [Description]
FROM	@db1 db1

UPDATE	db2
SET	ParentID	= db2p.ID
FROM	@db2 db2
	INNER JOIN @db1 db1	ON	db2.[Description]	= db1.[Description]
	INNER JOIN @db1 db1p	ON	db1.ParentID		= db1p.[ID]
	INNER JOIN @ids db2p	ON	db2p.[Description]	= db1p.[Description]
WHERE	db2.ParentID	IS NULL

SELECT	*
FROM	@db2

/*
ID          ParentID    Description 
----------- ----------- ----------- 
          1           0 AAX
          2           0 BBX
          3           2 CCX
          4           1 DDX
          5           4 EEX
          6           0 AAA
          7           0 BBB
          8           6 CCX
          9           7 DDD
         10           8 EEX

(10 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources