• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 140
  • Last Modified:

Problem to insert

Hi,
I don't know why I get this problem below

use CBRHKMA
go

INSERT INTO T41 values ('1','HKD','HK','0','0747958050001','0','0','41330','0','0','XZ','XZ','0','0','0','0','0','0','0','0',166637553.01,166637553.01,0,0,0,0,0,0,0,0,0,0,0,0,'','','N.R.','0','','0','','1','N.R.','US106     KK','2','0','XZ','N.R.','','XZ','','','','0','N.R.','N.R.','N.R.','C','8990','0','0','0','N.A','N.A','0','1','0','0','0','0','0','0','0','0','','N.A','0','0','1','20141213')

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

Open in new window

while here is the relevant table involved.

USE [CBRHKMA]
GO

/****** Object:  Table [dbo].[T41]    Script Date: 01/02/2015 18:27:18 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[T41]') AND type in (N'U'))
DROP TABLE [dbo].[T41]
GO

USE [CBRHKMA]
GO

/****** Object:  Table [dbo].[T41]    Script Date: 01/02/2015 18:27:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[T41](
	[Id] [nvarchar](255) NULL,
	[Currency Code] [nvarchar](255) NULL,
	[Cty of Domicile] [nvarchar](255) NULL,
	[Connected Party Code] [nvarchar](255) NULL,
	[Internal Reference] [nvarchar](255) NULL,
	[Sector Classification] [nvarchar](255) NULL,
	[Re-discountable] [nvarchar](255) NULL,
	[Product Type] [nvarchar](255) NULL,
	[Interest Rate Type] [nvarchar](255) NULL,
	[Type of Coverage] [nvarchar](255) NULL,
	[Country of Risk Counter Party] [nvarchar](255) NULL,
	[Market Place] [nvarchar](255) NULL,
	[Start Date] [nvarchar](255) NULL,
	[Maturity Date] [nvarchar](255) NULL,
	[Deal Date] [nvarchar](255) NULL,
	[Next Interest Reprising Date] [nvarchar](255) NULL,
	[Early Redemption Date] [nvarchar](255) NULL,
	[Next Interest Settlement Date] [nvarchar](255) NULL,
	[Next Roll-over Date] [nvarchar](255) NULL,
	[Code Listed Or Unlisted] [nvarchar](255) NULL,
	[Book Or Undrawn Amount in Original Currency] [float] NULL,
	[Book Or Undrawn Amount in HKD Eqv] [float] NULL,
	[Nominal Value Or Limit in Original Currency] [float] NULL,
	[Nominal Value Or Limit in HKD eqv] [float] NULL,
	[Original Cost in Original Currency] [float] NULL,
	[Original Cost in HKD Eqv] [float] NULL,
	[Accrued Interest in Original Currency] [float] NULL,
	[Accrued Interest in HKD Eqv] [float] NULL,
	[Generic bad debt Provision in Reporting Currency] [float] NULL,
	[Specific bad debt Provision in Reporting Currency] [float] NULL,
	[Specific Country Risk Provision in Reporting Currency] [float] NULL,
	[Accrued interest Provision in Reporting Currency] [float] NULL,
	[Market Value in Original Currency] [float] NULL,
	[Market Value in Reporting Currency] [float] NULL,
	[Counter Party ID] [nvarchar](255) NULL,
	[Counter Party Group Name] [nvarchar](255) NULL,
	[Long Term Credit Rating Counter Party] [nvarchar](255) NULL,
	[Counter Party quality] [nvarchar](255) NULL,
	[Counter Party Group ID] [nvarchar](255) NULL,
	[Chinese influence] [nvarchar](255) NULL,
	[Counter Party Name] [nvarchar](255) NULL,
	[Residence Indicator] [nvarchar](255) NULL,
	[Short Term Credit Rating Counter Party] [nvarchar](255) NULL,
	[Origin Or Application ID] [nvarchar](255) NULL,
	[Sequential Counter] [nvarchar](255) NULL,
	[Type of Guarantor] [nvarchar](255) NULL,
	[Country of domicile Guarantor] [nvarchar](255) NULL,
	[Long Term Credit Rating Guarantor] [nvarchar](255) NULL,
	[Guarantor ID] [nvarchar](255) NULL,
	[Country of Risk Guarantor] [nvarchar](255) NULL,
	[Guarantor Group ID] [nvarchar](255) NULL,
	[Guarantor Name] [nvarchar](255) NULL,
	[Guarantor Group Name] [nvarchar](255) NULL,
	[Connected Guarantor Code] [nvarchar](255) NULL,
	[Short Term Credit Rating Guarantor] [nvarchar](255) NULL,
	[Short Term Credit Rating Product] [nvarchar](255) NULL,
	[Long Term Credit Rating Product] [nvarchar](255) NULL,
	[Debit Or Credit] [nvarchar](255) NULL,
	[Industrial Sector Classification] [nvarchar](255) NULL,
	[Market (primary Or Secondary)] [nvarchar](255) NULL,
	[Interest Rate in basis Points] [nvarchar](255) NULL,
	[Usage Disposal Or Goal Acquisition] [nvarchar](255) NULL,
	[G Or L Code (bank Specific)] [nvarchar](255) NULL,
	[Coverage ID] [nvarchar](255) NULL,
	[Liquifyable] [nvarchar](255) NULL,
	[Eligible] [nvarchar](255) NULL,
	[Marketable] [nvarchar](255) NULL,
	[Received as Part of Settlement] [nvarchar](255) NULL,
	[Rescheduled] [nvarchar](255) NULL,
	[Suspended Interest] [nvarchar](255) NULL,
	[Maturity Indicator] [nvarchar](255) NULL,
	[Position Type] [nvarchar](255) NULL,
	[Denominated & Funded] [nvarchar](255) NULL,
	[Portfolio Code] [nvarchar](255) NULL,
	[Transaction ID] [nvarchar](255) NULL,
	[Facility ID] [nvarchar](255) NULL,
	[Entity Code Counter Party] [nvarchar](255) NULL,
	[Entity Code Guarantor] [nvarchar](255) NULL,
	[Entity Code Origin of information] [nvarchar](255) NULL,
	[Leg of Transaction] [nvarchar](255) NULL,
	[Reporting Date] [nvarchar](50) NULL
) ON [PRIMARY]

GO

Open in new window

0
HuaMinChen
Asked:
HuaMinChen
1 Solution
 
Pratima PharandeCommented:
INSERT INTO T41 values ('1','HKD','HK','0','0747958050001','0','0','41330','0','0','XZ','XZ','0','0','0','0','0','0','0','0',166637553.01,166637553.01,0,0,0,0,0,0,0,0,0,0,0,0,'','','N.R.','0','','0','','1','N.R.','US106     KK','2','0','XZ','N.R.','','XZ','','','','0','N.R.','N.R.','N.R.','C','8990','0','0','0','N.A','N.A','0','1','0','0','0','0','0','0','0','0','','N.A','0','0','1','20141213')
this staement has 80 fields

where as you need to insert 81 fields
0
 
PortletPaulCommented:
ALWAYS specify the fields you are about to insert
e.g.

INSERT INTO Contacts
(type, details)         -- ---------------------- list the fields
VALUES
('Email', 'admin@xyz.com'),
('Twitter', '@xyz');
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>INSERT INTO T41 values
Oh hell, Paul beat me to it.

The ONLY (own - lee) time it is truly acceptable to not specifiy columns in an INSERT statement is when you're taking a Microsoft exam and the question is 'What is the fastest way to insert?'.  Microsoft asks a lot of trick questions like that on exams.

Otherwise, you're just begging for an error to occur due to a typo.  Worse, if this code ever makes it to production and somebody adds a column to T41, all of your 'INSERT INTO T41 values..' statements will fail.  So the only true value of such syntax is to identify amateur developers and behave accordingly.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now