error with insert statement

  • 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

  • What about the table from which the data is being selected, Channels2? Would help to see its definition as well.

  • Some of records in source table have string values larger than allowed by definition of column at destination.

    DDL for [Test].[dbo].[Channels2]?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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.

  • 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.

  • 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

  • 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

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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