Stored Proc syntax
-
Hello,
Kindly check if I did the correct syntax. The command completed successfully when I parse the query. But when I run it, I encountered "Invalid column name 'serv_lev'." in line 1 of my query. I checked the column name and it is correct. Kindly advise. Thanks.
if serv_lev is not null
insert tblMatchNSI(serv_lev,ctry_code,expamt,impamt,LCamt,usdamt,wkending,trans_code,ship_id)
SELECT dbo.ExpHybrid.serv_lev, dbo.tblTempNSI.*
FROM dbo.ExpHybrid RIGHT OUTER JOIN
dbo.tblTempNSI ON dbo.ExpHybrid.ship_id = dbo.tblTempNSI.Ship_id
-
I think that you're looking for:
INSERT INTO tblMatchNSI(serv_lev,ctry_code,expamt,impamt,LCamt,usdamt,wkending,trans_code,ship_id)
SELECT ExpHybrid.serv_lev, tblTempNSI.*
FROM dbo.ExpHybrid ExpHybrid
RIGHT OUTER JOIN dbo.tblTempNSI tblTempNSI
ON ExpHybrid.ship_id = tblTempNSI.Ship_id
WHERE ExpHybrid.serv_lev IS NOT NULL
If this is not correct, please can you give more information such as table definitions etc. -
Hello.. thanks a lot it work. But the reason why I want to put a condition because if the serv_lev is not null, it will insert to tblMatchNSI and if it is null, it will insert to tblNonMatchNSI.
How will I handle this? Thanks again so much.
-
If you notice the WHERE clause in the statement this will stop any rows with NULL values in the serv_lev column being inserted into tblMatchNSI.
You can have a second query similar to the first but change the INSERT INTO tblMatchNSI and IS NOT NULL to INSERT INTO tblNonMatchNSI and IS NULL respectively.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply