insert into table with computed column

gogetsome
gogetsome used Ask the Experts™
on
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)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
you skip the column in the insert, so you should NOT get an error due to that computed column, unless the values you insert make the computed column expression raises an error, like data type overflow ...

please clarify the exact error, and if possible, try to isolate what data raises the issue.
Commented:
you shouldnt suply any value for the computed column and ass I seen you are doing so. Check that whether any other column has computed value or any column that is used by computed column formula and doesnt provided any value.

Author

Commented:
How long should I run the statement? I do not get an error but stop the statement after three minutes. There is only 800 rows or so to insert. Is it normal to take that long in my scenario?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
that mainly depends on this:

where orderid not in (select orderid from zoomdev..orderdetails)

is there a index on orderid for the orderdetails table?

Commented:
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.

Commented:
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
Top Expert 2012

Commented:
tigin44,

I think you mean:
where od2. orderid is null

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial