Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Error converting data type varchar to numeric . In Ensert Selection Query

Beantwortet Error converting data type varchar to numeric . In Ensert Selection Query

  • Sunday, February 03, 2013 4:48 AM
     
      Has Code
    Sign In to Vote
    0

    I have Two tabels Transections2 and Transections2, when i Try to Copy Data From one to another.

    I get This Error "Error converting data type varchar to numeric".  

    INSERT INTO Transections2([Date], 
[agent], [ID], [Name], [Department], [Dedication], 
[Description], [Basic Salery], [Bonus], [advance], 
[PreviouseBalance], [S-pyment])
Select  convert(varchar(10),(Date),101),min(Agent),min(Id)as ID,min(name) as Name,
min([Basic Salery])as 'B-salary',
min([Department]),min([Dedication]),min([Description]),
sum(Bonus)as Bonus,sum(advance)as 'Advance',
sum(PreviouseBalance) as 'P-Balance',sum([S-pyment]) as Spyment
From Transections group by ID,Date

    In Select  Only Query is ok,  Put Ensert Salect Query Give me the Error according Date field .

    Please give me advaice,  thenks Allot. 

     

All Replies

  • Sunday, February 03, 2013 4:22 AM
     
      Has Code
    Sign In to Vote
    0

    How i can Solve This Error

    INSERT INTO Transections2([Date], 
[agent], [ID], [Name], [Department], [Dedication], 
[Description], [Basic Salery], [Bonus], [advance], 
[PreviouseBalance], [S-pyment])
Select  convert(varchar(10),(Date),101),min(Agent),min(Id)as ID,min(name) as Name,
min([Basic Salery])as 'B-salary',
min([Department]),min([Dedication]),min([Description]),
sum(Bonus)as Bonus,sum(advance)as 'Advance',
sum(PreviouseBalance) as 'P-Balance',sum([S-pyment]) as Spyment
From Transections group by ID,Date
     
     
    "Error converting data type varchar to numeric" i got this Error my Fields data types
    is Varchar datatype.


     
  • Sunday, February 03, 2013 4:27 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Looks like you put the columns in the wrong order in your select statement. BTW, several of your columns and tables are misspelled, e.g. Salary is misspelled or Transactions. Anyway, without correcting that error you should use:

    INSERT INTO Transections2([Date], 
[agent], [ID], [Name], [Department], [Dedication], 
[Description], [Basic Salery], [Bonus], [advance], 
[PreviouseBalance], [S-pyment])
Select  convert(varchar(10),[Date],101),min(Agent),min(Id)as ID,min(name) as Name,

min([Department]),min([Dedication]),min([Description]),
min([Basic Salery]) as 'B-salary',
sum(Bonus)as Bonus, sum(advance)as 'Advance',
sum(PreviouseBalance) as 'P-Balance',sum([S-pyment]) as Spyment
From Transections group by ID,Date

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


     
  • Sunday, February 03, 2013 4:35 AM
    Answerer
     
     
    Sign In to Vote
    0
    create table #t (id varchar(20))

    insert into #t values ('5656')
    insert into #t values ('aa12')
    insert into #t values ('265')



    select convert(int,id) from #t
    ----Msg 245, Level 16, State 1, Line 1
    ----Conversion failed when converting the varchar value 'aa12' to data type int.


    select
    CASE WHEN PATINDEX('%[0-9]%', ID) = 1 THEN CAST(ID AS INT) END
    from #t

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Sunday, February 03, 2013 4:53 AM
    Answerer
     
     
    Sign In to Vote
    0
    Date field is numeric? Do you have a trigger on Transections2?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

     
  • Sunday, February 03, 2013 6:30 AM
     
     
    Sign In to Vote
    0
    Thank you very mach, you get My mistake
     
  • Sunday, February 03, 2013 12:58 PM
     
     
    Sign In to Vote
    0

    Hi

    You are putting varchar() value into the numeric field so that it raise error .Better if you show us your table definition.

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/