Help! Last Time

I have posted other comments about this issue and this will be the last time
.
I have got 20 diffrent ways to do this but none of them work for me.
Here is what I want to do
I have an excel spread sheet with say colums first name, last name, ssn,
location. I want to import this into a table called cr_staging. Now based on
the first name, last name, and ssn I need it to either update the cr_staging
table if a record is found or insert a new record if it is not found.
I have tried creating a dts pakage and that doesn't seem to work(maybe im
doing it wrong). I can get it to import the data into the cr_staging table
but without the logic behind it. It just dumps all the records even if they
exists.
Could someone please give me a walkthough on how to do this.
Or if using a query would be better we could go that router.
I uregently need to get this solidified and your help is GREATLY appreciated
.
Thanks,
you can email me also at eric.lovelace@.saralee.comWhy don't you use DTS to import the data into a table, into the same
database as the cr_staging table, then do your data modifications?
http://www.aspfaq.com/
(Reverse address to reply.)
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:97704051-16DC-4459-A47F-7ED353A970D2@.microsoft.com...
> I have posted other comments about this issue and this will be the last
time.
> I have got 20 diffrent ways to do this but none of them work for me.
> Here is what I want to do
> I have an excel spread sheet with say colums first name, last name, ssn,
> location. I want to import this into a table called cr_staging. Now based
on
> the first name, last name, and ssn I need it to either update the
cr_staging
> table if a record is found or insert a new record if it is not found.
> I have tried creating a dts pakage and that doesn't seem to work(maybe im
> doing it wrong). I can get it to import the data into the cr_staging table
> but without the logic behind it. It just dumps all the records even if
they
> exists.
> Could someone please give me a walkthough on how to do this.
> Or if using a query would be better we could go that router.
> I uregently need to get this solidified and your help is GREATLY
appreciated.
>
> Thanks,
> you can email me also at eric.lovelace@.saralee.com
>
>|||lol 1 large choc cake is in the mail ;)
Yeah I got how to import data that way and it works. But that imports all
the data into the table. I need to take that a step further and say if there
is a record with the first name last name and ssn then update the record els
e
insert a new one. We have a spread sheet with about 40000 users in it and
would like some automation to it.
Thanks
"Peter The Spate" wrote:

> Here are the steps to load it into a SQL Server.
> 1. Open up EA
> 2. Select the database
> 3. Right Click and select import data
> 4. Click Next
> 5. Under Data Sources select Excel, remember to put in the
> excel version.
> 6. Put in the file name of the spreadsheet including
> diretory path
> 7. Click Next
> 8. In 'Choose Destination' leave the 'Destination' to the
> one defaulted
> 9. Choose the server the spreadsheet is going to
> 10 Choose the database the spreadsheet is going to
> 11 Click Next
> 12 Select 'Copy tables(s) and view(s)...'
> 13 click Next
> 14 Now under source click which ever sheet the data
> resides.
> 15 Choose a destination table, if there isn't one then it
> will automatically make one with the name printed
> under 'Destination'
> 16 Press Next
> 17 Select Run immediatly
> 18 Click Next
> 19 Click finish.
> If you have any problems then email me at
> peternolan67REMOVETHIS@.hotmail.com
> And if you work for Sara Lee the cake company, my fee is a
> large chocolate cake ;)
> Peter
>
> will be the last time.
> work for me.
> last name, ssn,
> cr_staging. Now based on
> update the cr_staging
> is not found.
> to work(maybe im
> cr_staging table
> records even if they
> this.
> router.
> GREATLY appreciated.
>|||I would sugest you do what Aaron said, get it in a
temporary, perform the changes then delete the temp table.
There are ways that you can do it but they are a bit
fiddly.
Using the method shown you can actually put in SQL if the
data you want in, but even then it will only import data,
not update data.
The other way is the OPENSOURCE command which I only touch
if I really need to.
Peter

>--Original Message--
>lol 1 large choc cake is in the mail ;)
>Yeah I got how to import data that way and it works. But
that imports all
>the data into the table. I need to take that a step
further and say if there
>is a record with the first name last name and ssn then
update the record else
>insert a new one. We have a spread sheet with about 40000
users in it and
>would like some automation to it.
>Thanks
>"Peter The Spate" wrote:
>
the[vbcol=seagreen]
the[vbcol=seagreen]
it[vbcol=seagreen]
is a[vbcol=seagreen]
them[vbcol=seagreen]
name,[vbcol=seagreen]
it[vbcol=seagreen]
seem[vbcol=seagreen]
the[vbcol=seagreen]
is[vbcol=seagreen]
>.
>|||Aaron are you talking about importing all of the records into the cr_staging
every day? If I do that it will keep the existing 40000 records and then
insert another 40000. So now I have 80000 after two days. This table will be
hudge.
Maybe I am looking at this the wrong way. Is there another way to get the
required results?
Thanks
"Aaron [SQL Server MVP]" wrote:

> Why don't you use DTS to import the data into a table, into the same
> database as the cr_staging table, then do your data modifications?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:97704051-16DC-4459-A47F-7ED353A970D2@.microsoft.com...
> time.
> on
> cr_staging
> they
> appreciated.
>
>|||Your *outsourcing* your problem to the ng.The ng is
the utlimate form of this - not even cheap but *free*!
Outsource your problem for real, it will probably cost
less than a pound cake.|||No, import it into a work table, perform your updates/inserts against the
real table within SQL Server (instead of through DTS), and then
drop/truncate the work table.
http://www.aspfaq.com/
(Reverse address to reply.)
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C1457E64-D02A-4FFA-8F65-E5B2A58A96DE@.microsoft.com...
> Aaron are you talking about importing all of the records into the
cr_staging
> every day? If I do that it will keep the existing 40000 records and then
> insert another 40000. So now I have 80000 after two days. This table will
be[vbcol=seagreen]
> hudge.
> Maybe I am looking at this the wrong way. Is there another way to get the
> required results?
>
> Thanks
> "Aaron [SQL Server MVP]" wrote:
>
last[vbcol=seagreen]
ssn,[vbcol=seagreen]
based[vbcol=seagreen]
im[vbcol=seagreen]
table[vbcol=seagreen]|||Problem Solved. This is how I did it for reference..Thanks to all who helped
--Create Temp Table
create table cr_staging_tmp(
Company varchar(100),
Employee varchar(100),
Last_Name varchar(100),
First_Name varchar(100),
Middle_Name varchar(100),
Fica_Nbr varchar(100),
Work_Sts varchar(100),
Work_Sts_Date varchar(100),
Bus_Unit_Desc varchar(100),
Location_Desc varchar(100),
type varchar(100),
location varchar(100),
manager varchar(100),
manager_phone varchar(100))
--insert into TempTable
insert into cr_staging_tmp
SELECT Company,
Employee,
Last_Name,
First_Name,
Middle_Name,
Fica_Nbr,
Work_Sts,
Work_Sts_Date,
Bus_Unit_Desc,
Location_Desc,
type,
location,
manager,
manager_phone
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
8.0')...Sheet1$
UPDATE cr.dbo.cr_staging
SET location = crs.location, Work_sts = crs.Work_sts, fica_nbr =
crs.Fica_Nbr, employee = crs.employee, Work_sts_date = crs.Work_sts_date,
type = crs.type, Bus_Unit_Desc = crs.Bus_Unit_Desc, Location_Desc =
crs.Location_Desc, manager = crs.manager, manager_phone = crs.manager_phone
FROM cr_staging
JOIN cr_staging_tmp crs ON cr_staging.first_name = crs.first_name
AND cr_staging.last_name = crs.last_name
AND cr_staging.fica_nbr = crs.Fica_Nbr
AND cr_staging.location = crs.location
INSERT cr_staging (first_name,
last_name,
Fica_Nbr,
location,
Work_sts,
Employee,
Work_sts_date,
Type,
Bus_Unit_Desc,
Location_Desc,
Manager,
manager_phone)
SELECT crs.first_name,
crs.last_name,
crs.Fica_Nbr,
crs.location,
crs.Work_sts,
crs.Employee,
crs.Work_sts_date,
crs.Type,
crs.Bus_Unit_Desc,
crs.Location_Desc,
crs.Manager,
crs.manager_phone
FROM cr_staging_tmp crs
WHERE NOT EXISTS (SELECT *
FROM cr_staging
WHERE cr_staging.first_name = crs.first_name
AND cr_staging.last_name = crs.last_name
AND cr_staging.Fica_Nbr = crs.Fica_Nbr
AND cr_staging.location = crs.location)
--Clean up
drop table cr_staging_tmp
"Aaron [SQL Server MVP]" wrote:

> No, import it into a work table, perform your updates/inserts against the
> real table within SQL Server (instead of through DTS), and then
> drop/truncate the work table.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C1457E64-D02A-4FFA-8F65-E5B2A58A96DE@.microsoft.com...
> cr_staging
> be
> last
> ssn,
> based
> im
> table
>
>