gogetsome
asked on
insert into table with computed column
Hello, I need to migrate some data from one table to another table. My insert is failing due to computed column on the target table. How do I get around the computed column.
Insert zoomdev..orderdetails
(
orderdetailid,
orderid,
productcode,
qty,
price,
discount,
--extprice, the computed column
availabledownloads,
isdownloadable,
rowcreated
)
select
orderdetailid,
orderid,
productcode,
qty,
price,
discount,
--extprice, the computed column
availabledownloads,
isdownloadable,
rowcreated
From zoomdata..orderdetails
where orderid not in (select orderid from zoomdev..orderdetails)
Insert zoomdev..orderdetails
(
orderdetailid,
orderid,
productcode,
qty,
price,
discount,
--extprice, the computed column
availabledownloads,
isdownloadable,
rowcreated
)
select
orderdetailid,
orderid,
productcode,
qty,
price,
discount,
--extprice, the computed column
availabledownloads,
isdownloadable,
rowcreated
From zoomdata..orderdetails
where orderid not in (select orderid from zoomdev..orderdetails)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
that mainly depends on this:
where orderid not in (select orderid from zoomdev..orderdetails)
is there a index on orderid for the orderdetails table?
where orderid not in (select orderid from zoomdev..orderdetails)
is there a index on orderid for the orderdetails table?
since dont know the computed column formula, index structure and the number of records and also the machine properties that is running the sql server its hard to say something about the execution time. Let it run until you get an error or execution finishes successfully.
you can also modify your sql as following
Insert zoomdev..orderdetails
(
orderdetailid,
orderid,
productcode,
qty,
price,
discount,
--extprice, the computed column
availabledownloads,
isdownloadable,
rowcreated
)
select
orderdetailid,
orderid,
productcode,
qty,
price,
discount,
--extprice, the computed column
availabledownloads,
isdownloadable,
rowcreated
From zoomdata..orderdetails od1
left outer join zoomdev..orderdetails od2 on od1.orderid = od2. orderid
where od1. orderid is null
Insert zoomdev..orderdetails
(
orderdetailid,
orderid,
productcode,
qty,
price,
discount,
--extprice, the computed column
availabledownloads,
isdownloadable,
rowcreated
)
select
orderdetailid,
orderid,
productcode,
qty,
price,
discount,
--extprice, the computed column
availabledownloads,
isdownloadable,
rowcreated
From zoomdata..orderdetails od1
left outer join zoomdev..orderdetails od2 on od1.orderid = od2. orderid
where od1. orderid is null
tigin44,
I think you mean:
where od2. orderid is null
I think you mean:
where od2. orderid is null
ASKER