SQL：插入数据的小坑

在SQL Server中，不管是存储过程还是自定义函数中，经常需要进行插入数据的操作。在插入数据的时候，很多人选择如下方式

INSERT INTO DestinationTable
SELECT Column1,Column2,<column3 ,>
FROM SourceTable

这样插入通常不会出现什么问题，但是在DestinationTable的表字段发生变化时，就会出现问题，如图所示的例子

USE [TrustManagement]
GO

/****** Object:  Table [Asset].[TrustAssetPaymentPlan_New]    Script Date: 8/3/2018 9:33:50 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Asset].[Table_1_New](
	[TrustId] [int] NOT NULL,
	[PoolId] [int] NOT NULL,
	[DimLoanId] [int] NOT NULL,
	[AccountNo] [nvarchar](100) NOT NULL,
	[PaymentTypeId] [int] NULL,
	[PrincipalPaymentType] [nvarchar](100) NULL,
	[InterestPaymentType] [nvarchar](100) NULL,
	[PrincipalPayFrequency] [int] NULL,
	[InterestPayFrequency] [int] NULL,
	[InterestOnlyPayFrequency] [int] NULL,
	[PrincipalTerm] [int] NULL,
	[InterestTerm] [int] NULL,
	[InterestOnlyTerm] [int] NULL,
	[InterestStartDate] [date] NULL,
	[PayCycleStartDate] [date] NULL,
	[LastPayDate] [date] NULL,
	[Term] [int] NULL,
	[Amount] [decimal](19, 6) NULL,
	[InterestRate] [decimal](15, 6) NULL,
	[PayDay] [int] NULL,
	[InterestBasis] [nvarchar](50) NULL,
	[IsInTrust] [int] NULL,
	[PaymentConfigurationTypeId] [int] NULL,
	[CloseDate] [date] NULL,
	[PaymentSchedule] [nvarchar](max) NULL,
	[FeeOutstanding] [decimal](19, 6) NULL,
	[PrincipalPayment] [decimal](38, 2) NULL,
	[LoanStartDate] [date] NULL,
	[InterestFirstPaymentDate] [date] NULL,
	[SecondLastPaymentDate] [date] NULL,
	[LastPaymentDate] [date] NULL,
	[InterestPayment] [decimal](38, 2) NULL,
 CONSTRAINT [PK_Table_1_New] PRIMARY KEY CLUSTERED 
(
	[TrustId] ASC,
	[PoolId] ASC,
	[DimLoanId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
-------------------------------------------------------------------------------
    INSERT  INTO Asset.Table_1_New
    SELECT 
        TrustId ,
        PoolId ,
        IIF(@TrustId>0, DimLoanid, Id) AS DimLoanid,--To prevent duplicate primary keys when performance test
        AccountNo  ,
        PaymentTypeId  ,
        PrincipalPaymentType  ,
        InterestPaymentType ,
        PrincipalPayFrequency  ,
        InterestPayFrequency ,
        InterestOnlyPayFrequency  ,
        PrincipalTerm ,
        InterestTerm  ,
        InterestOnlyTerm ,
        InterestStartDate  ,
        null as PayCycleStartDate ,
        null as LastPayDate  ,
        Term  ,
        Amount  ,
        InterestRate ,
        iif(PayDay is NULL,datepart(d,CloseDate),PayDay) as PayDay,
        InterestBasis  ,
        IsInTrust  ,
        PaymentConfigurationTypeId,
        CloseDate,
        PaymentSchedule ,
        FeeOutstanding ,
        PrincipalPayment ,
		InterestPayment,
		LoanStartDate,
		InterestFirstPaymentDate,
		SecondLastPaymentDate,
		LastPaymentDate
    FROM #PaymentPlan
    

执行后面的插入语句的时候会发现出现如下错误信息

System.Data.SqlClient.SqlException (0x80131904): Operand type clash: decimal is incompatible with date at Securitisation.Utilities.SQL.UtilSqlRunQueryADONET.RunNonQueryStoredProcedure(String sSPName, List`1 glParameters, String sConnectionString) at BL.TaskProcessService.OperationProvider.RunSP(ITaskAction action) ClientConnectionId:1c312f06-dc7e-453b-b42f-859e5d2541d2 Error Number:206,State:2,Class:16

原因是查询的结果集字段顺序与目标表的字段的顺序不是一一对应的，出现将decimal类型的数据插入到date类型的字段中。解决这个问题，最好的办法就是在插入的时候，列清楚字段，保证查询结果集中字段与要插入数据的字段一一对应。