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 |
|
tcv56
Starting Member
12 Posts |
 Posted - 2007-02-02 : 09:56:20
|
| Hi all,background:I use export/import wizard to copy data from one tableA to a differnt db tableB(test) 1-on-1. Tables are pretty much the same(type)on all field except the destination table has a field called 'value' varchar 255. This field in the design view has a [] around it automatically included when i add the field.I use query method to copy data and the order of the data is pretty much the same in both tables.The problem:I did not have any problem using the same method on another table. Howerver, I got TransformCopy'DirectCopyXform' conversion error on this table.It also reads Source column 'Value'(DBTYPE_STR), destination column 'State' (DBTYPE_I4).'State' is a column from another table (not in the two I want to copy)Obviously, it was a miss match of type and a wrong column to copy to but the question is how on earth that happened with the wizard and how do I get around this. Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-02 : 13:12:15
|
| Go into the transformation and remap the columns. It doesn't always align them properly. Are these databases on the same server? If so, it would be much simpler/easier to just use T-SQL to move the data. Are you moving all data? If so, then the best method to use is BACKUP/RESTORE. If a subset, then INSERT INTO/SELECT using the 3 part naming convention.Tara Kizer |
 |
|
|
tcv56
Starting Member
12 Posts |
Posted - 2007-02-02 : 17:53:23
|
| These dbs are not in the same server and I am just copy one table over. I came in the transformation tab and make sure all the DTS and DTSS are correctly mapped but on the other tab (columns mapping)the columns were not reflecting the change I made in the transformation area. I try to refresh but no lucks. The part that really bothered me was the destination columns were from some other tables (within the db but not related to the 2 I am trying to work with). Any thing else I can do? Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-02 : 17:56:45
|
| Delete the transformation then readd it.I prefer to use T-SQL when I can instead of DTS or bcp. So for your problem, what I would do is create a linked server, then use the four part naming convention.If linked server on source server:INSERT INTO LinkedServerName.DatabaseName.ObjectOwner.TableName (...)SELECT ...FROM ObjectOwner.TableNameIf linked server on destination server:INSERT INTO ObjectOwner.TableName (...)SELECT ...FROM LinkedServerName.DatabaseName.ObjectOwner.TableNameTara Kizer |
|
|
|
tcv56
Starting Member
12 Posts |
Posted - 2007-02-05 : 12:13:24
|
| OK, You keep saying something about Transact SQL, but pardon me for my lack of knowledge I only have SQl server enterprise manager 2000 and I have studio express 2005 on my local machine. My db is 2000. How do I start with what you are saying? I cant find anything in my Enterprise Mger 2000 and Studio Express does not come with import/export. Can you take me through steps by steps? Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-05 : 12:23:02
|
| I've shown you the T-SQL way of transferring data. It's in my last post. You run those commands in Query Analyzer. If you have Enterprise Manager, then you also have Query Analyzer. Then if you need to this to run on a scheduled basis, you can either wrap it into a stored procedure and call that from a job or paste the code in a job.Tara Kizer |
|
|
|
dksit_05
Starting Member
3 Posts |
Posted - 2008-08-22 : 06:10:17
|
quote:
Originally posted by tcv56
Hi all,background:I use export/import wizard to copy data from one tableA to a differnt db tableB(test) 1-on-1. Tables are pretty much the same(type)on all field except the destination table has a field called 'value' varchar 255. This field in the design view has a [] around it automatically included when i add the field.I use query method to copy data and the order of the data is pretty much the same in both tables.The problem:I did not have any problem using the same method on another table. Howerver, I got TransformCopy'DirectCopyXform' conversion error on this table.It also reads Source column 'Value'(DBTYPE_STR), destination column 'State' (DBTYPE_I4).'State' is a column from another table (not in the two I want to copy)Obviously, it was a miss match of type and a wrong column to copy to but the question is how on earth that happened with the wizard and how do I get around this. Thanks
Hi Can u please remove the portion from your .xls sheet which has no data. insort just select those rows from next to last datarow.and delete it. Now try to transfer the data ,it will be done. Thanks Darshan Shah |
|
|
|
|
|
|
|
|
conversion error in exporting