[SQL Server] 常见经验总结

SQL Server一些常用技巧汇总

1. 通过SQL Server Management Studio查看存储过程实现

SELECT sm.DEFINITION
FROM sys.objects so
INNER JOIN sys.all_sql_modules sm
   ON so.object_id = sm.object_id
WHERE type = 'P'
   AND so.NAME = ''--stored procedure name--

2. 导入csv文件到数据库

• 当文件有主键时，最好通过一个staging table来做数据中转
• bulk insert导入文件时，可能需要用用0x0a来作为换行，而不是使用\n
• 当mysql导出的文件utf8编码时，SQL Server不支持utf8，需要转换成utf16，可以用notepad++的“convert to UCS-2 little Endian”，同时换行需要用0x0a00

--- Create table
create table z_table 
(
   id int identity(1, 1) primary key,
   a varchar(64),
   b varchar(128),
   c varchar(128),
   dev_1 int,
   dev_2 int
)

create table z_table_staging 
(
   a varchar(128),
   b varchar(128),
   c varchar(128),
   dev_1 int,
   dev_2 int
)

bulk insert z_table_staging
from 'D:\all_info.csv'
with (FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a')

--For utf16
bulk insert z_table
from ''' + @file + '''
with (
KEEPIDENTITY,
DATAFILETYPE = ''widechar'',
FIELDTERMINATOR = ''#'',
ROWTERMINATOR = ''0x0a00'',
ERRORFILE = ''D:\debug.log'' 
)
	
	
INSERT INTO z_table(a, b, c, dev_1, dev_2) 
   SELECT a, b, c, dev_1, dev_2 FROM z_table_staging

drop table z_table_staging

3. 用存储过程导入csv文件到数据库

• 主要是一些变量要注意的地方

USE [DB1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Jerry

-- Description:	Load db file
-- =============================================
ALTER PROCEDURE [dbo].[importdb]
(
@file varchar(64)
)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- Create table
	DECLARE @t1String NVARCHAR(MAX)
	DECLARE @t1name VARCHAR(64)
	SET @t1name = 't_result_' + CONVERT(VARCHAR(10), GETDATE(), 112)

	SET @t1String = 'CREATE TABLE '+ @t1name + '( 
		id int identity(1, 1) primary key,
		...
	) '

	EXEC (@t1String)

	DECLARE @t2String NVARCHAR(MAX)
	SET @t2String = 'CREATE TABLE t_result_staging ( 
		...
	) '

	EXEC (@t2String)

	DECLARE @t3String NVARCHAR(MAX)
	SET @t3String = '
		bulk insert t_result_staging
		from ''' + @file + '''
		with (FIELDTERMINATOR = '','', ROWTERMINATOR = ''0x0a'')
	'
	EXEC (@t3String)

	DECLARE @t4String NVARCHAR(MAX)
	SET @t4String = 'INSERT INTO ' + @t1name + '(...) 
   SELECT ...
   FROM t_result_staging'

   EXEC (@t4String)

   DECLARE @t5String NVARCHAR(MAX)
	SET @t5String = '
		 drop table t_result_staging
		 '
  EXEC (@t5String)

END