Inserting data is often required in SQL Server, whether stored procedures or custom functions.When inserting data, many people choose the following

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

This insertion usually doesn't cause any problems, but when the table fields of the DestinationTable change, it does, as shown in the example shown

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
    

The following error message appears when executing the subsequent insert statement

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

The reason is that the order of the result set fields in the query does not correspond to the order of the fields in the target table, so data of type decimal is inserted into a field of type date.The best way to solve this problem is to list the fields clearly when inserting, so that the fields in the query result set correspond to the fields to be inserted into the data one-to-one.