How to insert data from one database to another

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

How to insert data from one database to another

Author

Topic

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-05-05 : 16:29:37

Hi,
As a part of server migration from sql server 2005 to sql server 2008, I need to migrate the data.

I've 2 databases: myproj & mytestproj

Now, I want to copy certain data from mytestproj database to myproj database. Can any one tell me how to perform this task?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-05 : 16:31:48

You can use a linked server:

INSERT INTO LinkedServerName.myproj.dbo.SomeTable (Column1, ...)
SELECT Column1, ...
FROM mytestproj.dbo.SomeTable
...

If you intend to copy everything, then I'd recommend backup/restore or detach/attach.

SSIS is also a good choice, although I prefer to use bcp.exe for bulk imports/exports.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

freephoneid
Yak Posting Veteran

52 Posts

Posted - 2010-05-05 : 16:34:03

Hi tkizer,
Thanks for the super fast response!! Can you please tell me how to configure linked server as I'm new to sql server? Also, I need to copy only specific rows from certain tables only.

Also, what is SSIS & bcp.exe as I couldn't find bcp.exe in my server?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-05 : 16:37:32

Linked servers are documented in Books Online. To give you an idea of where to add them in the GUI: in SSMS, expand Server Objects and then you'll see linked servers.

SSIS is an ETL tool that allows you to import/export data from many different sources. It can be a very complex tool or a simple tool, just depends on what you need from it.

Bcp.exe does exist on your server if you have the SQL client tools installed. It's a command line executable that allows you to import/export data.

Another choice for you is to use the import/export wizard directly in SSMS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sreez
Starting Member

1 Post

Posted - 2010-05-05 : 18:00:13

Tara,

Is there a way to copy data from a table in one database to another table a second database, where the 2 databases are in two different servers.

I know SSIS can be used, but my client want to use bcp or other similar commands that could be run from command prompt.

Thanks & Regards
Sree

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-05 : 18:08:45

Sreez, yes that's possible. See my comments above.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources