| Author |
Topic |
|
Marla
Starting Member
3 Posts |
 Posted - 2002-07-21 : 16:44:09
|
| Hope someone can help me because I'm feeling quite clueless about this. I have a create table query, insert into query, add 3 columns and subsequent updates of these three new columns. These work fine. I try and create a history table and I use the exact same code as the initial create table query, add the three additional columns into the new create table and then run an insert into. I get the error: "Error converting data type varchar to numeric". When I run the exact same insert into query below in MS Access, the query works. It's only in Query Analyzer that it doesn't.I've included the history table code below. The three add-on fields are ExtractMonth, ExtractYear and DLQ. I'm thinking it's something with the DLQ field but the datatype is int for both the original table and the history table. *************create table cslp_hist( SSN char(9), SSNSequenceNo char(2), BorrowerStatus char(1), LoanProgram char(1), LenderID char(6), CustID char(4), ExtractMonth char(3), ExtractYear char (4), SchoolType char(1), SubsidyFlag1 char(1), SubsidyFlag2 char(1), SubsidyFlag3 char(1), SubsidyFlag4 char(1), SubsidyFlag5 char(1), SubsidyFlag6 char(1), SubsidyFlag7 char(1), SubsidyFlag8 char(1), SubsidyFlag9 char(1), SubsidyFlag10 char(1), SubsidyFlag11 char(1), SubsidyFlag12 char(1), SubsidyFlag13 char(1), SubsidyFlag14 char(1), SubsidyFlag15 char(1), SubsidyFlag16 char(1), SubsidyFlag17 char(1), SubsidyFlag18 char(1), SubsidyFlag19 char(1), SubsidyFlag20 char(1), SubsidyFlag21 char(1), SubsidyFlag22 char(1), SubsidyFlag23 char(1), SubsidyFlag24 char(1), SchoolID char(6), NextPaymentDate datetime, LastPaymentDate datetime, ForbEndDate datetime, TotalCurrPrinc decimal(10,2), CurrPrincNS decimal(10,2), RepayIntDue decimal(10,2), NonsubIntDue decimal(10,2), IntCapped decimal(10,2), dlq int)gouse stg_CSLPinsert into cslp_histselect may_cslp.* from may_cslpgo*********Any ideas, suggestions, comments? |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-07-21 : 17:56:01
|
| Use an explicit column list and not select * i.e.insert into cslp_hist (column1,column2, etc...)select column1,column2, etc.....from may_cslp HTHJasper Smith |
 |
|
|
Marla
Starting Member
3 Posts |
Posted - 2002-07-22 : 07:32:00
|
| I tried that before trying the .* and I got the same error. Any other ideas? |
|
|
|
rksingh024
Yak Posting Veteran
56 Posts |
Posted - 2002-07-22 : 07:39:34
|
| It seems the order of the column are not exactly same.As you have added three columns in the forst table, these will be added at the last. And in your create table script you have written exactyear and exactmonth in between.Ramesh |
|
|
|
Marla
Starting Member
3 Posts |
Posted - 2002-07-22 : 08:13:28
|
| Even with the correct order on both the create and the insert, I still get the same error. |
|
|
|
|
|
|
Error converting data type varchar to numeric