i am trying to insert the values of agencytype column in agent table into agencytype column in financialdata_one table

use agentinformationsql
insert into financialdata_one (agencytype) select agencytype from agent

i am trying to run this query and i get a error msg Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'FinancialID_One', table 'agentinformationSQL.dbo.FinancialData_One'; column does not allow nulls. INSERT fails.



could anyone please help me out with this query thanx in advance

use agentinformationsql
insert into financialdata_one (agencytype) select agencytype from agent
where agencytype is not null

thanq u for ur response but its givin the same error msg

Please post FULL insert query. If the query is only the one above, it should work.
Also try this, if you have changed the default ANSI NULL settings.

use agentinformationsql
insert into financialdata_one (agencytype) select agencytype from agent
where agencytype is not null and agencytype <> NULL

Not passing a value for the FinancialID_One column is the issue. Without knowing the datatype of the FinancialID_One column, I am not sure what value to pass in the example.

Possible example...
insert into financialdata_one (agencytype, financialid_one)
select agencytype, <finanialid_one value>
from agent
where agencytype is not null

In the future, post the schema of the table. It will help understand the insert requirements.

HTH

Adam Gossage
Lake Wylie, SC, USA

Yes, adam is right... Praveen should have posted the schema definition along with the error to help understand.

When you are trying to insert values into a table with values to specific columns and not the for the all the columns of the table schema, you should make sure that the columns in the target table that are left out from the INSERT statement should ALLOW NULL, else such columns should also be included in the INSERT statement, unless it is an IDENTITY column. This is how you should be managing inserts into tables, that doesn't include values for the entire row.

Cheers.

_________________________
- Vijay G
Strive for Perfection