Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kingsofleo
Starting Member
7 Posts |
 Posted - 2012-07-24 : 06:44:53
|
Hi all,I was wondering if there is a way in SQL 2008 to import and export tables from different databases similar to how it works in SQL 2008.For example, I want to export a data table called dbo.TContactsALL from my FinOpsKPI database into my CCI database but I'm not sure how you do this in SQL 2008.Any help would be gfreatly appreciated.Also I have tables in SQL 2008 that I've imported from SQL 2000 but when I try to go into the design view of a table and change one of the rows (add a decimal place) SQL 2008 doesn't seem to allow this. I've attached a picture of the error I get.  Anyone have any ideas about this? Thanks! |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-24 : 07:59:08
|
If both databases are on the same server, you can use the 3 part naming convention to write an insert query, for example like this:INSERT INTO CCI.dbo.TargetTable(col1, col2, col3)SELECT colA, colB, colCFROM FinOpsKPI.dbo.TContactsALL; There are a number of other ways that works across servers as well - SSIS, OPENQUERY, LINKED SERVERS etc.Regarding editing the table, I tried to do what you described on my SQL 2008 and it seems to work - I used the menu "edit top 200 rows". Is that what you meant, or were you trying to change the the schema of the table? In either case, it could be a permissions issue. |
 |
|
|
kingsofleo
Starting Member
7 Posts |
Posted - 2012-07-25 : 05:53:33
|
| Thanks Sunita.For editing, it's when you right click on the datatable and go to design view, if I try and change an existing field from a table we've imported from SQL 2000 it gives that error I sent. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-25 : 07:32:55
|
| Other than the possibility of not enough permissions, I don't have any thoughts or ideas. I am not very familiar with SQL 2000 and I am not able to see the picture from where I am.Hopefully someone else who can see the picture and has ideas on what the issue might be would respond. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-25 : 19:05:53
|
| Now that it is evening here and I am not at a place where they block every image that comes through, I can see the error message screen shot that you posted.The message on the screen says what the problem is. SQL Server needs to drop the table and recreate it to effect the change you requested. But, it is prevented from doing so for one (or both) of these reasons:a) It cannot recreate the table (perhaps because of foreign key constraints etc. - but I am not sure whether that is the reason) If that indeed is the case, you might try removing the foreign key constraints and then trying it again. But I would be very careful - testing it 17 different ways in a dev environment etc.b) Your settings prevent dropping and re-creation of the table. You can change that option in SSMS Tools-> Options -> Designers -> Table and Database Designers and unchecking the "Prevent saving changes that require table re-creation" checkbox. Again, be careful and test it until you are completely satisfied. |
|
|
|
|
|
|
|
|
Import/Export tables in SQL 2008