Solved

Problem to insert

Posted on 2015-01-02
3
130 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 40527621
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
ID: 40527631
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
ID: 40527833
>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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

732 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