Copying Data From DB1.Table to DB2.Table

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

Express Edition and Compact Edition (2005)

Copying Data From DB1.Table to DB2.Table

Author

Topic

emersion
Starting Member

5 Posts

Posted - 2008-09-04 : 08:54:14

Hello Friends
I want to write a procedure
in which i should be able to pass a DB name as parameter
and it should copy data of table to table of another database

Data From DB1.Table to DB2.Table
DB1 Can be current DB so only DB2 name can be passed as Parameter.

Thnx in Advance

EmersioN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-04 : 09:13:16

insert into db1.dbo.table(columns)
EXEC('select columns from '+@DB2+'.dbo.table')

Madhivanan

Failing to plan is Planning to fail

emersion
Starting Member

5 Posts

Posted - 2008-09-05 : 00:26:51

quote:
Originally posted by madhivanan

insert into db1.dbo.table(columns)
EXEC('select columns from '+@DB2+'.dbo.table')

Madhivanan

Failing to plan is Planning to fail

That's Nice...
I am trying to execute such procedure from C#.Net and connecting to db1 via ADO.net Connection Object,
But this is giving error Invalid Object / Object Not found "value of @DB2"...

EmersioN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-05 : 03:05:57

You need to supply value to the parameter @DB2

Madhivanan

Failing to plan is Planning to fail

emersion
Starting Member

5 Posts

Posted - 2008-09-05 : 05:31:07

I have supplied it...
but its giving error as mentioned previously.

EmersioN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-05 : 06:29:19

Can you post the code you used?

Madhivanan

Failing to plan is Planning to fail

emersion
Starting Member

5 Posts

Posted - 2008-09-05 : 07:38:52

CREATE procedure [dbo].[CopyData]
@DB varchar(200)
as
begin
if @DB <>''
begin
exec EmptyDatabase
exec( 'insert into Medicine select * from ' + @DB + 'dbo.Medicine')
end
end

and I do call this Procedure from C# / ASP.Net Application
as usual

EmersioN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-05 : 09:13:39

That should be

CREATE procedure [dbo].[CopyData]
@DB varchar(200)
as
begin
if @DB <>''
begin
exec EmptyDatabase
exec( 'insert into Medicine select * from ' + @DB + '.dbo.Medicine')
end
end

Also, how did you call the procedure from .NET?

Madhivanan

Failing to plan is Planning to fail

emersion
Starting Member

5 Posts

Posted - 2008-09-08 : 01:09:31

string archiveConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString_Archive"].ConnectionString.Replace("|ARCHIVEDBFILE|", folder + @"\" + dbName + ".mdf").Replace("|DBNAME|", dbName);
lblErrorMessage.Text = "Archive Connection string : " + archiveConnectionString + "<br/>";
conArchiveDB = new SqlConnection(archiveConnectionString);
conArchiveDB.Open();
transactionArchive = conArchiveDB.BeginTransaction();
cmdArchiveDB = conArchiveDB.CreateCommand(); //new SqlCommand("CopyData", conArchiveDB);
cmdArchiveDB.Transaction = transactionArchive;
cmdArchiveDB.CommandText = "CopyData";
cmdArchiveDB.CommandType = CommandType.StoredProcedure;

cmdArchiveDB.Parameters.Add(new SqlParameter("@DB", "DataBase2");
cmdArchiveDB.ExecuteNonQuery();

EmersioN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources