Need help with the code - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with the code

Author

Topic

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-03-03 : 13:57:15

I have the following code to import .csv file into my table in excel
It's being inserted into a table. dbo.ImportedPromoPricing.
Table and the .csv file have 3 fields price, code and selling price.
Once import is completed I want to use the data in my dbo.ImportedPromoPricing to update another table dbo.MasterPricing. Records need to be compared and updated or appended if needed. in case of update only price will be updated. this is the beginning of my code

USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[ImportPromoPricing] Script Date: 03/03/2014 14:04:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[ImportPromoPricing] as
begin
truncate table dbo.ImportedPromoPricing

Bulk Insert dbo.ImportedPromoPricing
from 'D:\Reporting\DB\Sales\PromoPriceImport\PromoPriceImport.csv'
with
(FIELDTERMINATOR =',',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)

DELETE FROM dbo.ImportedPromoPricing
where SellingPrice IS NULL

end

USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[UpdatePromoPricing] Script Date: 03/03/2014 13:34:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UpdatePromoPricing]
@StockCode char(30),
@PriceCode char(2),
@SellingPrice decimal(15,5)
as
begin

SET NOCOUNT ON;

begin
IF NOT EXISTS (SELECT * FROM dbo.ImportedPromoPricing WHERE StockCode = @StockCode and PriceCode = @PriceCode)
BEGIN
insert into [dbo].[tmpinvpricetable] ([StockCode],[PriceCode],[SellingPrice])
values (@StockCode, @PriceCode, @SellingPrice);
END
ELSE
BEGIN
update dbo.tmpinvpricetable
set SellingPrice = @SellingPrice
WHERE StockCode = @StockCode and PriceCode = @PriceCode;
END

end
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-03 : 14:06:28

Show us how you are executing the stored procedure. It is your execute that's the problem, not the stored procedure.

EXEC UpdatePromoPricing 'value111111111111', 'v2', 15.54

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-03-03 : 14:10:34

I just right click / Execute Stored Procedure ? I am able to do this with all the other ones....?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-03 : 14:11:42

Do it through an EXEC command. Perhaps the GUI has a bug. By the way, in case it is a bug, you should try installing the latest SQL service pack plus cumulative update. MS includes bug fixes for client tools in those updates.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-03-03 : 14:57:45

A nicer and cleaner way to achieve what you are doing is to start using the MERGE command.

ALTER PROCEDURE dbo.UpdatePromoPricing
(
	@StockCode CHAR(30),
	@PriceCode CHAR(2),
	@SellingPrice DECIMAL(15, 5)
)
AS

SET NOCOUNT ON;

MERGE	dbo.tmpInvPriceTable AS tgt
USING	(
		SELECT	@StockCode AS StockCode,
			@PriceCode AS PriceCode,
			@SellingPrice AS SellingPrice
	) AS src ON src.StockCode = tgt.StockCode
		AND src.PriceCode = tgt.PriceCode
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.SellingPrice = src.SellingPrice
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					StockCode,
					PriceCode,
					SellingPrice
				)
			VALUES	(
					src.StockCode,
					src.PriceCode,
					src.SellingPrice
				);

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-03-03 : 15:30:03

Thanks however I am receiving error while running it.
Msg 102, Level 15, State 1, Procedure UpdatePromoPricing1, Line 16
Incorrect syntax near 'MERGE'.
Msg 156, Level 15, State 1, Procedure UpdatePromoPricing1, Line 21
Incorrect syntax near the keyword 'AS'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-03 : 15:43:48

MERGE is not supported in SQL Server 2005. You'll need to use your previous version and execute it via EXEC like I mentioned and showed an example.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-03-03 : 16:41:46

I think I have made a mistake creating this procedure. I am not supplying the parameters the right way.
I need to open ImportedPromoPricingTable and for each record in this table I need to compare with each record in my tmpinvpricetable (which is the destination table) if exist update the selling price if not append the whole line. I need some kind of LOOP code?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-03 : 18:01:52

Please update your original post to include the original stored procedure. We don't need to see ImportedPromoPricingTable stored procedure.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-03-03 : 18:14:33

I did however my second store procedure won't work since I am not providing the parameters the whole import / update or append needs to be reconfigured and redone.

The first sp works fine, this is why i think the other tasks could be just appended to it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-03 : 18:39:10

Try this:

insert into tmpinvpricetable] ([StockCode],[PriceCode],[SellingPrice])
select [StockCode],[PriceCode],[SellingPrice]
from ImportedPromoPricing i
left join tmpinvpricetable t on i.stockcode = t.stockcode
where t.stockcode is null

update t
set SellingPrice = i.SellingPrice
from ImportedPromoPricing i
left join tmpinvpricetable t on i.stockcode = t.stockcode
where t.stockcode is null

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-03-03 : 18:56:41

Tara.....

I will only need to insert if the record does not exist in my tmpinvpricetable
link is stockcode = stockcode and pricecode = pricecode

I will need to update (price only) if record found.

What does it mean in your code where t.stockcode is null?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-03 : 19:06:24

Just change the joins to include pricecode.

The t.stockcode is due to using a left join. A left join returns all rows from the first table (left table) but only the matches in the second table (right table). For the rows where there isn't a match, the columns will return as null.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-03 : 19:07:11

Run just the select to see what I mean regarding the left join/null stuff. Remove the where clause, add it back, change it to IS NOT NULL. Note the difference in each.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-03-03 : 19:50:43

I am getting the error while running the store procedure. Stock Code and Price Code are the primary Codes?

Msg 515, Level 16, State 2, Procedure testupdate, Line 3
Cannot insert the value NULL into column 'StockCode', table 'Reporting.dbo.tmpinvpricetable'; column does not allow nulls. INSERT fails.
Null not null ?

USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[testupdate] Script Date: 03/03/2014 16:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testupdate] as

insert into dbo.tmpinvpricetable ([tmpinvpricetable].[StockCode],[tmpinvpricetable].[PriceCode],[tmpinvpricetable].[SellingPrice])

select [tmpinvpricetable].[StockCode],[tmpinvpricetable].[PriceCode],[tmpinvpricetable].[SellingPrice]

from ImportedPromoPricing
left join tmpinvpricetable on ImportedPromoPricing.StockCode = tmpinvpricetable.StockCode and ImportedPromoPricing.PriceCode = tmpinvpricetable.PriceCode
where ImportedPromoPricing.StockCode is not null

update tmpinvpricetable
set SellingPrice = ImportedPromoPricing.SellingPrice
from ImportedPromoPricing
left join tmpinvpricetable on ImportedPromoPricing.StockCode = tmpinvpricetable.StockCode and ImportedPromoPricing.PriceCode = tmpinvpricetable.PriceCode
where ImportedPromoPricing.StockCode is not null

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-04 : 11:02:12

USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[testupdate] Script Date: 03/03/2014 16:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testupdate] as

insert into dbo.tmpinvpricetable ([tmpinvpricetable].[StockCode],[tmpinvpricetable].[PriceCode],[tmpinvpricetable].[SellingPrice])

select [tmpinvpricetable].[StockCode],[tmpinvpricetable].[PriceCode],[tmpinvpricetable].[SellingPrice]

from ImportedPromoPricing
left join tmpinvpricetable on ImportedPromoPricing.StockCode = tmpinvpricetable.StockCode and ImportedPromoPricing.PriceCode = tmpinvpricetable.PriceCode
where tmpinvpricetable.StockCode is null

update tmpinvpricetable
set SellingPrice = ImportedPromoPricing.SellingPrice
from ImportedPromoPricing
left join tmpinvpricetable on ImportedPromoPricing.StockCode = tmpinvpricetable.StockCode and ImportedPromoPricing.PriceCode = tmpinvpricetable.PriceCode
where tmpinvpricetable.StockCode is not null

You may need to also add tmpinvpricetable.PriceCode to the WHERE clauses.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-03-06 : 12:10:25

Still not working I am receiving an error message:
Msg 515, Level 16, State 2, Procedure testupdate, Line 3
Cannot insert the value NULL into column 'StockCode', table 'Reporting.dbo.tmpinvpricetable'; column does not allow nulls. INSERT fails.
The statement has been terminated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-06 : 12:27:40

Sorry I missed that you had added the tmp table to the select. It should be the import table instead.

USE [Reporting]
GO
/****** Object: StoredProcedure [dbo].[testupdate] Script Date: 03/03/2014 16:49:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testupdate] as

insert into dbo.tmpinvpricetable ([tmpinvpricetable].[StockCode],[tmpinvpricetable].[PriceCode],[tmpinvpricetable].[SellingPrice])
select ImportedPromoPricing.[StockCode],ImportedPromoPricing.[PriceCode],ImportedPromoPricing.[SellingPrice]
from ImportedPromoPricing
left join tmpinvpricetable on ImportedPromoPricing.StockCode = tmpinvpricetable.StockCode and ImportedPromoPricing.PriceCode = tmpinvpricetable.PriceCode
where tmpinvpricetable.StockCode is null

update tmpinvpricetable
set SellingPrice = ImportedPromoPricing.SellingPrice
from ImportedPromoPricing
left join tmpinvpricetable on ImportedPromoPricing.StockCode = tmpinvpricetable.StockCode and ImportedPromoPricing.PriceCode = tmpinvpricetable.PriceCode
where tmpinvpricetable.StockCode is not null

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-03-07 : 12:45:02

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources