SQL, Access, Excel Append - 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 2000 Forums

SQL Server Development (2000)

SQL, Access, Excel Append

Author

Topic

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-08-29 : 04:56:19

Hi,

Thanks for reading.

I ran a Query for my sales team as they wanted to do a mailshot to various types of customers. I have that list in excel format. Now we record correspondance so what I need to do is for each customer that I selected, add a correspondace record to each.

The values of the correspondance to insert do not change. What does change is that customer that it is being added to.

I started to write this as a proc but it started to get messy once I introduced the cursor.

I was wondering is their an easier way to add these static values to many customers, based on this excel list, through access? I notice once you link a spreadsheet you can treat it as a table.

Please let me know if im not making sense,

Thanks,

when life hands you lemons, ask for tequila and salt

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-30 : 03:39:29

What you are looking to do sounds very simple to achieve w/o a cursor, the only issue is I am having some difficulty figuring out what you are looking to do. Can you please describe the whole process.

i.e.
1. First I query the customer database to excel
2. For each record in excel I want to insert a record into the corresponance table
etc.

Please tell me the result you are looking for in the correspondance table. You don't need to use excel or cursors to achieve this, all you need to do is write the query to join the customer table to the insert. If you clearify what you are looking for we can resolve your issue fairly quickly.

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-08-30 : 06:57:32

Hi thanks for the reply...

Well to be honest I could cut excel out all together and re-run the select query used to populate excel.

So...
1) SELECT CustomerContacts(Table) that meet criteria held in the Customers(Table) 2 tables...
2) Using the customerContactID, insert into the Correspondance(Table) certain values that do not change.

So to get at customer X I have to select this contact but I need to join the customer table itself as the customer must be a certain type... then I have pre-set correspondance values that I want to add to each customerContact based on what I chose.

Is it starting to take shape?

when life hands you lemons, ask for tequila and salt

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-08-31 : 04:55:43

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[stp_MailshotCustomerUpdate]
as

/* declare customer id variable */
declare @customerID int

/* declare correspondance variables */
declare @ID int
declare @Code nvarchar (20)
declare @Date smalldatetime
declare @type nvarchar (20)
declare @Contact nvarchar (30)
declare @Summary nvarchar (100)
declare @Content nvarchar (200)
declare @Reply smalldatetime
declare @Location nvarchar (50)
declare @Hyperlink nvarchar (200)
declare @JobNo nvarchar (10) --[Job No]
declare @QuoteNo nvarchar (12) --[Quote No]
declare @EsbNo nvarchar (10) --[ESB No]
declare @Project nvarchar (50)
declare @Publication nvarchar (50)
declare @PurchaseOrderNo nvarchar (50)
declare @FollowUp smalldatetime --[Next Follow Up]
declare @SalesPerson nvarchar (4)
declare @ContactID int
declare @CustomerRef nvarchar (50)
declare @dummy1 nvarchar (50)
declare @dummy2 nvarchar (50)

--Give the correspondance variables their values...
SELECT
@ID = Custcon.ID
@Code = Custcon.Code
FROM
Custcon
JOIN
Customer
ON Custcon.CustomerID = Customer.CustconID

-- declare customerCursor! Uh oh here we go...

declare customerCursor Cursor for
SELECT
Custcon.CustomerID
FROM
Custcon
-- in this bit u need to put your query but only select the customerID field for it... ?????
-- I think thats right...

--open the cursor
open customerCursor

--fetch the first unlucky person to get all the spam...
fetch next from customerCursor into @customerID

--no loop around the customer, adding values to correspondance table for each one (we hope)
while (@@fetch_status = 0)
begin
INSERT
Correspondance
VALUES
Code = @Code -- Ecsetera!
WHERE
CustomerID = @customerID
--fetch the next cust
fetch next customer from customerCursor into @customerID
end
--clean up
close customerCursor
deallocate customerCursor

GO

Thats how far I got...

when life hands you lemons, ask for tequila and salt

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-09-07 : 06:07:25

Sorry to bump this, its just I have code now and I know how much you love it ;-p

when life hands you lemons, ask for tequila and salt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources