CODEWITHSUNDEEP
sql-server-2008t-sqlsql-server-2008-r2
chrj15
chrj15

Reputation: 51

Implicit conversion from data type datetime to tinyint is not allowed. Use the CONVERT function to run this query

I understand the error and its meaning. What I don't understand is why I'm receiving the error since the table I'm inserting into (Table B) is a replica of the table where the data is coming from (Table A). Can you please help explain why I'm receiving the error. Here are the data types for each column of the tables in question.

Table A                                    Table B

ID_TYPE:int                            int
ID_TYPE:int                            int
ID_TYPE:int                            int
ID_TYPE:int                            int
ID_TYPE:int                            int
ID_TYPE:int                            int
ID_TYPE:int                            int
ID_TYPE:int                            int
ID_TYPE:int                            int
ID_TYPE:int                            int
ID_TYPE:int                            int
ID_TYPE:int                            int
numeric(19, 4)                         numeric(19, 4)
ID_TYPE:int                            int
numeric(19, 4)                         numeric(19, 4)
ID_TYPE:int                            int
varchar(10)                            varchar(10)
varchar(128)                           varchar(128)
varchar(1000)                          varchar(1000)
ADDRESS_TYPE:varchar(40)               varchar(40)
ADDRESS_TYPE:varchar(40)               varchar(40)
CITY_TYPE:varchar(28)                  varchar(28)
STATE_TYPE:char(2)                     char(2)
POSTALCODE_TYPE:varchar(11)            varchar(11)
NPA_TYPE:varchar(3)                    varchar(3)
NXX_TYPE:varchar(3)                    varchar(3)
LINE_TYPE:varchar(12)                  varchar(12)
smallint                               smallint
varchar(11)                            varchar(11)
datetime                               datetime
datetime                               datetime
varchar(128)                           varchar(128)
varchar(32)                            varchar(32)
BOOLEAN_TYPE:tinyint                   tinyint
BOOLEAN_TYPE:tinyint                   tinyint
varchar(256)                           varchar(256)
varchar(256)                           varchar(256)
varchar(256)                           varchar(256)
datetime                               datetime
BOOLEAN_TYPE:tinyint                   tinyint
ABANO_TYPE:varchar(9)                  varchar(9)
BANKACCTNO_TYPE:varchar(17)            varchar(17)
QBID_TYPE:varchar(36)                  varchar(36)
QBID_TYPE:varchar(36)                  varchar(36)
BOOLEAN_TYPE:tinyint                   tinyint
int                                    int
USERID_TYPE:int                        int
SYSTEMDATE_TYPE:datetime               datetime
USERID_TYPE:int                        int
SYSTEMDATE_TYPE:datetime               datetime
SWORKSTATIONID_TYPE:varchar(256)       varchar(256)
APPLICATIONID_TYPE:smallint            smallint
ID_TYPE:int                            int
ID_TYPE:int                            int
ID_TYPE:int                            int
varchar(128)                           varchar(128)
varchar(128)                           varchar(128)
char(2)                                char(2)
BOOLEAN_TYPE:tinyint                   tinyint
ID_TYPE:int                            int
BOOLEAN_TYPE:tinyint                   tinyint
ID_TYPE:int                            int
BOOLEAN_TYPE:tinyint                   tinyint
varchar(25)                            varchar(25)
                                       nvarchar(50)

Query

 insert into cogent_firm_specific.dbo.client_grouping

 select MAIN.dbo.client.[NCLIENTID]
      ,main.dbo.client.[NFIRMID]
      ,main.dbo.client.[NCLIENTLIASONID]
      ,main.dbo.client.[NPARENTID]
      ,main.dbo.client.[NCLNTCOSTCODEID]
      ,main.dbo.client.[NFEEAGREEMENTID]
      ,main.dbo.client.[NREMITMETHODCODEID]
      ,main.dbo.client.[NCLIENTSTATUSCODEID]
      ,main.dbo.client.[NTRANSACTSTCODEID]
      ,main.dbo.client.[NEXPORTCODEID]
      ,main.dbo.client.[NARCODEID]
      ,main.dbo.client.[NCOLLECTIONCHRGCODEID]
      ,main.dbo.client.[NCOLLCHRG]
      ,main.dbo.client.[NATTYFEECODEID]
      ,main.dbo.client.[NATTYFEE]
      ,main.dbo.client.[NCOUNTRYCODEID]
      ,main.dbo.client.[SCLIENTNO]
      ,main.dbo.client.[SCLIENTNAME]
      ,main.dbo.client.[SCLIENTLEGALNAME]
      ,main.dbo.client.[SADDR1]
      ,main.dbo.client.[SADDR2]
      ,main.dbo.client.[SCITY]
      ,main.dbo.client.[SSTPROV]
      ,main.dbo.client.[SPOSTALCODE]
      ,main.dbo.client.[SCLNTNPA]
      ,main.dbo.client.[SCLNTNXX]
      ,main.dbo.client.[SCLNTLINE]
      ,main.dbo.client.[NPRIORITY]
      ,main.dbo.client.[STIN]
      ,main.dbo.client.[DTLASTPLCMNT]
      ,main.dbo.client.[DTFIRSTPLCMNT]
      ,main.dbo.client.[SWEBSITE]
      ,main.dbo.client.[SWEBLOGIN]
      ,main.dbo.client.[BRPTASFAMILY]
      ,main.dbo.client.[BBILLASFAMILY]
      ,main.dbo.client.[SFTPSITE]
      ,main.dbo.client.[SFTPLOGIN]
      ,main.dbo.client.[SFTPPWD]
      ,main.dbo.client.[DTLASTSTMT]
      ,main.dbo.client.[BNOPOST]
      ,main.dbo.client.[SCLIENTBANKABANO]
      ,main.dbo.client.[SCLIENTBANKACCTNO]
      ,main.dbo.client.[SQBID]
      ,main.dbo.client.[SQBDEBTORID]
      ,main.dbo.client.[BACTIVE]
      ,main.dbo.client.[NCONCURRENCYID]
      ,main.dbo.client.[NCREATEDBY]
      ,main.dbo.client.[DTCREATEDON]
      ,main.dbo.client.[NCHANGEDBY]
      ,main.dbo.client.[DTCHANGEDON]
      ,main.dbo.client.[SWORKSTATIONID]
      ,main.dbo.client.[NAPPLICATIONID]
      ,main.dbo.client.[NCLIENTCODEID]
      ,main.dbo.client.[NENTITYTYPEID]
      ,main.dbo.client.[NPLAINTIFFID]
      ,main.dbo.client.[SDBA]
      ,main.dbo.client.[SFRIENDLYNAME]
      ,main.dbo.client.[SSTINCORPORATEDIN]
      ,main.dbo.client.[BINVOICENSF]
      ,main.dbo.client.[NAUDITORLOGINID]
      ,main.dbo.client.[BALLOWNEGREMIT]
      ,main.dbo.client.[NCREDITORCLASSIFICATIONID]
      ,main.dbo.client.[BRECOVERCOSTSFIRST]
      ,main.dbo.client.[SCOUNTY]
 FROM main.[dbo].CLIENT
  join COGENT_FIRM_SPECIFIC.dbo.Client_Grouping on Client_Grouping.NCLIENTID = client.NCLIENTID 
 where main.dbo.CLIENT.NCLIENTID is null

Upvotes: 1

Views: 3592

Answers (1)

chrj15
chrj15

Reputation: 51

Thanks Aaron. I'm still not sure what caused the issue, but wrapping the INSERT INTO column list in () did the trick. Thanks again! Aaron, please submit as the answer and I'll accept it.

Upvotes: 2

Related Questions