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!

Copy/move data from TableA in DB1 to TableB in DB2 within the same SQL Server

Hallo,
I thouhgt it would be easily done, but no. I have a copy of data i saved in
another DB while laying out the original Database i want to use in producto
n Envi. The Prodution DB is finally ready and i want to move the 3 columns
of data in the first Databs
e into a table in the new database. I have tried "select into", "copy" with
out success.
The old DBTable has 3 Columns of hundreds of rows that i must move to 3 simi
lar columns in a table (15 columns) in the new database. The old table does
not have an ID, but the new table does.
Can someone help. I want to show this to my boss tomorrow.
Thanks.How would you relate the rows in the two tables to each other?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Omooba" <anonymous@.discussions.microsoft.com> wrote in message
news:B2D46104-7C63-4907-AB44-F8EC6FF860A4@.microsoft.com...
> Hallo,
> I thouhgt it would be easily done, but no. I have a copy of data i saved
in another DB while laying out the original Database i want to use in
producton Envi. The Prodution DB is finally ready and i want to move the 3
columns of data in the first Databse into a table in the new database. I
have tried "select into", "copy" without success.
> The old DBTable has 3 Columns of hundreds of rows that i must move to 3
similar columns in a table (15 columns) in the new database. The old table
does not have an ID, but the new table does.
> Can someone help. I want to show this to my boss tomorrow.
> Thanks.

Copy Table Data via StoreProcedures

Hi All.
How can I easily copy the data from one table of a SQL Database another Table residing on a Different SQL Database (Same server) via a StoredProcedure. Is there something like a copy to command?
Thanks in advance
you should be using DTS for this.
If you are hung up on a stored procedure do something like this
create procedure test as
insert into db2.dbo.tablename
select * from db1.tablename
"Guillermo" <anonymous@.discussions.microsoft.com> wrote in message
news:7CF43898-B81F-4504-BB1C-870906EFCC41@.microsoft.com...
> Hi All.
> How can I easily copy the data from one table of a SQL Database another
Table residing on a Different SQL Database (Same server) via a
StoredProcedure. Is there something like a copy to command?
> Thanks in advance