how to copy data from table of one database to another same database's table in sql server 2005

Question

0.00/5 (No votes)

See more:

actually i am developing a project for a company.so my client wants that all the transaction of today date copy to main database that has all the transactions.
and don't want to share his all transaction to others expect today date .

so i want copy the data of today date to main database that has same tables day by day.
so plz help me as soon as possible....
thanks

Posted 15-Jul-12 23:15pm

mayank1290

Updated 1-Feb-17 12:42pm

v2

Add a Solution

3 solutions

Solution 2

Hi,

to insert todays records in another table try following query
create strored procedure for this Query

SQL

Declare vchrScript as nvarchar(max)
set vchrScript='insert into Db2.dbo.table1(col1,col2,col3)(select col1,col2,col3 from Db1.dbo.table1 where colDate=getdate())'
Execute sp_executesql @vchrScript

hope it help u
Best Luck

Posted 15-Jul-12 23:58pm

Nilesh Patil Kolhapur

Updated 22-Jul-12 2:01am

v3

Comments

mayank1290 16-Jul-12 7:05am

first of all thanks for reply ....
but i want to transfer data into another database's table.another database is also exist into same project.i have used ...

SqlCommand cmd = new SqlCommand("Insert Into Db2.mdf..student (name, address,date)Select name, address,date From Db1.mdf..student", con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
but it give the error--
Could not find server 'Db1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

so plz give me solution..

Nilesh Patil Kolhapur 16-Jul-12 7:47am

u have to change connectionstring to do this.
Karthik Harve's ans create new table every time check it out

mayank1290 18-Jul-12 2:26am

i have two database one is Db1 another is Db2.
i want to copy of data of Db1's table student into Db2's table student on Button Click event....

so please give me solution...

Nilesh Patil Kolhapur 18-Jul-12 3:49am

try this
insert into dbo.Db2.table2(col1,col2,col3)(select col1,col2,col3 from dbo.Db1.table1 where colDate=getdate())

mayank1290 18-Jul-12 7:49am

actually my database name is Db1.mdf and Db2.mdf and both have same table names.and server is also same because i am using in same project.
but every time i get same error-"Invalid object Db1.dbo.student"

Nilesh Patil Kolhapur 18-Jul-12 8:33am

try this insert into Db2.dbo.table1(col1,col2,col3)(select col1,col2,col3 from Db1.dbo.table1 where colDate=getdate())

Nilesh Patil Kolhapur 22-Jul-12 8:01am

check updated solution

Solution 3

Guys,
I was trying this for hours finally found the solution for this,
please use sa credentials in your connection string or use a common user credential for both databases.

Posted 1-Feb-17 12:42pm

Member 12092495

Comments

[no name] 1-Feb-17 18:49pm

No! Never, ever use the sa account!

Dave Kreskowiak 1-Feb-17 19:22pm

Don't add an answer to a FIVE YEAR OLD question that was already answered.

On top of that, you're answer isn't an answer at all. You NEVER EVER IN YOUR LIFE use the SA account for anything other than installing the SQL server. After that, it's not to be used for anything at all, well, unless you want to risk destroying the server with it.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

*Karthik Harve* · Accepted Answer · 2012-07-15T23:38:00

Solution 1

Hi,

try like this,,

SQL

SELECT * INTO Table2 FROM Table1 WHERE Date = GETDATE()

refer this for more
http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/[^]

hope it works.

Posted 15-Jul-12 23:38pm

Karthik Harve

Updated 15-Jul-12 23:46pm

v2

Comments

mayank1290 16-Jul-12 7:01am

first of all thanks for reply ....
but i want to transfer data into another database's table.another database is also exist into same project.i have used ...

SqlCommand cmd = new SqlCommand("Insert Into Db2.mdf..student (name, address,date)Select name, address,date From Db1.mdf..student", con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
but it give the error--
Could not find server 'Db1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

so plz give me solution..

Karthik Harve 16-Jul-12 7:08am

does the two databases are in same server..??
if yes, try like this..

SELECT * INTO Db1.dbo.Table2 FROM Db2.dbo.Table1 WHERE [CONDITION]

if No, i think you cannot execute in a single query with single connection

mayank1290 16-Jul-12 7:16am

@karthik
i have use but it give the error---Invalid object name 'Db1.dbo.student'.

my code is this--
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;
public partial class _Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Order"].ConnectionString);

protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("SELECT * INTO Db2.dbo.student FROM Db1.dbo.student", con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

}
}

Karthik Harve 16-Jul-12 7:31am

"Db1" i just used for sample. replace with your actual database name. in positions of both db1 and db2 use your database names.

mayank1290 18-Jul-12 2:21am

@karthik i have two database one is Db1 another is Db2.
i want to copy of data of Db1's table student into Db2's table student on Button Click event....

so please give me solution...

Karthik Harve 18-Jul-12 2:28am

try this,

INSERT INTO Db2.dbo.Student SELECT * FROM Db1.dbo.Student

It must work

mayank1290 18-Jul-12 5:42am

same error found---invalid object Db1.dbo.Student

Karthik Harve 18-Jul-12 5:50am

if Db1 and Db2 are valid database names, then the query will definetly works.

mayank1290 18-Jul-12 5:56am

both are valid names but it is not working.
can we copy the data from one database to another through ASP.NET.
my code is this...

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Order"].ConnectionString);
SqlConnection con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["Order1"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("SELECT * INTO Db2.dbo.student FROM Db1.dbo.student", con1);
con1.Open();
con.Open();
cmd.ExecuteNonQuery();
con1.Close();
con.Close();

}

Karthik Harve 18-Jul-12 6:04am

"SELECT * INTO Db2.dbo.student FROM Db1.dbo.student"
this query will create table "Student" in Db2 database with same structure as Db1 Student. and it will also copy all the data.

if the Student tabel in db2 already existing then use below query.
"INSERT INTO Db2.dbo.Student SELECT * FROM Db1.dbo.Student"

do not use the first query, because it always creates the new table. better you use the second query.

mayank1290 18-Jul-12 6:50am

same error--object invalid
let me confirm about connection string...actually for two databases i use two connection string.
so which object is used for opening and closing connection and passing..
SqlCommand cmd = new SqlCommand("SELECT * INTO Db2.dbo.student FROM Db1.dbo.student", con1);

Karthik Harve 18-Jul-12 6:56am

"INSERT INTO Db2.dbo.Student SELECT * FROM Db1.dbo.Student"
this query will work for any of the connections strings unless the server name is same. no need of two connection strings. use your regular connection string and try. remember both Db1 and Db2 must in same server.

mayank1290 18-Jul-12 7:21am

thank for reply Mr. Karthik
actually my database name is Db1.mdf and Db2.mdf and both have same table names.and server is also same because i am using in same project.
but every time i get same error-"Invalid object Db1.dbo.student"