Solved

converting integer to varchar in SQL server 2008

Posted on 2010-08-16
2
434 Views
Last Modified: 2012-05-10
i have table data in which there are 3 integer data type columns. i have to convert them to character and insert in to another table.  In the below SQL the two columns in where clause are integer values. I have to convert them 'WO' and 'S':

SELECT [ID]
      ,[OrderNumber]
      ,[OperationsSequenceNumber]
      ,[TransactionCode]
      ,[LogTimeStamp]
      ,[EmployeeID]
      ,[QtyIn]
      ,[QuantityCompleted]
      ,[QuantityRejected]
      ,[Result]
      ,[ReasonID]
      ,[StartTime]
      ,[EndTime]
      ,[WeightingFactor]
      ,[SQLUpdated]
      ,[WoDescription]
      ,[Status]
  FROM [CVDProduction].[dbo].[OperationTransactions]
  WHERE [TransactionCode] = 2
  AND [Status] = 90
GO
0
Comment
Question by:maheshpappu
2 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 33450708
You mean something like the following, or is there more to it?
SELECT [ID]
      ,[OrderNumber]
      ,[OperationsSequenceNumber]
      ,'WO'
      ,[LogTimeStamp]
      ,[EmployeeID]
      ,[QtyIn]
      ,[QuantityCompleted]
      ,[QuantityRejected]
      ,[Result]
      ,[ReasonID]
      ,[StartTime]
      ,[EndTime]
      ,[WeightingFactor]
      ,[SQLUpdated]
      ,[WoDescription]
      ,'S'
  FROM [CVDProduction].[dbo].[OperationTransactions]
  WHERE [TransactionCode] = 2
  AND [Status] = 90
GO

Open in new window

0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 33450717
To insert into an existing table:INSERT INTO SomeTable (WO, S)SELECT CONVERT(varchar(100), TransactionCode), CONVERT(varchar(100), Status)FROM [CVDProduction].[dbo].[OperationTransactions]Or to just have your original query create a new table:SELECT [ID]      ,[OrderNumber]      ,[OperationsSequenceNumber]      ,CONVERT(varchar(100), [TransactionCode]) AS WO      ,[LogTimeStamp]      ,[EmployeeID]      ,[QtyIn]      ,[QuantityCompleted]      ,[QuantityRejected]      ,[Result]      ,[ReasonID]      ,[StartTime]      ,[EndTime]      ,[WeightingFactor]      ,[SQLUpdated]      ,[WoDescription]      ,CONVERT(varchar(100), [Status]) AS SINTO NewTableName  FROM [CVDProduction].[dbo].[OperationTransactions]  WHERE [TransactionCode] = 2  AND [Status] = 90
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

774 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question