Inserting from another database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inserting from another database

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-05-03 : 05:19:18

Hi

I have an empty sql table in a database (call it database1) that I want to insert from a backup (call it database2)

The SQL script I have have is this

SET IDENTITY_INSERT dbo.MobileDevice ON
INSERT INTO dbo.MobileDevice (MobileDeviceID, UserAgent, Name)
SELECT * from dbo.MobileDeviceOld
SET IDENTITY_INSERT dbo.MobileDevice OFF

This is under the situation where dbo.MobileDeviceOld exists but it does not. What I want to do is insert all the values from dbo.MobileDevice from database 2 into dbo.MobileDevice in database1

Is this possible? Never done this before.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-03 : 05:34:05

provided both databases are in same server you can use this


USE database1
GO
SET IDENTITY_INSERT dbo.MobileDevice ON
INSERT INTO dbo.MobileDevice (MobileDeviceID, UserAgent, Name)
SELECT * from database2.dbo.MobileDevice
SET IDENTITY_INSERT dbo.MobileDevice OFF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-03 : 05:37:07

If the databases are in different servers you need to setup a linked server connection between them and then use query like


USE database1
GO
SET IDENTITY_INSERT dbo.MobileDevice ON
INSERT INTO dbo.MobileDevice (MobileDeviceID, UserAgent, Name)
SELECT * from Server2.database2.dbo.MobileDevice
SET IDENTITY_INSERT dbo.MobileDevice OFF

Server2 is linkedserver connection you set in Server1 to other server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources