Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

how to copy data rows from one database to another

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
pratz09
Asked:
pratz09
2 Solutions
 
Rahul AgarwalTeam LeaderCommented:
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
 
zulumikeCommented:
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
 
jonaskaCommented:
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
 
Rahul AgarwalTeam LeaderCommented:
Insert into db2.tablename (column1, Column2,column3)  Select column1, Column2,column3 from db1.tablename
0
 
pratz09Author Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now