Solved

Problem to insert

Posted on 2015-01-02
3
115 Views
Last Modified: 2015-01-04
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
Comment
Question by:HuaMinChen
3 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now