TVP

  • 18
  • 0

若有大批資料寫入,又想用組字串的方式,可改用TVP的寫法

參考: https://mutsung.wordpress.com/2019/06/21/tvp-in-sql-server/

 

一般寫法: 耗時6分鐘

select cast('1' as varchar(10)) sn into #T
union select '2'
union select '3'
union select '4'
union select '5'
--中略...
union select '9995'
union select '9996'
union select '9997'
union select '9998'
union select '9999'
union select '10000'

改成TVP,只需6秒

--DROP type [dbo].[TVP_Int]
CREATE TYPE [dbo].[TVP_Int] AS TABLE(
    [Val] [varchar](10) NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
        [Val] ASC
    )WITH (IGNORE_DUP_KEY = OFF)
)

DECLARE @Tbl AS [TVP_Int];
INSERT INTO @Tbl (Val) VALUES ('1')
INSERT INTO @Tbl (Val) VALUES ('2')
INSERT INTO @Tbl (Val) VALUES ('3')
INSERT INTO @Tbl (Val) VALUES ('4')
INSERT INTO @Tbl (Val) VALUES ('5')
INSERT INTO @Tbl (Val) VALUES ('6')
--中略...
INSERT INTO @Tbl (Val) VALUES ('9995')
INSERT INTO @Tbl (Val) VALUES ('9996')
INSERT INTO @Tbl (Val) VALUES ('9997')
INSERT INTO @Tbl (Val) VALUES ('9998')
INSERT INTO @Tbl (Val) VALUES ('9999')
INSERT INTO @Tbl (Val) VALUES ('10000')

select * from @Tbl