Ms sql ошибка 213

SQL Server 2019 on Linux SQL Server 2019 on Windows Еще…Меньше

Проблемы

Предполагается, что вы используете временный
таблицы в нескольких областях может возникнуть ошибка 213 или нарушение прав доступа в SQL Server 2019 при изменении временных
схема таблицы после начальной
создании.

Примеры использования временных
в нескольких областях ниже указаны следующие таблицы:

  • Временная таблица создается в одной рамке, но доступ к ней можно получить в другой рамке.

  • Временная таблица создается в пакете tsql, но доступ к ней можно получить из другого пакета TSQL.

Статус

Корпорация Майкрософт подтверждает наличие этой проблемы в своих продуктах, которые перечислены в разделе «Применяется к».

Решение

Эта проблема устранена в
следующее накопительное обновление для SQL Server:

  • Накопительное обновление 1 для SQL Server 2019

Около
накопительные обновления для SQL Server:

Каждый новый накопительный пакет
Обновление для SQL Server со всеми исправлениями и исправлениями для системы безопасности, которые
были включены в предыдущий накопительный пакет обновления. Ознакомьтесь с последней версией
накопительные обновления для SQL Server:

  • Последнее накопительное обновление для SQL Server 2019

Ссылки

Ознакомьтесь с терминологией , которую корпорация Майкрософт использует для описания обновлений программного обеспечения.

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.

Источник

Can you please run this statement in your database and give us the output??

SELECT 
    c.name,
    c.is_identity,
    c.is_computed, 
    t.name 'Type name'
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE object_id = object_ID('tblTable1')

Questions:

  • is there any column that has is_identity set to 1 (true) ?
  • is there any column that has is_computed set to 1 (true) ?
  • is there any column with a type of timestamp or rowversion ??

If you have any of those columns: you cannot easily set any of these in an INSERT statement. You can set the identity columns with some extra work, but computed columns or columns of type TIMESTAMP / ROWVERSION cannot be set under any circumstances.

That’s why I’d recommend to always explicitly specify the list of columns — even if you need all of them:

INSERT INTO dbo.tblTable1(col1, col2, ...., colX)
   SELECT col1, col2, ...., colX FROM dbo.tblTable1_Link

With this approach, you can leave out any columns that cannot be inserted quite easily…

Источник

Error message 213 is a common error that happens when you try to insert values into a table without explicitly specifying the column names.

The error looks like this:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

It occurs when you specify the wrong number of values for that table. In other words, the number of values you provide doesn’t match the number of columns in the table.

Example

Here’s an example to demonstrate.

INSERT INTO Customers
VALUES ('Jake');

Result:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

In my case, the problem is that the table actually contains three columns. My table definition looks like this:

CREATE TABLE Customers (
CustomerId int IDENTITY(1,1) NOT NULL,
FirstName nvarchar(255),
LastName nvarchar(255)
);

I’m trying to insert a value, but SQL Server doesn’t know which column it should go into, hence the error.

I would also get the same error if I tried to insert too many values. For example, the following also produces the same error.

INSERT INTO Customers
VALUES ('Jake', 'Smith', 'New York', 'USA');

Result:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

How to Fix the Error

One way to fix this, is to ensure that the number of values you try to insert actually matches the number of columns in the table.

A better way to do it is explicitly specify the column names in your INSERT statement. Doing this will ensure you don’t accidentally insert data into the wrong columns.

So depending on which values I want to insert, I could rewrite my example to this:

INSERT INTO Customers (FirstName)
VALUES ('Jake');

Or this:

INSERT INTO Customers (FirstName, LastName)
VALUES ('Jake', 'Smith');

Implicit Column Names

As mentioned, it’s better to explicitly spell out each column name in your INSERT statement (as I did in the previous example).

I could however, change my example to use implicit column names, like this:

INSERT INTO Customers
VALUES (1, 'Jake', 'Smith');

However, this could now cause a separate issue regarding the identity column. See How to Insert an Explicit Value into an Identity Column if you need to do this.

Источник

here is my code 

CREATE PROCEDURE [dbo].[pr_ETL_Create_StagingTable_Icd9_042] 

AS
BEGIN

SET NOCOUNT ON;

IF OBJECT_ID(‘Icd9_042’) IS NOT NULL
DROP TABLE Icd9_042 

CREATE TABLE [dbo].[Icd9_042]
(
[TableID] [int] NULL,[KEY] [bigint] NULL,[AGE] [int] NULL,[AGEDAY] [int] NULL,[AMONTH] [int] NULL,
[ASOURCE] [int] NULL,[ASOURCEUB92] [varchar](1) NULL,[ASOURCE_X] [varchar](8) NULL,[ATYPE] [int] NULL,
[AWEEKEND] [int] NULL,[DIED] [int] NULL,[DISCWT] [decimal](11, 7) NULL,[DISPUB92] [int] NULL,
[DISPUNIFORM] [int] NULL,[DQTR] [int] NULL,[DRG] [int] NULL,
[DRGVER] [int] NULL,[DSHOSPID] [varchar](13) NULL,[DX1] [varchar](5) NULL,[DX2] [varchar](5) NULL,
[DX3] [varchar](5) NULL,[DX4] [varchar](5) NULL,[DX5] [varchar](5) NULL,[DX6] [varchar](5) NULL,
[DX7] [varchar](5) NULL,[DX8] [varchar](5) NULL,[DX9] [varchar](5) NULL,[DX10] [varchar](5) NULL,
[DX11] [varchar](5) NULL,[DX12] [varchar](5) NULL,[DX13] [varchar](5) NULL,[DX14] [varchar](5) NULL,
[DX15] [varchar](5) NULL,[DXCCS1] [int] NULL,[DXCCS2] [int] NULL,[DXCCS3] [int] NULL,
[DXCCS4] [int] NULL,[DXCCS5] [int] NULL,[DXCCS6] [int] NULL,[DXCCS7] [int] NULL,
[DXCCS8] [int] NULL,[DXCCS9] [int] NULL,[DXCCS10] [int] NULL,[DXCCS11] [int] NULL,
[DXCCS12] [int] NULL,[DXCCS13] [int] NULL,[DXCCS14] [int] NULL,[DXCCS15] [int] NULL,
[ECODE1] [varchar](5) NULL,[ECODE2] [varchar](5) NULL,[ECODE3] [varchar](5) NULL,[ECODE4] [varchar](5) NULL,
[E_CCS1] [int] NULL,[E_CCS2] [int] NULL,[E_CCS3] [int] NULL,[E_CCS4] [int] NULL,
[ELECTIVE] [int] NULL,[FEMALE] [int] NULL,[HOSPID] [int] NULL,[HOSPST] [varchar](2) NULL,
[LOS] [int] NULL,[LOS_X] [int] NULL,[MDC] [int] NULL,
[MDNUM1_R] [int] NULL,[MDNUM2_R] [int] NULL,[NDX] [int] NULL,[NECODE] [int] NULL,
[NEOMAT] [int] NULL,[NIS_STRATUM] [int] NULL,[NPR] [int] NULL,[PAY1] [int] NULL,
[PAY1_X] [varchar](10) NULL,[PAY2] [int] NULL,[PAY2_X] [varchar](10) NULL,[PL_UR_CAT4] [int] NULL,
[PR1] [varchar](4) NULL,[PR2] [varchar](4) NULL,[PR3] [varchar](4) NULL,[PR4] [varchar](4) NULL,
[PR5] [varchar](4) NULL,[PR6] [varchar](4) NULL,[PR7] [varchar](4) NULL,[PR8] [varchar](4) NULL,
[PR9] [varchar](4) NULL,[PR10] [varchar](4) NULL,[PR11] [varchar](4) NULL,[PR12] [varchar](4) NULL,
[PR13] [varchar](4) NULL,[PR14] [varchar](4) NULL,[PR15] [varchar](4) NULL,[PRCCS1] [int] NULL,
[PRCCS2] [int] NULL,[PRCCS3] [int] NULL,[PRCCS4] [int] NULL,[PRCCS5] [int] NULL,
[PRCCS6] [int] NULL,[PRCCS7] [int] NULL,[PRCCS8] [int] NULL,[PRCCS9] [int] NULL,
[PRCCS10] [int] NULL,[PRCCS11] [int] NULL,[PRCCS12] [int] NULL,[PRCCS13] [int] NULL,
[PRCCS14] [int] NULL,[PRCCS15] [int] NULL,[PRDAY1] [int] NULL,[PRDAY2] [int] NULL,
[PRDAY3] [int] NULL,[PRDAY4] [int] NULL,[PRDAY5] [int] NULL,[PRDAY6] [int] NULL,
[PRDAY7] [int] NULL,[PRDAY8] [int] NULL,[PRDAY9] [int] NULL,[PRDAY10] [int] NULL,
[PRDAY11] [int] NULL,[PRDAY12] [int] NULL,[PRDAY13] [int] NULL,[PRDAY14] [int] NULL,
[PRDAY15] [int] NULL,[RACE] [int] NULL,[TOTCHG] [int] NULL,[TOTCHG_X] [decimal](15, 2) NULL,
[YEAR] [int] NULL,[ZIPInc_Qrtl] [int] NULL
)

INSERT INTO [dbo].[Icd9_042]
SELECT
[ID],[KEY],[AGE],[AGEDAY],[AMONTH],[ASOURCE],[ASOURCEUB92],[ASOURCE_X],[ATYPE],[AWEEKEND],[DIED],[DISCWT]
      ,[DISPUB92],[DISPUNIFORM],[DQTR],[DRG],[DRGVER],[DSHOSPID],[DX1],[DX2],[DX3],[DX4],[DX5],[DX6],[DX7]
      ,[DX8],[DX9],[DX10],[DX11],[DX12],[DX13],[DX14],[DX15],[DXCCS1],[DXCCS2],[DXCCS3],[DXCCS4],[DXCCS5],[DXCCS6]
      ,[DXCCS7],[DXCCS8],[DXCCS9],[DXCCS10],[DXCCS11],[DXCCS12],[DXCCS13],[DXCCS14],[DXCCS15],[ECODE1],[ECODE2]
      ,[ECODE3],[ECODE4],[E_CCS1],[E_CCS2],[E_CCS3],[E_CCS4],[ELECTIVE],[FEMALE],[HOSPID],[HOSPST],[LOS],[LOS_X]
      ,[MDC],[MDNUM1_R],[MDNUM2_R],[NDX],[NECODE],[NEOMAT],[NIS_STRATUM],[NPR],[PAY1],[PAY1_X],[PAY2]
      ,[PAY2_X],[PL_UR_CAT4],[PR1],[PR2],[PR3],[PR4],[PR5],[PR6],[PR7],[PR8],[PR9],[PR10],[PR11],[PR12],[PR13]
      ,[PR14],[PR15],[PRCCS1],[PRCCS2],[PRCCS3],[PRCCS4],[PRCCS5],[PRCCS6],[PRCCS7],[PRCCS8],[PRCCS9],[PRCCS10]
      ,[PRCCS11],[PRCCS12],[PRCCS13],[PRCCS14],[PRCCS15],[PRDAY1],[PRDAY2],[PRDAY3],[PRDAY4],[PRDAY5],[PRDAY6]
      ,[PRDAY7],[PRDAY8],[PRDAY9],[PRDAY10],[PRDAY11],[PRDAY12],[PRDAY13],[PRDAY14],[PRDAY15],[RACE],[TOTCHG]
      ,[TOTCHG_X],[YEAR],[ZIPInc_Qrtl]
FROM dbo.[2004_Nis]
WHERE ‘042’ IN (DX1,DX2,DX3,DX4,DX5,DX6,DX7,DX8,DX9,DX10,DX11,DX12,DX13,DX14,DX15)

INSERT INTO [dbo].[Icd9_042] 
SELECT
[ID],[KEY],[AGE],[AGEDAY],[AMONTH],[ASOURCE],[ASOURCEUB92],[ASOURCE_X],[ATYPE],[AWEEKEND],[DIED],[DISCWT]
      ,[DISPUB92],[DISPUNIFORM],[DQTR],[DRG],[DRGVER],[DSHOSPID],[DX1],[DX2],[DX3],[DX4],[DX5],[DX6],[DX7]
      ,[DX8],[DX9],[DX10],[DX11],[DX12],[DX13],[DX14],[DX15],[DXCCS1],[DXCCS2],[DXCCS3],[DXCCS4],[DXCCS5],[DXCCS6]
      ,[DXCCS7],[DXCCS8],[DXCCS9],[DXCCS10],[DXCCS11],[DXCCS12],[DXCCS13],[DXCCS14],[DXCCS15],[ECODE1],[ECODE2]
      ,[ECODE3],[ECODE4],[E_CCS1],[E_CCS2],[E_CCS3],[E_CCS4],[ELECTIVE],[FEMALE],[HOSPID],[HOSPST],[LOS],[LOS_X]
      ,[MDC],[MDNUM1_R],[MDNUM2_R],[NDX],[NECODE],[NEOMAT],[NIS_STRATUM],[NPR],[PAY1],[PAY1_X],[PAY2]
      ,[PAY2_X],[PL_UR_CAT4],[PR1],[PR2],[PR3],[PR4],[PR5],[PR6],[PR7],[PR8],[PR9],[PR10],[PR11],[PR12],[PR13]
      ,[PR14],[PR15],[PRCCS1],[PRCCS2],[PRCCS3],[PRCCS4],[PRCCS5],[PRCCS6],[PRCCS7],[PRCCS8],[PRCCS9],[PRCCS10]
      ,[PRCCS11],[PRCCS12],[PRCCS13],[PRCCS14],[PRCCS15],[PRDAY1],[PRDAY2],[PRDAY3],[PRDAY4],[PRDAY5],[PRDAY6]
      ,[PRDAY7],[PRDAY8],[PRDAY9],[PRDAY10],[PRDAY11],[PRDAY12],[PRDAY13],[PRDAY14],[PRDAY15],[RACE],[TOTCHG]
      ,[TOTCHG_X],[YEAR],[ZIPInc_Qrtl]
FROM dbo.[2005_Nis]
WHERE ‘042’ IN (DX1,DX2,DX3,DX4,DX5,DX6,DX7,DX8,DX9,DX10,DX11,DX12,DX13,DX14,DX15)

INSERT INTO [dbo].[Icd9_042]
SELECT
[ID],[KEY],[AGE],[AGEDAY],[AMONTH],[ASOURCE],[ASOURCEUB92],[ASOURCE_X],[ATYPE],[AWEEKEND],[DIED],[DISCWT]
      ,[DISPUB92],[DISPUNIFORM],[DQTR],[DRG],[DRGVER],[DSHOSPID],[DX1],[DX2],[DX3],[DX4],[DX5],[DX6],[DX7]
      ,[DX8],[DX9],[DX10],[DX11],[DX12],[DX13],[DX14],[DX15],[DXCCS1],[DXCCS2],[DXCCS3],[DXCCS4],[DXCCS5],[DXCCS6]
      ,[DXCCS7],[DXCCS8],[DXCCS9],[DXCCS10],[DXCCS11],[DXCCS12],[DXCCS13],[DXCCS14],[DXCCS15],[ECODE1],[ECODE2]
      ,[ECODE3],[ECODE4],[E_CCS1],[E_CCS2],[E_CCS3],[E_CCS4],[ELECTIVE],[FEMALE],[HOSPID],[HOSPST],[LOS],[LOS_X]
      ,[MDC],[MDNUM1_R],[MDNUM2_R],[NDX],[NECODE],[NEOMAT],[NIS_STRATUM],[NPR],[PAY1],[PAY1_X],[PAY2]
      ,[PAY2_X],[PL_UR_CAT4],[PR1],[PR2],[PR3],[PR4],[PR5],[PR6],[PR7],[PR8],[PR9],[PR10],[PR11],[PR12],[PR13]
      ,[PR14],[PR15],[PRCCS1],[PRCCS2],[PRCCS3],[PRCCS4],[PRCCS5],[PRCCS6],[PRCCS7],[PRCCS8],[PRCCS9],[PRCCS10]
      ,[PRCCS11],[PRCCS12],[PRCCS13],[PRCCS14],[PRCCS15],[PRDAY1],[PRDAY2],[PRDAY3],[PRDAY4],[PRDAY5],[PRDAY6]
      ,[PRDAY7],[PRDAY8],[PRDAY9],[PRDAY10],[PRDAY11],[PRDAY12],[PRDAY13],[PRDAY14],[PRDAY15],[RACE],[TOTCHG]
      ,[TOTCHG_X],[YEAR],[ZIPInc_Qrtl]
FROM dbo.[2006_Icd9]
WHERE ‘042’ IN (DX1,DX2,DX3,DX4,DX5,DX6,DX7,DX8,DX9,DX10,DX11,DX12,DX13,DX14,DX15)

— Alter Table Statements to add Dimension table keys
ALTER TABLE Icd9_042
ADD AdmissionMonthKey int

ALTER TABLE Icd9_042
ADD PrimaryPaySourceKey int

ALTER TABLE Icd9_042
ADD SecondaryPaySourceKey int

ALTER TABLE Icd9_042
ADD AgeYearDayKey int

ALTER TABLE Icd9_042
ADD YearKey int

ALTER TABLE Icd9_042
ADD HospitalStateKey int

ALTER TABLE Icd9_042
ADD AdmissionSourceKey int

ALTER TABLE Icd9_042
ADD RaceCodeKey int

ALTER TABLE Icd9_042
ADD GenderCodeKey int

— Add Dimension table keys to Staging Table
—UPDATE Icd9_042
—SET AdmissionMonthKey = a.AdmissionMonthKey
—FROM DimAdmissionMonths a
—WHERE a.AdmissionMonthID = AMONTH

—UPDATE Icd9_042
—SET HospitalStateKey = a.HospitalStateKey
—FROM DimHospitalStates a
—WHERE a.HospStateAbrev = Icd9_042.HOSPST

—UPDATE Icd9_042
—SET AgeYearDayKey = a.AgeYearDayKey
—FROM DimAgeYearDays a
—WHERE 
— (a.AgeYear = Icd9_042.AGE AND Icd9_042.AGE > 0)
— OR (Icd9_042.AGEDAY > 0 AND Icd9_042.AGEDAY = a.AGEDAY)

—UPDATE Icd9_042
—SET AgeYearDayKey = 1
—WHERE 
— AGE <= 0 AND AGEDAY = -99

—UPDATE Icd9_042
—SET PrimaryPaySourceKey = a.PaySourceKey
—FROM DimPaySources a
—WHERE PAY1 = a.PaySourceID

—UPDATE Icd9_042
—SET SecondaryPaySourceKey = a.PaySourceKey
—FROM DimPaySources a
—WHERE PAY2 = a.PaySourceID

—UPDATE Icd9_042
—SET AdmissionSourceKey = a.AdmissionSourceKey
—FROM DimAdmissionSources a
—WHERE ASOURCE = a.ASourceID

—UPDATE Icd9_042
—SET RaceCodeKey = a.RaceCodeKey
—FROM DimRaceCodes a
—WHERE RACE = a.RaceID

—UPDATE Icd9_042
—SET GenderCodeKey = a.GenderCodeKey
—FROM DimGenderCodes a
—WHERE FEMALE = a.GenderCodeID

—UPDATE Icd9_042
—SET YearKey = a.YearKey
—FROM DimYears a
—WHERE [YEAR] = a.YearNumber

END
GO

Источник

If you copy the code and paste on SSMS that you can take the row numbers and count like that will give you why you have the error.
Therefore,I think you should use on SSMS.Right click on Table of you want to Insert,script table as->Insert Into-> New Query Editor Window

Also,You must convert your datatype of code to sql datatype in your programming language to exchange data with sql,if you use staticly typed language.

InsertColumnCount ValueCount
93 91

  insert into [dbo].[Table] ( 
    [id],
    [Client_No],
    [Loan_No],
    [Client_Name],
    [Loan_Amt],
    [Loan_Balance],
    [Loan_Int_Paid],
    [Loan_lsinst],
    [PartAmt],
    [InstNoPaid],
    [CentreId],
    [GroupId],
    [Loan_Dt],
    [finChrg],
    [Loan_Stat],
    [FirstInstDt],
    [Loan_Int],
    [Loan_Prd],
    [Loan_DueDt],
    [Loan_Inst],
    [Loan_Typ],
    [IRR],
    [FlatRate],
    [ROCode],
    [ROCodeOrg],
    [Loan_pen],
    [Loan_penpaid],
    [loan_Oc],
    [Loan_Ocpaid],
    [Loan_legal],
    [WOff],
    [WOffDt],
    [Reschedule],
    [Loan_Suggest],
    [Loan_Leg_Rem],
    [loan_woff_rem],
    [loan_sector],
    [loan_use],
    [Loan_ClDt],
    [Brn_Code],
    [Remarks],
    [I nsureAmt],
    [ProcessFee],
    [sTax],
    [trn_autNo],
    [LastPaidOn],
    [Loan_Cat],
    [Cycle],
    [invoiceNo],
    [BuyOutDt],
    [BuyOutBnk],
    [BuyOutFlg],
    [ResponseDt],
    [EndLoanUtil],
    [Util_Empno],
    [InsurComp],
    [CoolDys],
    [Collday],
    [CollWeek],
    [NetDisbFlg],
    [ML_ClAmt],
    [LastPaidAmt],
    [WoffBO],
    [WoffBODt],
    [LastEMI],
    [DisbBank],
    [NextDueDt],
    [ArrearNos],
    [DisbTyp],
    [GL_Fdgl],
    [GAN],
    [Bank_Id],
    [Nominee_Id],
    [IsAndroid],
    [Created_On],
    [Created_By],
    [Updated_On],
    [Updated_By],
    [Doc_Download],
    [Lot_Time],
    [CollMode],
    [Ref_LoanNo],
    [Sanction_dt],
    [GenInsu],
    [NatCatInsr],
    [MasterLoanUtil],
    [NachStatus],
    [NatCatInsrFlg],
    [SoftCopyStatus],
    [PennyDrop],
    [EximId],
    [Mort_Int],
    [InterestPaidUpto],
    [MortInsAmt]) 
    values ( 
    6494935,
    1002460000002934,
    1102460201145852,
    N'AKASH DINI',
    2.800000000000000e+004,
    2.800000000000000e+004,
    0.000000000000000e+000,
    NULL,
    0,
    0,
    306076,
    331912,
    {ts '2021-02-20 13:08:07.720'},
    0.000000000000000e+000,145,
    {ts '2021-02-20 13:08:07.720'},
    2.500000000000000e+001,104,
    {ts '2021-02-20 13:08:07.720'},
    0.000000000000000e+000
    ,2
    ,0.000000000000000e+000,
    NULL,
    'MM730',
    NULL,
    0.000000000000000e+000,
    0.000000000000000e+000,
    0.000000000000000e+000,
    0.000000000000000e+000,
    NULL,
    NULL,
    NULL,
    'N',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    246,
    NULL,
    0,
    0.000000000000000e+000,
    0.000000000000000e+000,
    NULL,
    NULL,
    48,
    1,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    0,
    'TUE',
    0,
    NULL,
    0.000000000000000e+000,
    0,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    0,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    1,
    {ts '2021-02-20 13:08:07.720'},
    'MM166',
    NULL,
    NULL,
    0,
    NULL,
    'W',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    N'N',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL)
    
    select (95-2) as InsertColumnCount,(188-97) as ValueCount

Источник

Понравилась статья? Поделить с друзьями:
  • Ms sql вызов ошибки
  • Ms sql server проверка базы на ошибки
  • Ms sql server ошибка 4064
  • Ms settings ошибка файловой системы 2147219196
  • Ms settings ошибка файловой системы 2144927439