Insert into Statmenet Problem - Urgent....

[gazal]

hi guys

i am writing the following statement to insert records from one table to another

Insert into SubscriberMaster (SubCode, SubName, SubAdd1)
Select OldSubsCode, OldSubsName, OldSubsAddress from
OldSubscriberMaster

I am not able to execute this statment becuase my SubscriberMaster Table consists of many columns out of which few are not NULLABLE, So the Sql statment fails saying can not insert NULL values in column xyz...

How do i insert into specified columns only??? please help...

I am using MSDE 2000. Windows 98SE.

Thanks

Gazal

 

[fredericofonseca]

The only option you have is to change the other columns to allow NULL.

As this may not be an option if they are part of an index/key, you will probably need to supply the missing values.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PatrickIRL]

Not sure if MSDE supports the IsNull function but if it does you can use it this way ...

SELECT ISNULL(OldSubsCode,'Unknown') FROM OldSubscriberMaster

This will check the field OldSubsCode for a null value, if it is null it replaces it with the value 'Unknown'

Hope this helps,

Patrick

 

[fredericofonseca]

Patrick
the problem is not the select but the insert

if you have a table defined as

tbl (
ID numeric not null
name varchar not null
address varchar null
)

you can't do the following insert

insert into tbl (name, address) values('My Name', 'My address')

UNLESS the ID field has a default value assign to it. Not all DB's support this.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[zemp]

Remember that when you have a table with 8 fields and you are inserting a new records with values for only 4 fields that other 4 fields will be set to null (as Frederico mentioned) or populated with default values (depending on how you set up the table). So you have to either allow nulls or set some default values.

Also check your Insert statement syntax. It must be like Frederico posted in his last post. Personally I don't like to use nested SQL statements in an Insert. I prefer to separate the two. Not only will that allow you to check the values before inserting them, but it just makes the code cleaner and easier to read. Especially when you look at it in six months.

Another option for the insert would be to use ADO parameters. See faq709-1526.

zemp

 

[fredericofonseca]

Just to clarify.

The SQL can be as you did (e.g. insert from select), but you need to have the mandatory fields on it.


As for doing one select and one insert separated, this is slower and should be avoided UNLESS there is a need to transform/validate some of the fields. Even the transform can sometimes be done as part of the SQL itself. (e.g. cast(date_field as char(20)))



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[gazal]

hi friends

thanks for ur responses, u all were right, i had to change the not nulls to allow nulls for the statment to work...

thanks any way... but i guess its bit weird...

anyways... its microsoft....

gazal