INSERT INTO statements

Question

INSERT INTO statements

veritas_86

SSC Eights!

Points: 954
More actions
April 24, 2009 at 12:04 pm

#384443

I'm having an interesting problem with an INSERT statement.
I created a table 'Test' with one column 'Col' and used an INSERT INTO statement to populate it with some data
CODE:
INSERT INTO Test(Col)
VALUES (45)
This worked fine
Then I went to a table that already exists in my database, we'll call it MyTable, with many columns, and tried a similar change
INSERT INTO [dbo].[MyTable](hex)
VALUES('45ff')
where the column name is hex
the column is type nchar(10)
When i try to run it I get an error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'TBD' to data type int.
this varchar value does not exist in column 'hex' , and column 'hex' does not hold varchar values ('hex' is just filled with NULLS)
note there are other columns in the table 'MyTable' that have datatype varchar
So what is happening? Why is SQL trying to change other things in my table? How do I get around this problem? Any help is greatly appreciated!
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

What data type is column Hex? Binary? Int? Varchar?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 2

If the hex column is nchar(10), something else must be going on to cause the error message. For example, the code below works in the scenario described. Do you have any triggers on the table? Sometimes they cause messages like this with inserts.

Create Table #Test (abc varchar(10), hex nchar(10))

INSERT INTO #Test(hex)

VALUES('45ff')

Select * from #Test

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 3

See if you have a column defined in MyTable with a default defined as 'TBD'.

When inserting into a table, you have to provide values for all columns that do not have a default defined. If there is a default defined and the column is not included in the insert - the default is used. That is probably why you are seeing this error - the default is defined as 'TBD' for an int column.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

veritas_86 SSC Eights! Points: 954 More actions · Answer 4

Jeffrey Williams (4/24/2009)
See if you have a column defined in MyTable with a default defined as 'TBD'.
When inserting into a table, you have to provide values for all columns that do not have a default defined. If there is a default defined and the column is not included in the insert - the default is used. That is probably why you are seeing this error - the default is defined as 'TBD' for an int column.

Let me know if I misunderstood your statement.

I do have a column in 'MyTable' that has a default of 'TBD' however I'm not looking to add data to every column. 'MyTable' already is filled with lots of data, in this case I added a new Column to MyTable and I'm looking to add data to it. Therefore my INSERT statement is only adding data to the 'hex' column (its the new one)

Would I have to 'tell' SQL to skip over the other columns somehow in my insert statement?

Thanks for your timely reply

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 5

You don't insert columns - you insert rows. Each insert statement is going to insert 1 or more rows into that table.

It sounds like you are trying to update the value in this new column for all existing rows. If that is the case, you need to issue an update statement - not an insert.

UPDATE MyTable

SET hex = 'value';

The above will update every row and set the column to the specified value.

UPDATE MyTable

SET hex = 'value'

WHERE {some criteria};

This will only update the rows that match the where clause.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 6

Your table has a defualt defined that will never work. What a default will do is if you do not specify a value for a column (which you are not) then SQL Server will use the default defined for that column. Since you can never insert 'TBD' into an interger field, there is really no use for the default. You can either remove the default or change it to something like -99 (if that is a value that would never be used for an insert).

veritas_86 SSC Eights! Points: 954 More actions · Answer 7

Jeffrey Williams (4/24/2009)
You don't insert columns - you insert rows. Each insert statement is going to insert 1 or more rows into that table.
It sounds like you are trying to update the value in this new column for all existing rows. If that is the case, you need to issue an update statement - not an insert.
UPDATE MyTable
SET hex = 'value';
The above will update every row and set the column to the specified value.
UPDATE MyTable
SET hex = 'value'
WHERE {some criteria};
This will only update the rows that match the where clause.

Thanks! I appreciate your help!