how to insert one column of table in one databse to other column of table in another databse

Question

Post reply

how to insert one column of table in one databse to other column of table in another databse

vinay.varaala

Mr or Mrs. 500

Points: 576
More actions
September 4, 2012 at 8:19 am

#266157

query:insert into fuel.dbo.Dispenser_pump_mapping (dispenser)
select Dispenser from Wetstock_Reporting.dbo.Dispenser_Pump_Mapping
error:
Cannot insert the value NULL into column 'simmons_id', table 'fuel.dbo.Dispenser_pump_mapping'; column does not allow nulls. INSERT fails.
The statement has been terminated.
how to get rid of the null because i declared it has not null.
i want to insert only one column with same type in other databse table same column defition

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Can you post the CREATE TABLE script for fuel.dbo.Dispenser_pump_mapping?

Looks to me like this column is possibly a FK.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 2

Dispenser_pump_mapping TABLE:

create table Dispenser_pump_mapping

(

dis_pump_map_id int IDENTITY,

simmons_id int not null,

dispenser int primary key ,

fp int not null,

nozzle int not null,

tank_id int foreign key (tank_id) references

Tank(tank_id)

)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

vinay.varaala (9/4/2012)
Dispenser_pump_mapping TABLE:
create table Dispenser_pump_mapping
(
dis_pump_map_id int IDENTITY,
simmons_id int not null,
dispenser int primary key ,
fp int not null,
nozzle int not null,
tank_id int foreign key (tank_id) references
Tank(tank_id)
)

So, what do you want to do?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

There are several columns in your destination table that don't allow NULLs. Therefore you need either to specify values for those columns when you insert a row into that table, or you need to set defaults for those columns so that values are automatically entered.

John

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 5

ALRIGTH ,

actually first database I reduced the database tables size using ER now I want to insert that data into respective columns from that respective column tables

cheers

vinay.varaala Mr or Mrs. 500 Points: 576 More actions · Answer 6

insert into fuel.dbo.Dispenser_pump_mapping (dispenser)

select Dispenser from Wetstock_Reporting.dbo.Dispenser_Pump_Mapping

from the above query how to put default value for simmons_id

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

You don't specify defaults in the query, you put them in the table definition. See the ALTER TABLE topic in Books Online for details. If you can't, or don't want to, do that, you'll need to use a constant, a row number, or a value from the source table, like these examples:

insert into fuel.dbo.Dispenser_pump_mapping (dispenser,simmons_id)

select Dispenser, simmons_id

from Wetstock_Reporting.dbo.Dispenser_Pump_Mapping

or

insert into fuel.dbo.Dispenser_pump_mapping (dispenser,simmons_id)

select Dispenser, 1

from Wetstock_Reporting.dbo.Dispenser_Pump_Mapping

or

insert into fuel.dbo.Dispenser_pump_mapping (dispenser,simmons_id)

select Dispenser, ROW_NUMBER() OVER (ORDER BY Dispenser)

from Wetstock_Reporting.dbo.Dispenser_Pump_Mapping

John