-1

I have two sqlserver database both having same database and tables structure now I want to insert data from one specific table to other database table, both database table having same structure but user for both database is diffrent I tried with this query but this does not work

insert into Database1.dbo.Audit_Assessment1 
select * from Database2.dbo.Audit_Assessment1

Please help me

Neeraj Kumar Gupta
  • 2,157
  • 7
  • 30
  • 58

4 Answers4

0

SQL Server Management Studio's "Import Data" task (right-click on the DB name, then tasks) will do most of this for you. Run it from the database you want to copy the data into.

If the tables don't exist it will create them for you, but you'll probably have to recreate any indexes and such. If the tables do exist, it will append the new data by default but you can adjust that (edit mappings) so it will delete all existing data.

Pulled from https://stackoverflow.com/a/187852/435559

Community
  • 1
  • 1
Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155
0

1-You can use Linked server , set up it on view option on top left and select registered server . Then you can open a new query window and write your query. 2-You can use replication. Snapshot replication if it is just one time or sometimes . Transactional replication if your insert is repeatedly.

Read more about replication : http://msdn.microsoft.com/en-us/library/gg163302.aspx

Read more about linked servers : http://msdn.microsoft.com/en-us/library/aa560998.aspx

Amir Keshavarz
  • 3,050
  • 1
  • 19
  • 26
0

Try approaching this differently. Why not script out the table you need and manipulate that way?

From the scripted out insert statement you should be able to easily modify this to go into your new database.

Alec.
  • 5,371
  • 5
  • 34
  • 69
0

Sounds like your login doesn't have insert permissions on Database2.dbo.Audit_Assessment1. The error about it being an invalid object name is probably because you don't currently have view definition permissions either.

MLT
  • 524
  • 7
  • 19