| Author |
Topic |
|
Analyzer
Posting Yak Master
115 Posts |
 Posted - 2013-06-27 : 06:48:06
|
| Hi, Performing INSERT into table which schema is identical to table data is sourced (used select * into TblB from TblA to create)Problem is the insert fails each time with "String or binary data would be truncated". Knowing this means a value string is to large to fit with one of (or more) of the columns data types. How can this happen when the values being insert already exist in table of identical schema.Would anyone know of a condition when this may happen?insert into TblB VALUES('Col1','Col2','Col3','Col4')select Col1, Col2, col3, Col4 from TblAThanks in advance |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-27 : 06:54:54
|
| One possibility is TblA columns changing the datatype to increased length manually by someone after the creation and being populated with bigger data which tblb cant hold.Other possibility is using some calculation for populating column of tblb in the select statement------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-27 : 08:23:41
|
| Script both tables and carefully compare each column definition. There has to be something different. To script, right click on the table name in SSMS object explorer and Script Table as -> Create to -> New query editor window. |
|
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2013-06-27 : 09:49:12
|
| Thanks All. Thought I had been thorough, crossed checked every col, data type latest check found additional Col which skewed the col sequence. New issue now. Get the below error although no data types in source or target are varchar? They are INT to INT so no idea why a conversion error is produce. If the error states Col3 is the problem, I remove Col3 and then the error moves one Col along to Col4. Any ideas what could be causing this?Conversion failed when converting the varchar value 'Col3' to data type int. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-27 : 10:21:52
|
The syntax of the insert statement is not correct. Te way you have written it, it is really two statements - first one is inserting the strings col1, col2, col3, col4 into TblB. The second is selecting some columns from TblA. To insert data into TblB by selecting from TblA, you should remove the VALUES keyword like so:insert into TblB (Col1,Col2,Col3,Col4)select Col1, Col2, col3, Col4 from TblA |
|
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2013-06-27 : 10:22:33
|
| I found the failing point. Elementary syntax error (we all have off days) Was using Insert Into [#tmptable] VALUES(col1, col2,col3....)Think I found a dodgy information source on the web with incorrect syntax. Appreciate your help guys |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-27 : 10:30:37
|
quote:
Originally posted by Analyzer
I found the failing point. Elementary syntax error (we all have off days) Was using Insert Into [#tmptable] VALUES(col1, col2,col3....)Think I found a dodgy information source on the web with incorrect syntax. Appreciate your help guys
it should be thisINSERT Into [#tmptable]SELECT col1, col2,col3 FROM yoursourcetable EDIT:Didnt see early post. James has already given solution to you ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-27 : 10:34:06
|
quote:
Originally posted by Analyzer
I found the failing point. Elementary syntax error (we all have off days) Was using Insert Into [#tmptable] VALUES(col1, col2,col3....)Think I found a dodgy information source on the web with incorrect syntax. Appreciate your help guys
VALUES clause is used when you want to insert constants or literals into a table. For example:Insert Into [#tmptable] VALUES('abcd',1,17.5,'xyz');That is assuming that the number and ordinal positions of clumns in the #tmptable match the list in the values clause (excepting identity columns, computed columns etc.). |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2013-06-27 : 11:42:34
|
| Thanks guys. Back to the T-SQL school for me :) |
|
|
|
|
String or binary data would be truncated.