Insert Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert Statement

Author

Topic

helpme09
Starting Member

4 Posts

Posted - 2008-10-21 : 12:47:08

Need some help with inserting data from existing table to another table. I am using the following statement but i get an error:
Cannot insert the value NULL into column 'Employee Hold', table 'Test'; column does not allow nulls. INSERT fails.

Insert into Test ([Prox Card No.])
Select [prox #] from csvtest where exists(Select [Prox Card No.] from Test where test.[no.] = csvtest.[no])

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-10-21 : 12:50:35

Insert into Test ([Prox Card No.])
Select [prox #] from csvtest where exists(Select [Prox Card No.] from Test where test.[no.] = csvtest.[no] and test.[no.] is not null)

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-21 : 13:02:46

Your taget table Test has not only the column [Prox Card No.].
There is another column named [Employee Hold].
Cause you are not considering this column in your statement, SQL is going to insert NULL into it but NULL is not alowed.

Webfred

Planning replaces chance by mistake

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 13:09:04

and if you want to poulate only values of [Prox Card No.] as in above insert statement, create a default constraint on [Employee Hold] to insert default value to avoid the current error.

helpme09
Starting Member

4 Posts

Posted - 2008-10-21 : 13:14:45

how would i do that?

we are imported an excel soreadsheet to a table called csvtest but i need to grad all the [prox card no.] and insert only those to an existing table 'Test" but it needs to match up by test.[no.] = csvtest.[no]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 13:21:23

quote:
Originally posted by helpme09

how would i do that?

we are imported an excel soreadsheet to a table called csvtest but i need to grad all the [prox card no.] and insert only those to an existing table 'Test" but it needs to match up by test.[no.] = csvtest.[no]

then you need to create a default constraint as below

ALTER TABLE Test ALTER COLUMN [Employee Hold] <datatype> NOT NULL DEFAULT <defaultvalue>

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-21 : 13:23:11

I think, when there is a need to match test.[no.] = csvtest.[no] then what you want to do is not an insert!
I think you would like to update [Prox Card No.] in Test.

Is that right?

Webfred

Planning replaces chance by mistake

helpme09
Starting Member

4 Posts

Posted - 2008-10-21 : 13:30:59

That's correct i should do an update instead of insert but when i change the syntax i get an error: Incorrect syntax near the keyword 'IN'.

update [Prox Card No.] IN test
Select [prox #] from csvtest where exists(Select [Prox Card No.] from test where test.[no.] = csvtest.[no])

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 13:34:43

[code]update t
SET t.[Prox Card No.]= c.[prox #]
from csvtest c
join test t
on t.[no.] = c.[no]
[/code]

helpme09
Starting Member

4 Posts

Posted - 2008-10-21 : 13:50:27

Thanks! One more question if there is already a [Prox Card No.] in the test table how do i NOT replace it with the updated [prox #] from csvtest table?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-21 : 15:37:31

Assuming that [Prox Card No.] is NULL when update is desired and [Prox Card No.] is NOT NULL when update is not desired...
Test this:
update t
SET t.[Prox Card No.]= c.[prox #]
from csvtest c
join test t
on t.[no.] = c.[no]
where t.[Prox Card No.] IS NULL

Webfred

Planning replaces chance by mistake

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources