copying data

[BrianR111613]

does anyone know if it is possible to copy data from one database on a computer to another database on a different computer using query anylizer?

 

[ClaireHCM]

yes..but you need to add the remote server as[linked server] or [remote server].

 

[ClaireHCM]

And enable the "select into/bulkcopy" to "true" in the destination database

example
select * into pubs.dbo.auth from Server2.pubs.dbo.authors
which can copy the whole table from pubs..authors in remte server(server2) to your current pubs database

 

[BrianR111613]

great. thank you.

 

[HolaJohn]

Hello out there
I am new to SQL Server 7 and now runing into dilema about the design. I hope you can point me out to right direction. I have Saleperson table the have foreign key in Address table. And I have customer table that also have foreign key in Address table. Here is the problem: whenever I insert new address for sale person, it is Ok. But when I insert the address for customer, It said that I have violated the foreign key in Saleperson table even I have delete the link in diagram (for example Saleperson -N:0- Address -0:N- Customer). Here is my first question: Do I need to create a separated reference address table for Saleperson and for customer table? if this was a case then SQL is really s!!!

Second question: I let the key of saleperson talbe as auto increment number that starts from 1. And I let the key of customer as auto increment number that starts from 300. My question here is what happen if the saleperson key meet (or equal to) the customer key in address table. I have tried to let both increment number keys of saleperson and customer start from 1 but this approach does not work because SQL mistake the foreign key between both tables.

Can anyone please give me some direction in this design. I think that I am in very deep chili now!!!
Anyhelp is greatly appreciated. thanks you very much in reading my message.

John

 

[dky1e]

how do you "enable the "select into/bulkcopy" to "true" in the destination database" ?

 

[ClaireHCM]

dky1e,
sp_dboption 'dbname','select into/bulkcopy','true'

HolaJohn,
Can you specify which are the tables possessing PK which are possessing FK.How is this PK related to that FK and so on...

And also be more specific about the column defined in the PK and FK.

 

[ClaireHCM]

dkyle,
If the user is a SA/dbowner for the destination database.He can enable it.
If not,you can use DTS to do the task.Or just do a insert and select statement to do it.