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!
Solved
how to copy data rows from one database to another
Posted on 2011-03-08
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.
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.
[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
2
- +1
5 Comments
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
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
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.
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.
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:
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
Featured Post
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…
Suggested Courses
Course of the Month7 days, 1 hour left to enroll
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.