Solved

Problem to insert

Posted on 2015-01-02
3
131 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 49

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 66

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

696 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