Solved

how to copy data rows from one database to another

Posted on 2011-03-08
5
512 Views
Last Modified: 2012-05-11
Hi !

I have a Database Db1 in SQL Server 2005. I am creating a new Database Db2 on the same machine in same SQL Server instance. I have 5 columns and more than 100 rows in table1 of Db1 and I want to copy 3 columns and all rows of Table1 and put it in Table2, Db2.

What could be the easiest way to do that ? Thanks.
0
Comment
Question by:pratz09
5 Comments
 
LVL 13

Assisted Solution

by:agarwalrahul
agarwalrahul earned 300 total points
ID: 35068240
Write the Query:

if table not exists in DB2:

Select Column1,Column2, Column3 into db2.tablename from db1.tablename

if Table Exists in DB2:

Insert into db2.tablename Select column1, Column2,column3 from db1.tablename
0
 
LVL 3

Expert Comment

by:zulumike
ID: 35068258
I'm no sql expert but i believe this is one way to do it:
If Db2 and table 2 is an empty new table, you can just copy entire table/db and simply delete the coums you don`t want via sql server management studio.
0
 
LVL 6

Accepted Solution

by:
jonaska earned 200 total points
ID: 35068330
Agree with agarwalrahul. Altough you should use the tre part syntax.
db.tablename will not work.
db.dbo.tablename or db..tablename works better.
Example:
INSERT INTO desDb..destTable  (ID, Text, Comment)
SELECT ID, Text, Comment FROM sorceDb..sourceTable

Open in new window

0
 
LVL 13

Expert Comment

by:agarwalrahul
ID: 35068358
Insert into db2.tablename (column1, Column2,column3)  Select column1, Column2,column3 from db1.tablename
0
 

Author Closing Comment

by:pratz09
ID: 35068378
Thanks guys,

db.dbo.tablename works better... since it is cross Database query, without dbo, it throws object not recognized error for the second database.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now