Geek Answers Handbook

Copying relational data from a database to a database

Change . Let me rephrase this completely, because I'm not sure if there is an XML way as I originally described.

Another edit . This should be a repeatable process, and it needs to be configured in such a way that it can be called in C # code.

In database A, I have a set of tables related to PK and FK. Let's say a parent table with tables for children and grandchildren.

I want to copy a rowset from database A to database B , which has identical table and field names. For each table, I want to insert it into the same table in database B. But I cannot restrict the use of the same primary keys. The copy procedure must create new PKs for each row in database B and must propagate them to child rows. In other words, I keep the same relationship between the data, but not the exact same PK and FK.

How would you solve this? I am open to suggestions. SSIS is not completely ruled out, but it doesn't seem to me that this will do just that. I am also open to a solution in LINQ, either using typed datasets or using some kind of XML thing or anything that will work in SQL Server 2005 and / or C # (.NET 3.5). A better solution would not require SSIS and would not require writing a lot of code. But I agree that this “best” solution may not exist.

(I did not do this task myself, nor restriction, so it was given to me.)

+5
source share
11 answers

, , , . ; , - . , , ; .

PK .

0

, SQL Server tablediff.exe , .

. .

+2

-, , SSIS - . , ...

, , , .

, , insert . SELECT, XML-:

declare @xml xml 
set @xml='<People Key="1" FirstName="Bob" LastName="Smith">
  <PeopleAddresses PeopleKey="1" AddressesKey="1">
    <Addresses Key="1" Street="123 Main" City="St Louis" State="MO" ZIP="12345" />
  </PeopleAddresses>
</People>
<People Key="2" FirstName="Harry" LastName="Jones">
  <PeopleAddresses PeopleKey="2" AddressesKey="2">
    <Addresses Key="2" Street="555 E 5th St" City="Chicago" State="IL" ZIP="23456" />
  </PeopleAddresses>
</People>
<People Key="3" FirstName="Sally" LastName="Smith">
  <PeopleAddresses PeopleKey="3" AddressesKey="1">
    <Addresses Key="1" Street="123 Main" City="St Louis" State="MO" ZIP="12345" />
  </PeopleAddresses>
</People>
<People Key="4" FirstName="Sara" LastName="Jones">
  <PeopleAddresses PeopleKey="4" AddressesKey="2">
    <Addresses Key="2" Street="555 E 5th St" City="Chicago" State="IL" ZIP="23456" />
  </PeopleAddresses>
</People>
'

select t.b.value('./@Key', 'int') PeopleKey,
    t.b.value('./@FirstName', 'nvarchar(50)') FirstName,
    t.b.value('./@LastName', 'nvarchar(50)') LastName
from @xml.nodes('//People') t(b)

select t.b.value('../../@Key', 'int') PeopleKey,
    t.b.value('./@Street', 'nvarchar(50)') Street,
    t.b.value('./@City', 'nvarchar(50)') City,
    t.b.value('./@State', 'char(2)') [State],
    t.b.value('./@Zip', 'char(5)') Zip
from 
@xml.nodes('//Addresses') t(b)

- XML . , ../../ .

+1

XML- /SSIS.

0

SQL Red Gate. , , .

0

Red Gate SQL , , .

-/ - , , / , temp, .

, , , ? ? , ?

0

.

B , . , A!

. ; SSIS . SSIS , -, .

#.

0

script , . PK A ( @@Scope_Identity) - , PK A Identity?

, , , CLR - .

, , : , ) (, B C ).

0

, ? , . , - :

.

OUTPUT, . . .

. , .

T-SQL Script, # SSIS. SSIS.

0

, ( , ). , SSIS / - .

, , , , , "" .

0

.

, " ".

:

  • , , TSQL

:

  • , , - , , ( , )
  • , , , db, .
  • ( , -)
  • , (, , ).

.., ( , )

.

, , , , .

, .

0

More articles:


All Articles