Copy database table with data from one database to another empty data base

Question

Post reply

Copy database table with data from one database to another empty data base

manaprime

SSChasing Mays

Points: 650
More actions
May 16, 2008 at 4:46 am

#123910

I am using sqlserver2005 express and i need to Copy data base table with data from one database to another empty data base.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

jaypee_s Right there with Babe Points: 793 More actions · Answer 1

Hi

Create a full database backup and restore in the new database.

Thanks

Jaypee.s

manaprime SSChasing Mays Points: 650 More actions · Answer 2

No i cant restore database.The exact requirement is that i need to copy only one table with data with same primary keys to another database using sqlserver 2005 express.

alimcitp SSC Eights! Points: 930 More actions · Answer 3

Please see the instructions to accomplish the desired object:

- BCP operating system command to export the data into the text file.

- Right-Click the table and choose Script Table As -> new Query Window

- Create the table Execute that script in Empity database.

- Use BCP or BULK INSERT to import the data into the new table from text file.

Hope this helps, any more question please write.

Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate

manaprime SSChasing Mays Points: 650 More actions · Answer 4

I accomplish this task of copy table from one db to another by using

1. script to create table structure into another database

2. insert into new data base and select from old database technique

the problem is with primary key as i need the same primary auto incremented key values on both tables but new table got new values for autoincremented primary key.

jaypee_s Right there with Babe Points: 793 More actions · Answer 5

Hi

Here the concept is to

SET IDENTITY INSERT ON and SET IDENTITY INSERT OFF

Please check the link, i guss it will solve you problem,

http://www.sqlteam.com/article/how-to-insert-values-into-an-identity-column-in-sql-server

Thanks

jaypee.s

sridhar_varaha SSC Journeyman Points: 75 More actions · Answer 6

I am having 21, 98, 01, 000 records in a table and i need to move this data into another database.

I tried in the following ways:

1. Insert into A (dest columns)

select * from b

It took 3 hours and gave a message 'transport layer'

2. Using Export utility.

It is taking 1, 00, 00, 000 records 25 mins, it mean to complete this it has to run 8 hrs, again the same message will come.

So, is there any other way to do this task faslty.

Thanks

Sridhar

abmore Hall of Fame Points: 3628 More actions · Answer 7

You can use Import Export wizard, where you can specify the source database & table and Destination database and table( or create the new table in destination database )

CrazyMan SSChampion Points: 12560 More actions · Answer 8

Hi Mana

Use BCP this is the best option if there are large number of rows, i have used and it proved to be quite effective for me

Cheers