June 14, 2010 at 6:20 pm
Its inserting from the same table its selecting from, but it's truncating data coming from the same column. What am I missing?
DECLARE @To int, @From int, @LogonID int
select @To=3000064, @From=500006, @LogonID=1
INSERT INTO ClientXAttributeText (clientid, attributeid, attributevalue, insertedby)
SELECT @To, AttributeID, AttributeValue, @LogonID FROM ClientXAttributeText WHERE ClientID = @From
June 14, 2010 at 7:48 pm
SQLSeTTeR (6/14/2010)
Its inserting from the same table its selecting from, but it's truncating data coming from the same column. What am I missing?DECLARE @To int, @From int, @LogonID int
select @To=3000064, @From=500006, @LogonID=1
INSERT INTO ClientXAttributeText (clientid, attributeid, attributevalue, insertedby)
SELECT @To, AttributeID, AttributeValue, @LogonID FROM ClientXAttributeText WHERE ClientID = @From
Which column(s) is it truncating?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2010 at 4:01 am
AttributeValue
It's set to VARCHAR(8000)
I don't understand how it can be truncating if I'm SELECT(ing) from it.
June 15, 2010 at 4:06 am
not quite getting this question? I'm assuming you are losing data and think it's because of this statement?
are there any other code involved in your process that you didn't post?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 15, 2010 at 4:28 am
Nope, that is it. here is the table structure. It's truncating at AttributeValue column.
select max(len(AttributeValue)) from ClientXAttributeText
--3167
USE [Reference]
GO
/****** Object: Table [dbo].[ClientXAttributeText] Script Date: 06/15/2010 06:25:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ClientXAttributeText](
[ClientXAttributeTextID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NULL,
[ProgramID] [int] NULL,
[AssessmentID] [int] NULL,
[AttributeID] [int] NULL,
[AttributeValue] [varchar](8000) NULL,
[TypeID] [int] NULL,
[ins_datetime] [datetime] NULL,
[insertedBy] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
June 15, 2010 at 4:29 am
Don't understand the question, mainly because you haven't given us any information!
I've done some test data, but obviously didn't know what you're using. . .
DECLARE @ClientXAttributeText AS TABLE(
clientid VARCHAR(10),
attributeid VARCHAR(5),
attributevalue VARCHAR(8000),
insertedby VARCHAR(15))
INSERT INTO @ClientXAttributeText
(clientid,
attributeid,
attributevalue,
insertedby)
SELECT '500006','00001','Wonder what data should be in here','John'
UNION ALL SELECT '500006','00001','Still no idea data should be in here','Pete'
Which produces: -
/*
clientid attributeid attributevalue insertedby
---------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------
500006 00001 Wonder what data should be in here John
500006 00001 Still no idea data should be in here Pete
*/
I've then run your code
DECLARE @To INT,
@From INT,
@LogonID INT
SELECT @To = 3000064,
@From = 500006,
@LogonID = 1
INSERT INTO @ClientXAttributeText
(clientid, attributeid, attributevalue, insertedby)
SELECT @To,attributeid,attributevalue,@LogonID
FROM @ClientXAttributeText
WHERE clientid = @From
Which produced: -
/*
clientid attributeid attributevalue insertedby
---------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------
500006 00001 Wonder what data should be in here John
500006 00001 Still no idea data should be in here Pete
3000064 00001 Wonder what data should be in here 1
3000064 00001 Still no idea data should be in here 1
*/
So. . . what do you want?
June 15, 2010 at 4:39 am
All the information you need should be there. I am running:
DECLARE @To int, @From int, @LogonID int
select @To=3000064, @From=500006, @LogonID=1
INSERT INTO ClientXAttributeText (clientid, attributeid, attributevalue, insertedby)
SELECT @To, AttributeID, AttributeValue, @LogonID FROM ClientXAttributeText WHERE ClientID = @From
and it's TRUNCATING on AttributeValue. The MAX LEN in the column = 3167, the data inside the column is irrelevant. Thank you for your help.
June 15, 2010 at 4:44 am
SQLSeTTeR (6/15/2010)
Nope, that is it. here is the table structure. It's truncating at AttributeValue column.select max(len(AttributeValue)) from ClientXAttributeText
--3167
SQLSeTTeR (6/15/2010)
[AttributeValue] [varchar](8000) NULL
SQLSeTTeR (6/15/2010)
and it's TRUNCATING on AttributeValue. The MAX LEN in the column = 3167, the data inside the column is irrelevant. Thank you for your help.
. . . What you're saying is wrong.
DECLARE @test-2 AS TABLE(
test1 VARCHAR(10))
INSERT INTO @test-2
(test1)
SELECT 'abc'
--returns 3
SELECT MAX(Len(test1))
FROM @test-2
INSERT INTO @test-2
(test1)
SELECT 'abcd'
--returns 4
SELECT MAX(Len(test1))
FROM @test-2
Run this and see how it works.
June 15, 2010 at 4:50 am
What information do you need? The information I provided you was valid.
June 15, 2010 at 4:52 am
Please re-read my post and execute the SQL I showed.
The SELECT query you wrote to see the max length of the column is not doing what you think it is doing. Instead it is displaying the length of the longest piece of data in the column.
-edit-
To get the information you want from your select query, run this: -
USE [Reference]
SELECT character_maximum_length
FROM information_schema.columns
WHERE data_type IN ( 'VARCHAR' )
AND table_name = 'ClientXAttributeText'
AND column_name = 'attributevalue';
That'll display the max length of your column, instead of the max length of the data in your column.
June 15, 2010 at 5:07 am
I understand that. I scripted out the table structure above which shows the data lengths. I was showing the max length of the data did not exceed 8000 char. Sorry for the disconnect.
June 15, 2010 at 5:11 am
In that case, I'd need to be able to reproduce the error to look into it. With no data, that's not possible. When I tried using test data I wrote, the results were as expected.
June 15, 2010 at 9:13 am
SQLSeTTeR (6/14/2010)
Its inserting from the same table its selecting from, but it's truncating data coming from the same column. What am I missing?DECLARE @To int, @From int, @LogonID int
select @To=3000064, @From=500006, @LogonID=1
INSERT INTO ClientXAttributeText (clientid, attributeid, attributevalue, insertedby)
SELECT @To, AttributeID, AttributeValue, @LogonID FROM ClientXAttributeText WHERE ClientID = @From
When you say "it's truncating data" what do you really mean ?
What does the data look like before truncation ?
What does the data look like after truncation ?
Can you produce a repro script ?
A repro script is a standalone SQL script that we can run that reproduces the problem. The repro script should contain the following:
* Table creation
* Insert statements to insert sample data
* Your code
* Selects that will clearly show us the problem.
If you are not able to produce such a script, the problem is probably somewhere else.
/SG
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply