Solved

Problem to insert

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

896 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

15 Experts available now in Live!

Get 1:1 Help Now