-1

So I've got these two tables that I'm trying to copy data between and according to http://msdn.microsoft.com/en-us/library/ms187928.aspx I shouldn't have a problem doing the conversion but it seems that I do. Here are the two tables with the pertinent information for diagnosing this issue: 

Table1
col1(PK, int, not null)
col2(varchar(4608), null)
col3(datetime, null)
col4(datetime, null)
col5(char(12), null)
col6(datetime, null)
col7(int, null)
col8(datetime, null)
col9(char(1), not null)
col10(varchar(25), null)
col11(char(1), null)
col12(char(1), null)
col13(char(1), null)
col14(uniqueidentifier, null)
col15(FK, in, null)

Table2
col1(PK, int, not null)
col2(varchar(4608), null)
col3(datetime, null)
col4(datetime, null)
col5(char(12), null)
col6(datetime, null)
col7(int, null)
col8(varchar(80), null)
col9(varchar(120), null)
col10(datetime, null)
col11(char(1), not null)
col12(varchar(25), null)
col13(char(1), null)
col14(char(1), null)
col15(char(1), null)

Now this is the where the odd business comes into play. Columns 14 and 15 in Table1 correspond to Columns 8 and 9 in Table 2. Other than that, you can clearly see which columns line up according to their data types right? So the code for copying those odd columns is here: (Assume that this is at the bottom of...

SET IDENTITY_INSERT Table2 ON;
DELETE FROM Table2;
INSERT INTO Table2 (
col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15)
SELECT col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13
,CAST(col14 AS VARCHAR(80))
,CAST(col15 AS VARCHAR(120))
FROM Table1;
SET IDENTITY_INSERT Table2 OFF;

And this is the error message I get: 

Conversion failed when converting date and/or time from character string.

What's happening here?

Jfabs
  • 543
  • 4
  • 9
  • 23
  • 1
    Sounds like your columns aren't lining up. Can't prove it because you didn't include the actual code. *shrug* – Aaron Bertrand Apr 26 '13 at 14:04
  • There, I edited it to include the rest of the code. I just didn't think it was important to be honest since you could kind of assume what it would look like. Thanks though. – Jfabs Apr 26 '13 at 14:14
  • 2
    Well, see, you say that col14 and col15 in table1 correspond to columns 8 and 9 in table2. Yet your insert statement puts columns 8 and 9 into columns 8 and 9, and columns 14 and 15 into column 14 and 15. – Aaron Bertrand Apr 26 '13 at 14:16
  • I thought I read somewhere that SQL lines up the data types more so than the column names? – Jfabs Apr 26 '13 at 14:17
  • Please for the love of god tell me those are just example column names an not your actual columns. – Zane Apr 26 '13 at 14:19
  • Gahhh. That was correct. Well, now I feel dumb. A lesson well learned though, the SELECT statement has to line up with the INSERT statement then. Thanks. – Jfabs Apr 26 '13 at 14:19
  • @Zane, yes, they are just examples. – Jfabs Apr 26 '13 at 14:20
  • No, you didn't read that anywhere. If you did, please tell us where, so we can shame them into correction. – Aaron Bertrand Apr 26 '13 at 14:21
  • Well clearly I misinterpreted what was meant here or rather just didn't understand it clearly but check the answer on this one: http://stackoverflow.com/questions/10560386/sql-select-into-a-table-with-different-column-names – Jfabs Apr 26 '13 at 14:23
  • The misunderstanding being that the order didn't matter as long as the data types matched up, but the order does matter. Thank you for clearing that up. – Jfabs Apr 26 '13 at 14:25

1 Answers1

0

In your query col10 (by my cont) is at the same position in the column list and the select statement.

It is varchar in tableanddatetime` in table2.

This may be where your problem lies.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786