April 17, 2012 at 9:56 am
Hi All,
I am trying to do an insert which uses a select and I am getting an error:
Msg 8152, Level 16, State 4, Line 2
String or binary data would be truncated.
The statement has been terminated.
insert into [Test].[dbo].[Channels]
SELECT
[MAS90ID]
,[Name]
,'Test@testmail.com' as EmailInvoice
,null as [CompTypeID]
, convert(decimal(18,1),[CommPlan]) as [CommPlan]
, convert(decimal(18,1),[CommPlan]) as [CommPeople]
, convert(decimal(18,1),[CommPlan]) as [CommPhone]
, convert(decimal(18,1),[CommPlan]) as [CommText]
, convert(decimal(18,1),[CommPlan]) as [CommEmail]
, convert(decimal(18,1),[ChannelRate]) as [ChannelRate]
,1 as BillingReportID
,[SLXID]
,null as [Contact]
,'Test@testmail.com' as Email
,null as [Phone]
,null as [Address1]
,null as [Address2]
,null as [City]
,null as [State]
,null as [Zip]
,null as [Country]
,null as [Notes]
,1 as active
,3 as methodid
,null as [CCard#]
,null as [CC_ExpiresMonth]
,null as [CC_ExpiresYear]
,null as [CC_ExpireCode]
,null as [Routing#]
,null as [Account#]
,null as [CreditCardTypeID]
,null as [SageVaultId]
FROM [Test].[dbo].[Channels2]
this is structure of the table channels.
CREATE TABLE [dbo].[Channels](
[ID] [int] IDENTITY(1,1) NOT NULL,
[MAS90ID] [nvarchar](10) NULL,
[Name] [nvarchar](50) NULL,
[EmailInvoice] [nvarchar](50) NULL,
[CompTypeID] [int] NULL,
[CommPlan] [decimal](18, 1) NULL,
[CommPeople] [decimal](18, 1) NULL,
[CommPhone] [decimal](18, 1) NULL,
[CommText] [decimal](18, 1) NULL,
[CommEmail] [decimal](18, 1) NULL,
[ChannelRate] [decimal](18, 1) NULL,
[BillingReportID] [int] NULL,
[SLXID] [nvarchar](10) NULL,
[Contact] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
[Phone] [nvarchar](15) NULL,
[Address1] [nvarchar](100) NULL,
[Address2] [nvarchar](100) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](2) NULL,
[Zip] [nvarchar](10) NULL,
[Country] [nvarchar](50) NULL,
[Notes] [nvarchar](max) NULL,
[Active] [bit] NULL,
[MethodID] [int] NULL,
[CCard#] [nvarchar](19) NULL,
[CC_ExpiresMonth] [int] NULL,
[CC_ExpiresYear] [int] NULL,
[CC_ExpireCode] [nvarchar](10) NULL,
[Routing#] [nvarchar](50) NULL,
[Account#] [nvarchar](50) NULL,
[CreditCardTypeID] [int] NULL,
[SageVaultId] [uniqueidentifier] NULL,
CONSTRAINT [PK_Channels] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Channels] WITH CHECK ADD CONSTRAINT [FK_Channels_BillingReports] FOREIGN KEY([BillingReportID])
REFERENCES [dbo].[BillingReports] ([ID])
GO
ALTER TABLE [dbo].[Channels] CHECK CONSTRAINT [FK_Channels_BillingReports]
GO
ALTER TABLE [dbo].[Channels] WITH CHECK ADD CONSTRAINT [FK_Channels_CompType] FOREIGN KEY([CompTypeID])
REFERENCES [dbo].[CompType] ([ID])
GO
ALTER TABLE [dbo].[Channels] CHECK CONSTRAINT [FK_Channels_CompType]
GO
ALTER TABLE [dbo].[Channels] WITH CHECK ADD CONSTRAINT [FK_Channels_CreditCardType] FOREIGN KEY([CreditCardTypeID])
REFERENCES [dbo].[CreditCardType] ([ID])
GO
ALTER TABLE [dbo].[Channels] CHECK CONSTRAINT [FK_Channels_CreditCardType]
GO
ALTER TABLE [dbo].[Channels] WITH CHECK ADD CONSTRAINT [FK_Channels_Methods] FOREIGN KEY([MethodID])
REFERENCES [dbo].[Methods] ([ID])
GO
ALTER TABLE [dbo].[Channels] CHECK CONSTRAINT [FK_Channels_Methods]
GO
can someone please help where i am doing wrong
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
April 17, 2012 at 10:01 am
What about the table from which the data is being selected, Channels2? Would help to see its definition as well.
April 17, 2012 at 10:03 am
Some of records in source table have string values larger than allowed by definition of column at destination.
DDL for [Test].[dbo].[Channels2]?
April 17, 2012 at 10:06 am
Most likely, length of data from your select table exceeds the lenght of data in your test table. Check for lengths of columns in both tables.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 17, 2012 at 10:08 am
S_Kumar_S (4/17/2012)
Most likely, length of data from your select table exceeds the lenght of data in your test table. Check for lengths of columns in both tables.
Nothing like stating the obvious for the third time. Reason why we asked for the DDL for the table Channels2.
April 17, 2012 at 10:16 am
Thats the DDL for the source table
CREATE TABLE [dbo].[Channels2](
[Name] [varchar](50) NULL,
[ChannelRate] [varchar](50) NULL,
[MAS90ID] [varchar](50) NULL,
[SLXID] [varchar](50) NULL,
[CommPlan] [varchar](50) NULL
) ON [PRIMARY]
GO
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
April 17, 2012 at 10:24 am
SOURCE: [MAS90ID] [varchar](50) to DESTINATION: [MAS90ID] [nvarchar](10)
SOURCE: [SLXID] [varchar](50) to DESTINATION: [SLXID] [nvarchar](10)
Run the following:
SELECT * FROM [dbo].[Channels2] WHERE LEN(MAS90ID) > 10 OR LEN(SLXID) > 10
April 17, 2012 at 10:25 am
MAS90ID and SLXID are varchar(50) in the source table, nvarchar(10) in the destination. Likely there are values over 10 characters long.
Widen the column in the destination table or use the LEFT function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2012 at 10:27 am
Your Channels Table definition should be changed to be the same as Channels2:
[MAS90ID] [nvarchar](10) NULL,
[SLXID] [nvarchar](10) NULL,
Should be changed to:
[MAS90ID] [nvarchar](50) NULL,
[SLXID] [nvarchar](50) NULL,
EDIT : Gail beat me to it
April 17, 2012 at 10:45 am
Thanks Guys. That was helpful.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply