Ms sql ошибка 213

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.

Источник

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…

Источник

Hi I’m trying to append the result of my query to another table, but i get the an error, here is my code for the project

--create new table
create table tempweblogs
(
  date1 datetime
  users nvarchar(50)
  utotal int
  date2 datetime
  hostname nvarchar(50)
  htotal int
  date3 datetime
  srcip nvarchar(50)
  stotal int
)
--insert query result to tempweblogs table

insert into tempweblogs 
SELECT distinct top 10 
       Xdate as date1, Xuser as users, count (Xuser) as utotal 
from   weblogs 
where Xdate='2/16/2016'  and Xuser is not null 
group by Xuser, Xdate order by utotal  DESC    

SELECT distinct top 10 
       Xdate as date2, Xhostname as hostname, count(Xhostname) as htotal
from weblogs    
where Xdate='2/16/2016'  and xhostname is not null 
group by Xhostname, Xdate order by htotal DESC

SELECT distinct top 10 
       Xdate as date3, Xsrcip as srcip, count (Xsrcip) as stotal 
from weblogs 
where Xdate='2/16/2016'  and Xuser is not null 
group by Xsrcip, Xdate order by stotal  DESC

Squirrel's user avatar

Squirrel

23.6k4 gold badges34 silver badges32 bronze badges

asked Jun 30, 2016 at 3:09

Melvuen's user avatar

1

You should need to specify the column list in the INSERT statement

insert into tempweblogs ( date1 , users , utotal )
select ....

also you need the INSERT clause for all 3 query

answered Jun 30, 2016 at 3:13

Squirrel's user avatar

SquirrelSquirrel

23.6k4 gold badges34 silver badges32 bronze badges

0

Источник

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

Источник

Понравилась статья? Поделить с друзьями:
  • Ms sql логирование ошибок
  • Ms sql код ошибки 3013
  • Ms sql код ошибки 1814
  • Ms sql server ошибка 1326
  • Ms sql вернуть ошибку