Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to copy data rows from one database to another

Posted on 2011-03-08
5
Medium Priority
?
524 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 13

Assisted Solution

by:Rahul Agarwal
Rahul Agarwal earned 1200 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 800 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:Rahul Agarwal
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

705 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