transfering 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

SQL Server 2005 Forums

Transact-SQL (2005)

transfering data from one database to another

Author

Topic

Gyte
Starting Member

23 Posts

Posted - 2007-11-30 : 08:03:16

Hi all,
In an ASP-page I would like to transfer content of table in a SQL-Server database to another table in another SQL-Server database.

I don't want to make use of opening RecordSets, because the amount of data is very large and this would take too much time.

So, is there another way?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-30 : 08:06:49

If both databases on same server, its pretty simple insert statement:

Insert into Dest.dbo.tbl1(col1, col2...)
Select col1, col2,...
from Source.dbo.tbl2 s LEFT JOIN Dest.dbo.tbl1 d
on s.keycol = d.keycol
where d.keycol is null

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-11-30 : 08:32:59

if the data is very large, I would use bcp.

elsasoft.org

Gyte
Starting Member

23 Posts

Posted - 2007-11-30 : 08:42:34

quote:
Originally posted by harsh_athalye

If both databases on same server, its pretty simple insert statement:

Insert into Dest.dbo.tbl1(col1, col2...)
Select col1, col2,...
from Source.dbo.tbl2 s LEFT JOIN Dest.dbo.tbl1 d
on s.keycol = d.keycol
where d.keycol is null

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

I did what you wrote and I get the following error message :

Msg 916, Level 14, State 1, Line 1
The server principal "SQLtools4salecontrol" is not able to access the database "forleadsdevelop" under the current security context.

The databases in our server are username and password protected. However, I do know what the username and password of the different databases are.

Gyte
Starting Member

23 Posts

Posted - 2007-11-30 : 08:44:32

quote:
Originally posted by jezemine

if the data is very large, I would use bcp.

elsasoft.org

You can only use bcp for text files. Im my case the data source is a table in a SQL-Server database.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-30 : 08:51:16

Both databases are on the same server? or different one?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Gyte
Starting Member

23 Posts

Posted - 2007-11-30 : 09:08:03

Both databases are on the same server.

Gyte
Starting Member

23 Posts

Posted - 2007-12-03 : 03:12:44

Dear Harsh Athalye,

If the databases (on the same server) are username and password protected how do I reach these databases from ASP ?
I want to use one SQL-query to transfer data from one database to the other.

Please help me with this. I would be very gratefull if you had an answer.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-03 : 03:16:21

As far as I know, password protection at database level is only available in SQL Server Compact Edition. Are you using SQL Server CE?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Gyte
Starting Member

23 Posts

Posted - 2007-12-03 : 03:40:57

No, I use SQL server 2005.
I have to log in with username and password each time I access a different database.
Perhaps this could also be a rights issue.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-03 : 09:30:55

quote:
Originally posted by Gyte

quote:
Originally posted by jezemine

if the data is very large, I would use bcp.

elsasoft.org

You can only use bcp for text files. Im my case the data source is a table in a SQL-Server database.

it's a two step process.

1. use bcp to export data from the source table to a text file.
2. use bcp to import the text file to the destination.

elsasoft.org

ayasin
Starting Member

2 Posts

Posted - 2007-12-15 : 17:34:15

I have a question, I am using sql server CE. How can I use bcp OR can I use bcp at the command level or in sql server manager to load into sql server CE. I have been getting errors. If you think it can be done, please incluse a simple example . Thanks

quote:
Originally posted by jezemine

quote:
Originally posted by Gyte

quote:
Originally posted by jezemine

if the data is very large, I would use bcp.

elsasoft.org

You can only use bcp for text files. Im my case the data source is a table in a SQL-Server database.

it's a two step process.

1. use bcp to export data from the source table to a text file.
2. use bcp to import the text file to the destination.

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-15 : 21:12:59

this seems to indicate that bcp works with sql server mobile 2005. I don't really know though as I have never worked with mobile.

http://www.microsoft.com/sql/editions/sqlmobile/sqlmobile.mspx

from the link: "Bulk copy program (BCP) file consumption. Developers are able to improve scalability for initial synchronization and performance for dynamically filtered publications by using snapshots that contain precreated .bcp files."

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources