how to replace data from another database?

Question

how to replace data from another database?

mak101

Ten Centuries

Points: 1111
More actions
September 6, 2010 at 9:57 am

#157185

I am using Import wizard to import data from SQL server 2000 to SQL server 2005, but everytime I import the data, it appends the data to tables. I want to replace all data with new data, I don't find any option in the wizard, can anyone please help?
Thanks.

Viewing 15 posts - 1 through 15 (of 16 total)

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

On the Import Wizard, when you get to the screen where you select the Source Tables and Views, select the tables you want to copy FROM. Then, click the "Edit Mappings" button. Depending on if the destination table already exists, you will get options to "Delete rows in destination table", or "Drop and re-create destination table". Either of these will accomplish what you're trying to do.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mak101 Ten Centuries Points: 1111 More actions · Answer 2

Thank you found it.

Another question- How do I keep same identity value from original table?

mak101 Ten Centuries Points: 1111 More actions · Answer 3

And I saved it as SSIS package on the server. But I don't find the way to open it. Can you please help?

Thanks.

WayneS SSC Guru Points: 95461 More actions · Answer 4

mak101 (9/6/2010)
Thank you found it.
Another question- How do I keep same identity value from original table?

On that same screen (Column Mappings) is a check box for "Enable Identity Insert". Just check that.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95461 More actions · Answer 5

mak101 (9/6/2010)
And I saved it as SSIS package on the server. But I don't find the way to open it. Can you please help?
Thanks.

Open up BIDS. Start a new SSIS project, or open an existing one. In the Solution Explorer, right-click "SSIS Packages" and select "Add Existing Package". Fill in the server, and click the "..." button beside the package path to select the package. When you click OK, a copy of the package on the server will be made into this project.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mak101 Ten Centuries Points: 1111 More actions · Answer 6

WayneS (9/6/2010)
mak101 (9/6/2010)
Thank you found it.
Another question- How do I keep same identity value from original table?
On that same screen (Column Mappings) is a check box for "Enable Identity Insert". Just check that.

Yes I tried that before even posting it, but it is starting from 1 and auto increament for each new record inserted. What I want to do is, e.g. if in original table id# 15 is removed, I don't want id#15 to be created in destination table.

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 7

WayneS (9/6/2010)
mak101 (9/6/2010)
And I saved it as SSIS package on the server. But I don't find the way to open it. Can you please help?
Thanks.
Open up BIDS. Start a new SSIS project, or open an existing one. In the Solution Explorer, right-click "SSIS Packages" and select "Add Existing Package". Fill in the server, and click the "..." button beside the package path to select the package. When you click OK, a copy of the package on the server will be made into this project.

Note: changes to the package in BIDS will not be reflected in the copy on the server. Once you have made all the changes and validated everything, you need to put a copy of that package back on the server.

You can do that by performing a File | Save Copy As - which will allow you to save a copy to SQL Server and overwrite the existing package on the server.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 8

mak101 (9/6/2010)
WayneS (9/6/2010)
mak101 (9/6/2010)
Thank you found it.
Another question- How do I keep same identity value from original table?
On that same screen (Column Mappings) is a check box for "Enable Identity Insert". Just check that.
Yes I tried that before even posting it, but it is starting from 1 and auto increament for each new record inserted. What I want to do is, e.g. if in original table id# 15 is removed, I don't want id#15 to be created in destination table.

This doesn't make sense, if you turn on that option - then you have to include the original identity column from the source and map that to the identity column in the destination. This allows you to load the data with the original ID values.

What it sounds like you are asking is how to exclude rows from being extracted. To do that, you need to write a query that has the expected results and use that in the data source instead.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

mak101 Ten Centuries Points: 1111 More actions · Answer 9

Jeffrey Williams-493691 (9/6/2010)
mak101 (9/6/2010)
WayneS (9/6/2010)
mak101 (9/6/2010)
Thank you found it.
Another question- How do I keep same identity value from original table?
On that same screen (Column Mappings) is a check box for "Enable Identity Insert". Just check that.
Yes I tried that before even posting it, but it is starting from 1 and auto increament for each new record inserted. What I want to do is, e.g. if in original table id# 15 is removed, I don't want id#15 to be created in destination table.
This doesn't make sense, if you turn on that option - then you have to include the original identity column from the source and map that to the identity column in the destination. This allows you to load the data with the original ID values.
What it sounds like you are asking is how to exclude rows from being extracted. To do that, you need to write a query that has the expected results and use that in the data source instead.

Sorry for the confusion. Let me explain you different way.

In original table, record with id#=15 was deleted, so now when I import this table, what happen is record with id#=16 is imported into destination table with id#15. I want this record imported with id# 16 only. In short I want all data including identity colums intake. Reason is all my identity colums are foriegn keys in other tables and If I din't receive original data it messes up.

mak101 Ten Centuries Points: 1111 More actions · Answer 10

Jeffrey Williams-493691 (9/6/2010)
mak101 (9/6/2010)
WayneS (9/6/2010)
mak101 (9/6/2010)
Thank you found it.
Another question- How do I keep same identity value from original table?
On that same screen (Column Mappings) is a check box for "Enable Identity Insert". Just check that.
Yes I tried that before even posting it, but it is starting from 1 and auto increament for each new record inserted. What I want to do is, e.g. if in original table id# 15 is removed, I don't want id#15 to be created in destination table.
This doesn't make sense, if you turn on that option - then you have to include the original identity column from the source and map that to the identity column in the destination. This allows you to load the data with the original ID values.
What it sounds like you are asking is how to exclude rows from being extracted. To do that, you need to write a query that has the expected results and use that in the data source instead.

How can I open stored package from management studio?

mak101 Ten Centuries Points: 1111 More actions · Answer 11

mak101 (9/6/2010)
Jeffrey Williams-493691 (9/6/2010)
mak101 (9/6/2010)
WayneS (9/6/2010)
mak101 (9/6/2010)
Thank you found it.
Another question- How do I keep same identity value from original table?
On that same screen (Column Mappings) is a check box for "Enable Identity Insert". Just check that.
Yes I tried that before even posting it, but it is starting from 1 and auto increament for each new record inserted. What I want to do is, e.g. if in original table id# 15 is removed, I don't want id#15 to be created in destination table.
This doesn't make sense, if you turn on that option - then you have to include the original identity column from the source and map that to the identity column in the destination. This allows you to load the data with the original ID values.
What it sounds like you are asking is how to exclude rows from being extracted. To do that, you need to write a query that has the expected results and use that in the data source instead.
Sorry for the confusion. Let me explain you different way.
In original table, record with id#=15 was deleted, so now when I import this table, what happen is record with id#=16 is imported into destination table with id#15. I want this record imported with id# 16 only. In short I want all data including identity colums intake. Reason is all my identity colums are foriegn keys in other tables and If I din't receive original data it messes up.

Sorry again, I must have messed up earlier. I tried again with "Enable Identity Insert" checked, and it just worked fine.

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 12

mak101 (9/6/2010)

How can I open stored package from management studio?

You cannot open SSIS packages in SSMS - you have to use BIDS to open the packages and work with them.

Within SSMS - you can only work with specialized packages that are created within SSMS as maintenance plans.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

mak101 Ten Centuries Points: 1111 More actions · Answer 13

I used import wizard in SSMS to import data and stored as package. Can I open the package in SSMS?

WayneS SSC Guru Points: 95461 More actions · Answer 14

mak101 (9/6/2010)
I used import wizard in SSMS to import data and stored as package. Can I open the package in SSMS?

As Jeffrey already said:

Jeffrey Williams-493691 (9/6/2010)

mak101 (9/6/2010)
How can I open stored package from management studio?
You cannot open SSIS packages in SSMS - you have to use BIDS to open the packages and work with them.
Within SSMS - you can only work with specialized packages that are created within SSMS as maintenance plans.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2