Help with copying tables in Sql Server 2005 1

[ironhide1975]

Can someone direct me to a proper method of copying over tables and data from one database to another in SQL Server 2005. I've tried to use the export function, but I keep getting the rows of data appended on the end of the table instead of being replaced.

 

[mrdenny]

Use the same procedure. After you check the table that you want to move, click on the edit transformations button at the bottom. Then change it from append to delete existing records.

This will need to be done for each table in the database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ironhide1975]

Here's what I do

1) Connect to two databases
2) Export Data on database 1
3) Select Source Database 1, Destination Database 2
4) Select tables I want to copy
5) Select 'Edit Mappings' on all select tables
6) Click Drop and recreate new destination tables, delete rows in existing database, and enable identity insert
7) Execute

When the process finishes, I get the same exactly records twice at the end of the tables. For example

1 A
2 B
3 C
1 A
2 B
3 C

 

[mrdenny]

Check the source table and make sure it doesn't have two sets of records.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ironhide1975]

It doesn't

 

[mrdenny]

Try running SQL Profiler on both machines and see what's going on.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ironhide1975]

I'm using SQL 2005 I don't think Profiler exists anymore.

 

[mrdenny]

Profiler does still exist. In the managment studio click Tools > SQL Server Profiler. Or Start > Progams > SQL Server 2005 > Performance Tools > SQL Server Profiler.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ironhide1975]

Ok so what do I with profiler then? (sorry still very new at sql)

 

[mrdenny]

Open profiler, connect to the servers (you'll need to setup one trace for each server). Leave the other settings as default.

Then start the data transfer and this will show exactly what commands the SQL Servers are running so you'll be able to see if the inserts are being done twice.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ironhide1975]

I think we're getting to the limitations of my SQL server knowledge. I was mistaking Profiler for the Query Analyzer in 2000. What exactly does Profiler do and do you know of any demos on how to use it?

Thank you again for your patience.

 

[mrdenny]

SQL Profiler allows you to watch the SQL Commands as they are being executed as well as see how long each command takes to execute.

Sorry I don't know of any demos for using it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ironhide1975]

Ok so I went to open up the trace for the one server, but I don't have system admin privledges to the one server. I'm going to forward this to the help desk here at work and see if they can help me with this testing. Any recommendations you can make elsewise are appreciated. Thank you for your help on this.

 

[mrdenny]

The profiler output is the next troubleshooting step. Have a DBA run profiler and see what's happening.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jojacoder]

Hi all...

I have the exact problem as ironhide1975. I try to do a simple "Import Data" of a few tables in one database to another on the same server. I have been succesful in getting it to drop tables when I only select one table at a time, but as soon as I select more than one table it just ignores the "Delete rows in existing destination tables".

- jojacoder

 

[jojacoder]

I just found out womething really strange! If I do the same procedure but select the "Delete rows in destination table" for every table I get it to work. I tried this a couple of times to verify that I don't do anything else different, but I came to the same result every time... Could this parhaps be a bug in the GUI? Or does anyone have another explanation?


- jojacoder

 

[ironhide1975]

So you can't do the multiple select of tables and use the multiple select options box, you have to manually select each table, then select options and this will work?

 

[jojacoder]

You have to enter the "Edit Mappings..." for each and every table you want to have the "Delete rows to the destination table"-checkbox checked. At least that's the solution for me... not fun if you have lots of tables to copy.

- jojacoder

 

[ironhide1975]

So individual editing works fine, but group editing of tables has a bug in it.

Please confirm.

 

[ironhide1975]

I've talked with our local DBA and he even think this is a bug within Sql Management Studio. Does anyone know of a way we can confirm this with Microsoft?