0

I have two databases on the same SQL Server, and they have the same table structure. Database2010 is populated with data and Database2007 is empty.

I need to write a SQL statement that will copy the data from a table in Database2010 and insert it into the same table in Database2007.

I'm thinking something like this:

insert into Database2007.dbo.tablename
([all the fieldnames])
select
[all the fieldnames]
FROM Database2010.dbo.tablename

How do I set permissions in SQL Server to let me access both databases like this?

I've read the other questions about multiple database queries but they were all syntax related, and each had something like "Of course you need to have your appropriate permissions in place for each database," or something like that. Where are those appropriate permissions?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sXe
  • 242
  • 1
  • 4
  • 11
  • I think they are just referring to you having read/write permissions on the tables. – garnertb Oct 11 '11 at 20:57
  • 1
    Just give the user account you are using `SELECT` permission on `Database2010` (all tables needed) and `SELECT` and `INSERT` permissions on `Database2007` (again: for all tables needed)... – marc_s Oct 11 '11 at 21:03
  • Thanks, that was it. I was *way* over thinking this one. But since you answered in a comment, I'm not able to mark it as the right one... – sXe Oct 11 '11 at 21:08

0 Answers0