[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