-4

I am trying to insert data from one SQL table to another. The problem is that the columns have different data types. And when I use Cast() I still get the following error message:

[Msg 8114, Level 16, State 5, Line 3 Error converting data type varchar to bigint.]

Table1 columns have data types of bigint or int while Table2 columns are varchar. Any suggestions?

Here is my query:

INSERT INTO Table1 
        ([RowId],
         [Scenario],
         [Entity],
         [Project],
         [TimePeriod])

SELECT Cast([ProjectMgrID] as bigint), Cast('ACT' as bigint), 
Cast('APP' as bigint), Cast([Project] as bigint), Cast('201801' as int)
FROM Table2
johankent30
  • 65
  • 2
  • 3
  • 11
  • 5
    `Cast('ACT' as bigint)` this tries to cast the literal string "ACT" to bigint, I guess you mean to use Cast([ACT] as bigint). – HoneyBadger Feb 14 '18 at 14:34
  • 1
    How do you want to represent 'ACT' and 'APP' in `integer` terms ? – Abhishek Feb 14 '18 at 14:35
  • 1
    In addition to the above comments, why `CAST(201801' AS int)`? Just type `201801` (note there are no single quotation). That would be treated as an `int`. This makes me wonder what the initial data types of your other columns are; you probably don't need to `CAST` them if you're written a query that casts a literal value integer to an integer. – Thom A Feb 14 '18 at 14:38
  • 1
    Probably time to take a step back and read the documentation or take a quick jaunt through a youtube guide on data types. You're trying to turn a string value `'ACT'` into an integer, which means that you need some more fundamental knowledge than SO can provide. – Jacob H Feb 14 '18 at 14:40
  • I'm not sure the downvotes were needed. I don't think the OP has a made a lack of research, nor is the question not useful; it's simply that they are lacking understanding of the basics and made incorrect choices as a result. – Thom A Feb 14 '18 at 14:47
  • Are you using MSSQL or MySql ? – Ven Feb 14 '18 at 14:49
  • "Why can't I cast a string 'ACT' to an int?" is the question. Further, I am assuming that you know the difference as both column references and strings are in use in the select statement. In any event, it is entirely unclear what you are trying to do. – theMayer Feb 14 '18 at 14:59
  • Thanks for the feedback, obviously I am just starting out trying to learn SQL Server and was under the impression that CAST() could convert any data type to any other desired type. I will do some more research on basic data types – johankent30 Feb 14 '18 at 15:14

1 Answers1

0

use this 

INSERT INTO Table1 
        ([RowId],
         [Scenario],
         [Entity],
         [Project],
         [TimePeriod])

SELECT Cast([ProjectMgrID] as bigint), Cast([ACT] as bigint), 
Cast([APP] as bigint), Cast([Project] as bigint), Cast('201801' as int)
FROM Table2

you are casting string to bigint here Cast('ACT' as bigint), Cast('APP' as bigint) you should give column names in []

Imran Ali Khan
  • 8,469
  • 16
  • 52
  • 77