Import data to table with foreign key triggers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Import data to table with foreign key triggers

Author

Topic

ppritts
Starting Member

5 Posts

Posted - 2007-10-03 : 19:29:31

I've imported data from an Excel spreadsheet to a table that has fields to match the destination table I'm trying to populate. The destination table has an Insert trigger with several checks on certain fields to make sure they have corresponding records in other tables.

If I do a statement like
"INSERT INTO destinationTable
(
ItemId,
Product,
SuperID,
etc etc
)
SELECT * FROM oldtable"
it runs for a while then gives me error messages from the trigger and rolls back the Insert.

The trigger has code such as
"IF (SELECT COUNT(*) FROM inserted WHERE ((inserted.Product Is Not Null))) != (SELECT COUNT(*) FROM tblInProduct, inserted WHERE (tblInProduct.Product = inserted.Product))
BEGIN
(Error message code goes here)
END"

So, do I need to do an INNER JOIN to each of the related files?
When I try that, I get this error:
"Msg 121, Level 15, State 1, Line 2
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
Is SQL counting the foreign key fields as separate fields, or what?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-03 : 19:33:38

You need to use a column list instead of select * since your oldtable has more columns than the new one.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ppritts
Starting Member

5 Posts

Posted - 2007-10-03 : 19:43:22

When I try that, I get
"Incorrect syntax near ','."

Here's my code:

INSERT INTO dbo.tblInItem
(
ItemId,
Descr,
SuperID,
ItemType,
ItemStatus,
ProductLine,
SalesCat,
PriceID,
TaxClass,
UomBase,
UomDflt,
LottedYN,
AutoReorderYN,
KittedYN,
ResaleYN,
PictID,
UsrFld1,
UsrFld2,
UsrFld3,
UsrFld4,
CostMethodOverride,
HMRef
)
SELECT
(
ItemId,
Descr,
SuperID,
ItemType,
ItemStatus,
ProductLine,
SalesCat,
PriceID,
TaxClass,
UomBase,
UomDflt,
LottedYN,
AutoReorderYN,
KittedYN,
ResaleYN,
PictID,
UsrFld1,
UsrFld2,
UsrFld3,
UsrFld4,
CostMethodOverride,
HMRef
)
FROM dbo.tblInItem1
INNER JOIN dbo.tblInProductLine
ON dbo.tblInItem1.ProductLine = dbo.tblInProductLine.ProductLine
ORDER BY dbo.tblInProductLine.ProductLine

Do you see anything wrong with it? It's got me stumped.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-03 : 19:54:30

remove the brackets for SELECT.


INSERT INTO dbo.tblInItem
(
ItemId,
Descr,
SuperID,
ItemType,
ItemStatus,
ProductLine,
SalesCat,
PriceID,
TaxClass,
UomBase,
UomDflt,
LottedYN,
AutoReorderYN,
KittedYN,
ResaleYN,
PictID,
UsrFld1,
UsrFld2,
UsrFld3,
UsrFld4,
CostMethodOverride,
HMRef
)
SELECT
(
ItemId,
Descr,
SuperID,
ItemType,
ItemStatus,
ProductLine,
SalesCat,
PriceID,
TaxClass,
UomBase,
UomDflt,
LottedYN,
AutoReorderYN,
KittedYN,
ResaleYN,
PictID,
UsrFld1,
UsrFld2,
UsrFld3,
UsrFld4,
CostMethodOverride,
HMRef
)
FROM dbo.tblInItem1 
INNER JOIN dbo.tblInProductLine 
ON dbo.tblInItem1.ProductLine = dbo.tblInProductLine.ProductLine 
ORDER BY dbo.tblInProductLine.ProductLine

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-03 : 20:29:56

Dinakar's post doesn't look too clear with the strike throughs, so just to be sure you understand, you need to remove your parenthesis around the column list in the select. The strike throughs in his post look like arrows.

Here is an example:

INSERT INTO Table2 (Column1, Column2)
SELECT Column1, Column2
FROM Table1
...

Please reference BOL for INSERT and SELECT syntax.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ppritts
Starting Member

5 Posts

Posted - 2007-10-08 : 16:24:08

Thanks for the help.
That got me past that error.

But without the '()''s and with INNER JOIN's setup for the other tables I get '0 records read.'

If I use a LEFT OUTER JOIN on the tables, it processes the records then rolls them back with trigger errors saying 'Unable to insert.'

(I also had to explictly list the table name each time I listed a field in the SELECT part of the statement or I got an error about ambiguous column names.)

The trigger in my message above is going off saying that a certain value ('811') doesn't exist in the other table's (one of the one's I'm LEFT OUTER JOINing) field, but if I open the table I can see that the record is there! How can I get it to read these tables?

I'm very tempted to just deactivate the triggers, but I've been warned to not deactivate the triggers because any corrupt data will cause all sorts of weird errors.

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 20:07:30

Your trigger is checking the [Product] column in the table (don't know which table, you haven't posted enough code to see that) and making sure that it exists in tblInProduct.Product.

Your code is trying to insert to tblInItem.ProductLine, from tblInItem1.ProductLine, and is making sure that tblInItem1.ProductLine exists in tblInProductLine.ProductLine.

The trigger's
[Product] / tblInProduct.Product
does not match your code's
tblInItem.ProductLine / tblInProductLine.ProductLine

so maybe you are correctly checking tblInItem.ProductLine / tblInProductLine.ProductLine, but the trigger is checking something else.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources