22,209
社区成员
发帖
与我相关
我的任务
分享
--查看下你SQL Server的版本
SELECT @@VERSION
--如果是SQL Server2012 或更高版本可以用系统自带的 TRY_CONVERT 来实现
USE tempdb
GO
IF OBJECT_ID('[target]') IS NOT NULL DROP TABLE [target]
IF OBJECT_ID('[source]') IS NOT NULL DROP TABLE [source]
CREATE TABLE [target](t_id INT IDENTITY(1,1), t_c1 XML)
CREATE TABLE [source](s_id INT IDENTITY(1,1), s_c1 NVARCHAR(20))
SET NOCOUNT ON
INSERT INTO [source](s_c1) VALUES('<x>1</x>')
INSERT INTO [source](s_c1) VALUES('<x>2</x>')
INSERT INTO [source](s_c1) VALUES('<x>3</x>')
INSERT INTO [source](s_c1) VALUES('<x>4</x>')
INSERT INTO [source](s_c1) VALUES('<x>5</x>')
INSERT INTO [source](s_c1) VALUES('<x>6')
INSERT INTO [source](s_c1) VALUES('<x>7</x>')
INSERT INTO [source](s_c1) VALUES('<x>8</x>')
INSERT INTO [source](s_c1) VALUES('<x>9</x>')
INSERT INTO [source](s_c1) VALUES('<x>10</x>')
INSERT INTO [target](t_c1)
SELECT [s_c1] FROM [source] WHERE TRY_CONVERT(XML, s_c1 ) IS NOT NULL
--很明显 第 6 条有问题的数据没有插入进来
SELECT * FROM [target]
/*
t_id t_c1
1 <x>1</x>
2 <x>2</x>
3 <x>3</x>
4 <x>4</x>
5 <x>5</x>
6 <x>7</x>
7 <x>8</x>
8 <x>9</x>
9 <x>10</x>
*/
USE tempdb
GO
IF OBJECT_ID('[target]') IS NOT NULL DROP TABLE [target]
IF OBJECT_ID('[source]') IS NOT NULL DROP TABLE [source]
CREATE TABLE [target](t_id INT IDENTITY(1,1), t_c1 INT)
CREATE TABLE [source](s_id INT IDENTITY(1,1), s_c1 NVARCHAR(20))
SET NOCOUNT ON
INSERT INTO [source](s_c1) VALUES('1')
INSERT INTO [source](s_c1) VALUES('2')
INSERT INTO [source](s_c1) VALUES('3')
INSERT INTO [source](s_c1) VALUES('4')
INSERT INTO [source](s_c1) VALUES('5')
INSERT INTO [source](s_c1) VALUES('d')
INSERT INTO [source](s_c1) VALUES('7')
INSERT INTO [source](s_c1) VALUES('8')
INSERT INTO [source](s_c1) VALUES('9')
INSERT INTO [source](s_c1) VALUES('10')
INSERT INTO [target](t_c1)
SELECT [s_c1] FROM [source] WHERE ISNUMERIC(s_c1)=1
--很显示 第 6 条有问题的数据没有插入进来
SELECT * FROM [target]
/*
t_id t_c1
1 1
2 2
3 3
4 4
5 5
6 7
7 8
8 9
9 10
*/