导出数据脚本

SET NOCOUNT ON
DECLARE @tblName nvarchar(100)
DECLARE @DBName nvarchar(100)
DECLARE @i int
DECLARE @j int
DECLARE @ssql nvarchar(max)
DECLARE @sCloumns nvarchar(max)
DECLARE @CloumnName nvarchar(100)
DECLARE @xtype int
DECLARE @isnullable int
SET @tblName = 'tbl_StationInfo'
SET @DBName = 'Station'
CREATE TABLE #tbl_columnIDs(name nvarchar(100),id nvarchar(100),xtype int,isnullable int,row int identity)
INSERT INTO #tbl_columnIDs
SELECT name,id,xtype,isnullable
FROM SYSCOLUMNS WHERE id = OBJECT_ID(@tblName)
SET @i = 1
SELECT @j = max(row) FROM #tbl_columnIDs
--SET @sCloumns = ''
--SELECT @sCloumns = @sCloumns + ',' + name FROM #tbl_columnIDs
--SET @sCloumns = SUBSTRING(@sCloumns,2,LEN(@sCloumns))
--SET @sCloumns = '(' + @sCloumns +')'
--PRINT @sCloumns
SET @ssql = 'SELECT ''INSERT INTO ' + @tblName + '('''+CHAR(10)
WHILE (@i < @j + 1)
BEGIN
SELECT @CloumnName = name FROM #tbl_columnIDs WHERE ROW = @i
IF @i = @j
BEGIN
SET @ssql = @ssql + '+''' + @CloumnName + ')'''+ CHAR(10)
END
ELSE
BEGIN
SET @ssql = @ssql + '+''' + @CloumnName + ',''' + CHAR(10)
END
SET @i = @i + 1
END

SET @ssql = @ssql + '+'' VALUES (''' + CHAR(10)

SET @i = 1
SELECT @j = max(row) FROM #tbl_columnIDs
WHILE (@i < @j + 1)
BEGIN
SELECT @CloumnName = name,@xtype = xtype,@isnullable = isnullable FROM #tbl_columnIDs WHERE ROW = @i
IF @isnullable = 0
BEGIN
SET @CloumnName = CASE @xtype WHEN 61 THEN '+'',''+''''''''+CONVERT(nvarchar,'+@CloumnName+',121)+''''''''' --DateTime 61
WHEN 58 THEN '+'',''+''''''''+CONVERT(nvarchar,'+@CloumnName+',121)+''''''''' --smalldatetime 58
WHEN 48 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+''''''''' --tinyint
WHEN 52 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+'''''''''
WHEN 56 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+'''''''''
WHEN 59 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+'''''''''
WHEN 60 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+'''''''''
WHEN 62 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+'''''''''
WHEN 104 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+'''''''''
WHEN 108 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+'''''''''
WHEN 122 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+'''''''''
WHEN 127 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+'''''''''
WHEN 189 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+'''''''''
WHEN 231 THEN '+'',''+''N''+''''''''+REPLACE('+@CloumnName+','''''''','''''''''''')+'''''''''
WHEN 239 THEN '+'',''+''N''+''''''''+REPLACE('+@CloumnName+','''''''','''''''''''')+'''''''''
ELSE '+'',''+''''''''+REPLACE('+@CloumnName+','''''''','''''''''''')+'''''''''
END
END
ELSE
BEGIN
SET @CloumnName = CASE @xtype WHEN 61 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar,'+@CloumnName+',121)+'''''''',''NULL'')' --DateTime 61
WHEN 58 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar,'+@CloumnName+',121)+'''''''',''NULL'')' --smalldatetime 58
WHEN 48 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')' --tinyint
WHEN 52 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
WHEN 56 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
WHEN 59 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
WHEN 60 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
WHEN 62 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
WHEN 104 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
WHEN 108 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
WHEN 122 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
WHEN 127 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
WHEN 189 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+'''''''''
WHEN 231 THEN '+'',''+ISNULL(''N''+''''''''+REPLACE('+@CloumnName+','''''''','''''''''''')+'''''''',''NULL'')'
WHEN 239 THEN '+'',''+ISNULL(''N''+''''''''+REPLACE('+@CloumnName+','''''''','''''''''''')+'''''''',''NULL'')'
ELSE '+'',''+ISNULL(''''''''+REPLACE('+@CloumnName+','''''''','''''''''''')+'''''''',''NULL'')'
END
END
IF @i = 1
BEGIN
SET @CloumnName = SUBSTRING(@CloumnName,5,LEN(@CloumnName))
END
SET @ssql = @ssql + @CloumnName + CHAR(10)
SET @i = @i + 1
END
SET @ssql = @ssql + '+'')''' + CHAR(10)
SET @ssql = @ssql + 'FROM '+@DBName + '..' + @tblName

print @ssql
PRINT CHAR(10)+'SELECT ''GO'''
DROP TABLE #tbl_columnIDs

--select * from systypes
--
--
--
--
--PRINT @ssql
--
--
--DROP TABLE #tbl_columnIDs
--
--
--SELECT * FROM #tbl_columnIDs
--
--SELECT * FROM SYSCOLUMNS

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7192349/viewspace-1009325/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7192349/viewspace-1009325/