asked
inserting data from one database table to another database table
hello all, i am trying to insert the data from one table ina database in to another table which is present in another database but i am failing. please provide me tsql statement to do so.i am using the following statement please suggest insert into Olm.Table_Checknote_Milestone(name) select Milestone_Namex from table_MileStone_Name where MilestoneID in (select distinct Milestone_ID from table_Milestone_Process where Process_ID=9) please help
the following error i am getting when i added dbo schema as suggested by magnus Msg 515, Level 16, State 2, Line 1 Cannot insert the value NULL into column 'SortOrder', table 'Olm.dbo.Table_Checknote_Milestone'; column does not allow nulls. INSERT fails. The statement has been terminated.
It's always good to give some more information than just "It doesn't work", or "i am failing". Is there an error message? I'm guessing now that you want to insert data into a table named Table_Checknote_Milestone which is located in a database named Olm. If that's the case, all you're missing is the schema-name that Table_Checknote_Milestone resides in. I'd guess it's in schema **dbo**. Then your statement should be: insert into Olm.dbo.Table_Checknote_Milestone(name) select Milestone_Namex from dbo.Table_MileStone_Name where MilestoneID in (select distinct Milestone_ID from dbo.Table_Milestone_Process where Process_ID=9) Though you don't have to schema-qualify your object names, it's a good habit to do so, and not rely on the default schema. Apart from that, you could probably rewrite your query to use a join instead of a subquery, or at least remove the distinct clause of the subquery. But that's another topic.
4 People are following this question.
