accesing data from different databases on the same server

[eja2000]

i have a server called ACD10122843Z.
on it i have 2 databases db1 and db2.

i want to import data from db1 into db2
how can i do this?
thanks

 

[Katy44]

Right click db2, choose "All tasks" - "Import data"

Go through the options selecting source and destination etc.

You could also have done this by going into db1 and selecting "Export data".

 

[eja2000]

thanks but i want to do this using tsql..

 

[dbomrrsm]

just need to qualify where data coming from and going to:

insert into test.dbo.MSreplication_options
select *
from master.dbo.MSreplication_options

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

or to create a table in one db from another

select * into newtable
from master.dbo.MSreplication_options

if you run this in QA within the new database it will copy all from master.dbo.MSreplication_options.

[blue]DBomrrsm[/blue]

 

[SQLSister]

Of course if your structures are not identical, you will need to explicity define the columns in the insert statment.

Other things to consider. Do you have exisitng records? Do you want to keep them or delete them prior tot he insert?

Truncate table is good for deleting records quickly but it is not logged, so you can't recover easily from it. ALso the person running the command must have sys admin or dbo rights to the table. If you want ordinatry users to perform this task, it is best to use the delete command.

Also, you may want to update existing records, then insert any which were not inthe dataset originally. If you want to do this, let us know and we can provide sample code.

Questions about posting. See faq183-874