Append new Cust. based on some values from existin

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)

Append new Cust. based on some values from existin

Author

Topic

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-08-08 : 19:03:36

Hello, I need to write a store procedure to append records to our customer table. Here is a sample of my table below>
Customer char(7)
Name char(30)
Salesperson char(3)
PriceCode char(2)
CustomerClass char(2)
Branch char(2)
TermsCode char(2)
Area char(2)
Telephone char(20)
Contact char(40)
DateCustAdded datetime
CustomerOnHold char(1)

I will have the following parameters provided
NewCustomer
ParentCustomer (will be listed in the existing CustomerTable under Customer field)
Name
Telephone
CustomerOnHold = Y
DateCustomerAdded = CurrentDate
TermsCode = P

In order to insert a new record I would need to query the current CustomerTable where ParentCustomer = Customer
From the existing customer I would need to copy the following fields that would be needed in order to append a newCustomer
Salesperson
PriceCode
CustomerClass
Area
Branch.

Please let me know if you could be any help

thanks.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-10 : 08:02:15

What have you tried so far?

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-08-10 : 16:00:54

What I need is an example on how to append a record from one table (need to search for this row first) into another table. Most of the columns will be the same, however some fields will have to be substituted with parameters.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-10 : 18:47:26

quote:
Originally posted by Patyk

What I need is an example on how to append a record from one table (need to search for this row first) into another table. Most of the columns will be the same, however some fields will have to be substituted with parameters.


insert into target (col1, col2, col3)
select col1, col2, col3 from source

What do you mean by "first row"? Since SQL returns sets, there is no guarantee of any order unless you add an ORDER BY clause to the SELECT. If you do, what do you want to ORDER on?

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-08-11 : 11:52:26

yes that would be correct if I would need to copy the whole row. Some columns i need to substitute with parameter. For example column1 and column2 would be a parameter everything else would be copied from the other table.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-11 : 12:45:26

quote:
Originally posted by Patyk

yes that would be correct if I would need to copy the whole row. Some columns i need to substitute with parameter. For example column1 and column2 would be a parameter everything else would be copied from the other table.

insert into target (col1, col2, col3)
select col1, @parm1, @parm2 from source

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-08-11 : 14:47:29

I need to append a record from the same table based on provided parameter. The new record will have a new value for Customer field(provided by parameter), rest of the record will need to be copied from the row where customer = parent_customer_code

USE [companyT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE dbo.appendSXcustomer
@parent_customer_code char (7) ,
@sx_customer_code char (7)

AS
BEGIN
SET NOCOUNT ON
set @sx_customer_code = 'NewCust'

INSERT INTO dbo.ArCustomer (customer,Salesperson,CustomerClass,Branch,TermsCode,Area )
SELECT (@sx_customer_code,salesperson,customerclass,branch,termscode,area from dbo.ArCustomer where customer like @parent_customer_code)

END

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-11 : 18:10:37

OK -- does that work? If not, what do you want it to do differently

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-08-11 : 18:22:23

It does not work still I have some errors. What is the other way of doing it?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-11 : 20:09:01

INSERT INTO dbo.ArCustomer (customer,Salesperson,CustomerClass,Branch,TermsCode,Area )
SELECT @sx_customer_code,salesperson,customerclass,branch,termscode,area
from dbo.ArCustomer
where customer like '%' + @parent_customer_code + '%'

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

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-08-12 : 13:33:44

Now I have the following sp. Still won't work or compile I am getting an error Msg 102, Level 15, State 1, Procedure insert_sx_customer, Line 25
Incorrect syntax near ','.
the procedure is as follows.

USE [companyT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[insert_sx_customer]

@sx_customer_code char(10),
@parent_customer_code char(10),
@email_address char(50),
@name char(30),
@telephone char(20)

as

BEGIN

SET NOCOUNT ON;

declare @date as datetime
set @date = getdate()
set @parent_customer_code= 'BC0001'
set @sx_customer_code= 'SX0001'
set @email_address = 'test@hotmail.com'
set @telephone = '001-123-4567'
set @name = 'Alex Smith'

INSERT INTO dbo.ArCustomer (Customer,[Name], Salesperson,CustomerClass,Branch,TermsCode,Area,CustomerOnHold,SoDefaultType,Email,Rating,Contact,DateCustAdded)
SELECT (@parent_customer_code,@name, Salesperson,CustomerClass,Branch,"P",Area,"Y","B",@email_address,"HOLD",@name,@date)
from dbo.ArCustomer
where customer like '%' + @parent_customer_code + '%'

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-12 : 13:39:44

Remove the parenthesis for the SELECT. Please see my last reply for how the INSERT/SELECT should look.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-12 : 13:40:31

I'm confused why you are setting values in the body of the stored procedure for the input parameters. If they are just test values, then set the test values when you execute the stored procedure, not by changing the code of the stored procedure.

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

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-08-12 : 14:04:20

OK Thanks it executes fine, no errors, however no new records are being appened. It needs to find the existing record where customer like '%' + @parent_customer_code + '% and append a new one that same values with changes. Customer will be substituted with new Customer (@sx_customer_code) etc.... I am not sure why no new records are not appended.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-12 : 14:09:11

Does this return the row you want to duplicate?

SELECT *
from dbo.ArCustomer
where customer like '%BC0001%'

Don't you need to change @parent_customer_code to @sx_customer_code in the SELECT portion?

SELECT @sx_customer_code,@name, Salesperson,CustomerClass,Branch,"P",Area,"Y","B",@email_address,"HOLD",@name,@date
from dbo.ArCustomer
where customer like '%' + @parent_customer_code + '%'

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

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-08-12 : 14:34:30

Still won't work does not append any records, parentc_customer_code exits in the ARCustomer database

USE [companyT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[insert_sx_customer]

@sx_customer_code char(7),
@parent_customer_code char(7),
@email_address char(50),
@name char(30),
@telephone char(20)

as

BEGIN

SET NOCOUNT ON;

declare @date as datetime
set @date= getdate()
set @parent_customer_code = '2ND01A'
set @sx_customer_code = 'SX0001'
set @email_address = 'test@hotmail.com'
set @telephone = '001-123-4567'
set @name= 'Alex Smith'

INSERT INTO dbo.ArCustomer (Customer,[Name], Salesperson,CustomerClass,Branch,TermsCode,Area,CustomerOnHold,SoDefaultType,Email,UserField1,Contact,DateCustAdded)
SELECT @sx_customer_code,@name, Salesperson,CustomerClass,Branch,'P',Area,'Y','B',@email_address,'HOLD',@name,@date
from dbo.ArCustomer
where Customer like '%' + @parent_customer_code + '%'

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-12 : 14:40:45

Please see my last reply. Need the first question answered.

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-12 : 14:41:03

By the way, for faster help, you should post your question like this: http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-08-12 : 14:52:08

Thank you I will try ... just back to this on here is my error

USE [companyT]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[insert_sx_customer]

SELECT 'Return Value' = @return_value

GO

Msg 201, Level 16, State 4, Procedure insert_sx_customer, Line 0
Procedure or function 'insert_sx_customer' expects parameter '@sx_customer_code', which was not supplied.

(1 row(s) affected)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-12 : 14:54:43

You need to pass values for the input parameters since they don't have default values. Remove the SET commands from your stored procedure for the input parameters.

EXEC [dbo].[insert_sx_customer] @sx_customer_code = 'somevalue', @parent_customer_code = 'some other value', .......

Remove these from the body of the stored procedure:
set @parent_customer_code = '2ND01A'
set @sx_customer_code = 'SX0001'
set @email_address = 'test@hotmail.com'
set @telephone = '001-123-4567'
set @name= 'Alex Smith'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources