August 3, 2011 at 11:22 pm
This is an important problem for me, that I have spent a lot of time on. So, I have simulated my problem with the following simple tables, to illustrate exactly what the issue looks like.
Situation: i am migrating data from 'old' to 'new' table. The new table contains two new columns (phaseid & schemamapping), one in which the values can be derived from a third table with a column called 'phaseid', and another columnwhich has no prior existence AND is an identity column, called 'schemamapping'.
My MERGE statement is below (and works), but it is lacking a method for inserting values into schemamapping. How do i finish the merge statement so that it contains auto incremented number values in the 'schemamapping' column?
Thanks in advance.
create table phase_lookup
(phaseID smallint primary key not null,
fYear varchar(10) not null);
create table old
(id smallint primary key not null,
fYear smallint not null,
budget decimal not null
)
create table new
(id smallint primary key not null,
fYear smallint not null,
budget decimal not null,
phaseid smallint not null REFERENCES phase_lookup(phaseID),
schemamapping smallint IDENTITY(1,1) NOT NULL
);
insert into old (id, fYear, budget) values (1, 2009, 400),(2, 2010, 500), (3, 2011, 200), (4, 2012, 300);
insert into phase_lookup (phaseID, fYear) values (1, '2009/10'), (2, '2011/12');
select * from old;
select * from new;
select * from phase_lookup;
merge new as T
using (select id, fYear
, budget
, phaseid =
CASE fYear
when '2009' THEN 1
WHEN '2010' THEN 1
WHEN '2011' THEN 2
WHEN '2012' THEN 2
END
FROM old o
) AS s
ON s.ID = T.ID
WHEN NOT MATCHED THEN INSERT(id, fYear, budget, phaseid)VALUES(s.id, s.fYear, s.budget, s.phaseid);
August 4, 2011 at 2:10 am
hxkresl (8/3/2011)
...How do i finish the merge statement so that it contains auto incremented number values in the 'schemamapping' column?..
Since 'schemamapping' is set to autoincrement, it shouldn't appear in the MERGE statement unless you want to override the autoincrement property and populate it. The pseudocode you've posted looks correct. Are you saying that inserting a row into the table 'new' fails to generate a value for the 'schemamapping' column of that row?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 4, 2011 at 3:11 am
Have you tried to execute your code and checked the results? It does work as it is!
August 4, 2011 at 7:06 am
Thank you. It did populate, didn't it?! :doze:
I will now try with the none-pseudocode.
August 4, 2011 at 7:20 am
OK, please excuse the fact the the problem I am hvig with 'real' merge statement is now different.
When I run the real statement I get the below error:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
My research would indicate this is the cause of trying to insert more data than a column can accomodate:But, line 1 is only referring to the very first line of code, the merge statement, eg:
merge mogknicker.mog.EnligtenmentSchemaMap as T
The select statement, which is embedded in the USING clause, is exactly like the one in the pseudocode, but has more case statements, and runs fine. Do you have experience or suggestions for getting rid of this error?
Helen
August 4, 2011 at 7:25 am
hxkresl (8/4/2011)
My research would indicate this is the cause of trying to insert more data than a column can accomodate:
Yup.
But, line 1 is only referring to the very first line of code, the merge statement, eg:
It points at the start of the statement that caused the error, nothing more detailed than that
Do you have experience or suggestions for getting rid of this error?
Examine the values you're trying to insert, the columns you're trying to insert into and see which one the data exceeds the column length. It'll be a char, varchar, nchar, nvarchar, binary or varbinary column.
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
August 4, 2011 at 7:26 am
hxkresl (8/4/2011)
.... Do you have experience or suggestions for getting rid of this error?Helen
Check that the datatypes of the target table match those of the output columns of the SELECT used as the source.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 4, 2011 at 10:42 am
Really stumped. Trying to think of what other tests I could use to troubleshoot.
Datatypes and sizes of straight accross data migration are compatible.
In the case of CASE statements, there are instances I am determing what INT values to put into target columns based on VARCHAR values in source database column, but that is only logic.
For example:
Source table column Business_Type is varchar(32) and based on the values in that column I am determining what int value to put into target column, ModelId. This should not be a factor, of course.
ModelId =
CASE BS. Business_Type
WHEN 'consumer' THEN 1
WHEN 'css' THEN 1
WHEN 'MSdp' THEN 2
In all cases, the actual values I am inputting are within the constraints on the target columns. Can you think of another way I can troubleshoot?
August 4, 2011 at 10:55 am
Can you please provide the DDL for all tables involved and full code you have?
It's hard to guess where it may fail. However the error is quite exact: somedata is going to be truncated in a process as it's longer than expected in a place where this check is performed. Please note, it doesn't need to be destination column...
So, please DDL and code;-)
August 4, 2011 at 11:00 am
It's not going to be an integer column. They return arithmetic overflow errors, not 'string or binary data...'.
It's a string column (varchar, nvarchar) or binary (varbinary)
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
August 4, 2011 at 11:03 am
Ok, found that in my INSERT statements I had the column names and values mismatched (wrong order) corrected order to align and am on to bigger better errors.
August 4, 2011 at 12:21 pm
OK, thank you.
I found the problem. I had an the order of column names in the insert statment incorrectly aligned with the values I was inserting.
Yes, on varchar columns.
Thank you for your support. I'm on to the next set of errors.
Helen
August 4, 2011 at 1:27 pm
August 5, 2011 at 2:12 am
hxkresl (8/4/2011)
...merge mogknicker.mog.EnligtenmentSchemaMap as T
...
What?!! No, no. Classy mogs go commando.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 5, 2011 at 12:37 pm
Well, as I said, those tables are just quick mock ups to illustrate my issue, and do not reflect the naming convention or design practices of the development team where I work. Alas, I could not share the real tables.;-)
Thank you Celko for telling me that I did a better than average job of sharing the problem. From you I take that as a compliment and I am interested in your books. There is so much truth in what you said and I appreciate it.
But I have come full circle to my problem with the identity column. I am migrating data into an identity column whose values cannot be wholesale reseeded. I have to maintain the associations between existing rows of information in the target table and their identity column values. But, as I am bringing in new data, I also must be able to do inserts on the identity column for the new rows.
So, the following has been suggested to me:
1. ALTER TABE to remove identity field from 'phaseid'
2. MERGE for the WHERE MATCHED clause
3. For the WHERE NOT MATCHED clause run something like this to set the identity at a value much higher so that it doesn't conflict with existing identity values (eg 1000,000):
SELECT @Identity = CASE WHEN ISNULL(MAX(VendorEngagementId),0) > @IdentityStart
THEN ISNULL(MAX(VendorEngagementId),0)
ELSE @IdentityStart
END
FROM mgo.VendorEngagement
DBCC CHECKIDENT ([mgo.Vendor], RESEED, @Identity)
GO
4. reapply the identity field (alter table statement)
Is this a good approach? Any others?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply