Insert Into and recomendations on sql statements

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

Insert Into and recomendations on sql statements

Author

Topic

sbhegel
Starting Member

19 Posts

Posted - 2005-12-02 : 12:22:44

Hello,

I would like to get opinions about the code below and what I can do to improve it. I don't know if I am using the best techniques in this code. I am not running into any problems, but I am assuming there has to be cleaner ways of doing this.

Also I am trying to figure out why the INSERT INTO statement in the query is giving me the error:
"The column prefix '#ttsku' does not match with a table name or alias name used in the query."

The purpose of the code is to select all the item records from a linked server (Non MS SQL) and bring it into a temp table. I did this because I can't create a cursor to the other DB.

Using this temp table, look if the SKU table has the item in it, if it does then update the record, otherwise I need to create the record and fill in the values from the temp table.

Finally I need to delete any records that don't exist in the temp table.

Here is the code:

SELECT
pt_mstr.pt_part as part,
pt_mstr.pt_desc1 as desc1,
dbo.udf_GetEntry(cd_det.cd_cmmt,1,';') as custdesc,
dbo.udf_GetEntry(cd_det.cd_cmmt,2,';') as caformat,
dbo.udf_GetEntry(cd_det.cd_cmmt,3,';') as plformat,
dbo.udf_GetEntry(cd_det.cd_cmmt,6,';') as eaformat,
(pt_mstr.pt__qad24 * pt_mstr.pt__qad25) as pallqty,
case
when um_mstr.um_conv is not null then
round((pt_net_wt / (cast(pt_drwg_loc as numeric) /um_conv)),0)
else
round((pt_net_wt / cast(pt_drwg_loc as numeric)),0)
end as packqty,
pt_mstr.pt_drwg_loc as packsize,
pt_mstr.pt_drwg_size as packum,
dbo.udf_GetEntry(cd_det.cd_cmmt,4,';') as dist1,
dbo.udf_GetEntry(cd_det.cd_cmmt,5,';') as dist2,
pt_mstr.pt_user2 as brand,
pt_mstr.pt__qad24 as ti,
pt_mstr.pt__qad25 as hi,
pt_mstr.pt_status as status
INTO #ttsku
FROM mfgprod..pub.pt_mstr pt_mstr
left join mfgprod..pub.cd_det cd_det on
(cd_det.cd_ref = pt_mstr.pt_part
and cd_det.cd_type = 'MK'
and cd_det.cd_lang = 'US'
and cd_det.cd_seq = 0)
left join mfgprod..pub.um_mstr um_mstr on
(um_mstr.um_um = pt_mstr.pt_net_wt_um
and um_mstr.um_alt_um = pt_drwg_size
and um_mstr.um_part = '')
WHERE pt_part_type = 'FG'
and (pt_status = 'A' or pt_status = 'AMTO')
and (pt_drwg_loc <> '' and pt_drwg_loc <> '0')

declare c_part cursor for
select *
from #ttsku

open c_part

fetch next from c_part

while @@fetch_status = 0
begin

if exists (select *
from sku
where sku.part = #ttsku.part)
BEGIN
update sku
set sku.Desc1 = #ttsku.desc1,
sku.CustDesc = ##ttsku.custdesc
where sku.part = #ttsku.part
END
ELSE BEGIN
insert into sku (sku.part, sku.desc1)
select #ttsku.part, #ttsku.desc1
END

fetch next from c_part

END -- while

close c_part
deallocate c_part

DELETE FROM sku
WHERE not exists (select *
from #ttsku
where #ttsku.part = sku.part)

drop table #ttsku

Thanks again for any help.

Scott

Kristen
Test

22859 Posts

Posted - 2005-12-02 : 12:48:01

"dbo.udf_GetEntry(cd_det.cd_cmmt,1,';')"

Normalise cd_det.cd_cmmt into separate columns (in an in-parallel table, if necessary)

"SELECT
...
INTO #ttsku

Pre-create #ttsku, and create a PK on it. This will improve performance.

"FROM mfgprod..pub.pt_mstr"

How does that "double dot" work then?

"and (pt_status = 'A' or pt_status = 'AMTO')"

Maybe change that to

AND pt_status IN ('A', 'AMTO')

but I don't suppose it will make any difference to the optimiser.

"select *
from #ttsku"

Always name the columns, never use "SELECT *"

"fetch next from c_part"

You need some @Variables for the various columns in the SELECT defined for the cursor, don't you?

I reckon you are doing the CURSOR wrongly altogether, but what it appears you want to do doesn't need a CURSOR - and they are slow and horrible things anyway! Won't this do?:


update U
set sku.Desc1 = #ttsku.desc1, 
    sku.CustDesc = ##ttsku.custdesc  -- Do you mean double-# ??
FROM dbo.sku AS U
where sku.part = #ttsku.part

insert into sku (sku.part, sku.desc1)
select #ttsku.part, #ttsku.desc1

FROM #ttsku AS T
     LEFT OUTER JOIN dbo.sku AS S
         ON S.part = T.part
WHERE S.part IS NULL -- Non-existing parts only

"sku.CustDesc = ##ttsku.custdesc"

That double-# looks wrong

"if exists (select *
from dbo.sku
where sku.part = #ttsku.part)
...
BEGIN
update dbo.sku
...
insert into dbo.sku
...
DELETE FROM dbo.sku"

Always specify the table owner - otherwise the optimiser is unlikely to cache the query plan.

"DELETE FROM sku
WHERE not exists (select *
from #ttsku
where #ttsku.part = sku.part)"

I would do this with an OUTER JOIN, but its as broad as its long - so only a question of style:


DELETE D
FROM dbo.sku AS D
     LEFT OUTER JOIN #ttsku AS T
         ON T.part = D.part
WHERE T.part IS NULL -- Only parts that do not exist in #ttsku

Kristen

sbhegel
Starting Member

19 Posts

Posted - 2005-12-02 : 13:59:44

Kristen,

Thanks for the advice, I am just confused on 1 thing.

If I use the following statement

update U
set sku.Desc1 = #ttsku.desc1,
sku.CustDesc = ##ttsku.custdesc -- Do you mean double-# ??
FROM dbo.sku AS U
where sku.part = #ttsku.part

Don't I somehow need to get the #ttsku.part record that corresponds to the sku record?

Also, in the first bit of code I posted, why is the #ttsku.part record not found with the insert into? It is available for the update?

I guess the real question is, from a select statement, how can I read through each record and then perform actions on that record if conditions are met?

As for your question
"How does that "double dot" work then?"
I really have no idea. The database is a linked server so I just put in the db name and some dots until I got the information!

Thanks again for your comments,
Scott

Kristen
Test

22859 Posts

Posted - 2005-12-03 : 02:38:30

"Don't I somehow need to get the #ttsku.part record that corresponds to the sku record?"

Its in the WHERE clause - so SKU is updated where the "part" column in "sku" table matches the "part" column in "#ttsku" table

"Also, in the first bit of code I posted, why is the #ttsku.part record not found with the insert into? It is available for the update?"

Because:

if exists (select *
from sku
where sku.part = #ttsku.part)

is always true - and that is because the syntax for your CURSOR is wrong.

"I guess the real question is, from a select statement, how can I read through each record and then perform actions on that record if conditions are met?"

And the real answer! is that you don't do ti that way, you process the data as a SET if you possibly can, not by stepping through each record.

""How does that "double dot" work then?"
I really have no idea. The database is a linked server so I just put in the db name and some dots until I got the information!"

Ah, OK. You need:

RemoteServerName.RemoteDatabaseName.dbo.TableName

You can leave out the "dbo" bit, but you can't leave out the database name, so I'm not sure why

mfgprod..pub.pt_mstr

isn't treated as a syntax error ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources