copy data from one table to another db table

[patrichek]

Hi,
I have 2 databases in ms sql server that have the same tables and fields. how can i copy/add the data from select tables in one db to the same tables in the other?

thanks!

 

[SQLDenis]

insert into db2.dbo.table
select * from db1.dbo.table


“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[patrichek]

Thanks Denis,
will this just add onto what's already in db2 or overwrite everything?

can you walk me through it? i'm completely new to ms sql and need my hand held
thanks!

 

[SQLDenis]

It will add, you can't overwrite a table in SQL (unless you drop it first,recreate it and populate it afterwards)
You can update a table (meaning changing data)
you can delete/truncate a table(meaning taking out data)
And you can insert data (meaning appending)

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[patrichek]

Sorry for such juvenile questions but where do i run this command from?

insert into db2.dbo.table
select * from db1.dbo.table

 

[SQLDenis]

Query Analyzer

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[patrichek]

Thanks!
My last question(s) is (well maybe my last!). I want to give a user the priviledges to do this copy, what access do I allow them? And can i install E Mngr on their workstation?

 

[SQLDenis]

GRANT INSERT
ON db2.dbo.table
TO Mary, John, Tom
GO

GRANT SELECT
ON db1.dbo.table
TO Mary, John, Tom
GO


Lookup GRANT in Books On Line (BOL)

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[Nakis]

What if the two databases are on twp different servers?
Do I have to export the data into a flat file first and then import it into the second database?
Is there any alternative way?
Which is the fastest?

thanx

 

[SQLDenis]

you create libked servers and then
insert into server2.db2.dbo.table
select * from server1.db1.dbo.table

lookup linked servers in BOL

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/