Solved

how to copy data rows from one database to another

Posted on 2011-03-08
5
511 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

12 Experts available now in Live!

Get 1:1 Help Now