我的选择是从 table 1
插入到 table 2
,当我运行它时,我收到以下消息:
消息8114,级别16,状态5,行5错误将数据类型varchar转换为数字 .
我从varchar转换为numeric的列是:
-
titulos
-
cambio
-
liquido
-
resultado
如果我从选择中删除这些列,它可以正常工作 .
有人可以帮忙吗?
这是实际选择:
INSERT INTO SICAVS1_Transacciones_con_ISIN
(tipo_operacion, fecha, cod_operacion,
nombre, titulos, cambio, liquido,
resultado, ISIN )
SELECT DISTINCT st.tipo_operacion
, st.fecha
, st.cod_operacion
, st.nombre
, cast(st.titulos as DECIMAL(16,2))
, cast(st.cambio as DECIMAL(16,2))
, cast(st.liquido as DECIMAL(16,2))
, cast(st.resultado as DECIMAL(16,2))
, st.ISIN
FROM temp_Transacciones st WHERE NOT EXISTS
(SELECT 1
FROM SICAVS1_Transacciones t2
WHERE t2.tipo_operacion = st.tipo_operacion
AND t2.fecha = st.fecha
AND t2.cod_operacion = st.cod_operacion
AND t2.nombre = st.nombre
AND t2.ISIN = st.ISIN)
这是SICAVS1_transacciones_con_ISIN [dbo]的表格方案 . [SICAVS1_Transacciones_con_ISIN]
[ID] [int] IDENTITY(1,1) NOT NULL,
[tipo_operacion] [varchar](30) NULL,
[fecha] [varchar](10) NULL,
[cod_operacion] [varchar](6) NULL,
[nombre] [varchar](32) NULL,
[titulos] [decimal](16, 2) NULL,
[cambio] [decimal](16, 2) NULL,
[liquido] [decimal](16, 2) NULL,
[resultado] [decimal](16, 2) NULL,
[ISIN] [varchar](20) NULL,
[fecha_valor] [date] NULL,
[type] [varchar](14) NULL,
[categoria_1] [char](35) NULL,
CONSTRAINT [PK__BNP_SICA__3214EC27DA21ECEF] PRIMARY KEY CLUSTERED([ID] ASC)WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)ON [PRIMARY])ON [PRIMARY]
2 回答
SQL Server 2012 and Later
只需使用Try_Convert代替:
Read more about Try_Convert.
其中一行不是数字,你必须消除它们 . 找到它们:
请注意,这只能找到它们 . 它赢了't help you resolve the issues by itself. There' s无法将
abc123
转换为数字 .