copying data from one table to a new one with some different data types

Is it possible to easily copy data from one table to another if the data types don't match.

I know you can do a INSERT INTO table1(col1,col2) SELECT (col2,col7) FROM table2 if the data types match but is there a way to do this if they don't.

I'm not trying to copy date times into bit fields or anything. I just have an old table that I built when I really didn't know what I was doing now I at least

think I have a better understanding of what data types to use, so I was wanting to move the data in the orignal table to my new one. Most of the fields in the old

database are text datatypes and the new database is nvarchar(50) data types.

Thanks for any suggestions.

If the datatypes are "compatible" then you should be okay. As you mentioned moving from text datatype to nvarchar should be okay. Try using the BCP utility if you have millions of rows in the table.|||

I tried doing that and it gives an error saying string or binary data may be lost so the execution was cancelled.

I was able to do what I needed to by coding up a dummy method in C# that read everything into a datatable and then wrote it out to the new database, but this would be something nice to know how to do in T-Sql. Any idea why I was getting the error above. The only other two data types in the table are datetime and bit, but those fields match exactly no conversion should be necessary so the only reason I can think I'm getting that error is because I'm trying to convert a text field to a nvarchar().

Any way thanks for the help ndinakar!

|||

Try this in your query analyzer:

DECLARE @.tTABLE ( col1INT IDENTITY, col2TEXT, col3BIT )INSERT INTO @.tVALUES ('aaaaaaaaaaaaaaaaaa',0)SELECT *FROM @.tDECLARE @.t2TABLE (col1INT IDENTITY, col2NVARCHAR(50), col3BIT )INSERT INTO @.t2SELECT t1.col2, t1.col3FROM @.t t1SELECT *FROM @.t2

|||

I was essentially doing what you suggested ndinakar and was getting an error about truncating string or binary values.

I found this KB article with that error and by turning of ANSI WARNINGS I was able to get my data copied

http://support.microsoft.com/default.aspx/kb/255765

Thanks for your help!