Down to the wire.....

I need to expand off this query. Instead of data being imported from
cr_stating into cr. I need to import data from excel into cr_staging. Can
someone give me a good example please''?
UPDATE cr
SET cr.location = crs.location
FROM cr
JOIN cr_staging crs ON cr.first_name = crs.first_name
AND cr.last_name = crs.last_name
AND cr.ssn = crs.ssn
INSERT cr (first_name,
last_name,
ssn,
location)
SELECT crs.first_name,
crs.last_name,
crs.ssn,
crs.location
FROM cr_staging crs
WHERE NOT EXISTS (SELECT *
FROM cr
WHERE cr.first_name = crs.first_name
AND cr.last_name = crs.last_name
AND cr.ssn = crs.ssn)
Thanks...Eric,
This article should help you: http://support.microsoft.com/?id=321686
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
> I need to expand off this query. Instead of data being imported from
> cr_stating into cr. I need to import data from excel into cr_staging. Can
> someone give me a good example please''?
> UPDATE cr
> SET cr.location = crs.location
> FROM cr
> JOIN cr_staging crs ON cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn
> INSERT cr (first_name,
> last_name,
> ssn,
> location)
> SELECT crs.first_name,
> crs.last_name,
> crs.ssn,
> crs.location
> FROM cr_staging crs
> WHERE NOT EXISTS (SELECT *
> FROM cr
> WHERE cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn)
> Thanks...
>|||Eric
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
> I need to expand off this query. Instead of data being imported from
> cr_stating into cr. I need to import data from excel into cr_staging. Can
> someone give me a good example please''?
> UPDATE cr
> SET cr.location = crs.location
> FROM cr
> JOIN cr_staging crs ON cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn
> INSERT cr (first_name,
> last_name,
> ssn,
> location)
> SELECT crs.first_name,
> crs.last_name,
> crs.ssn,
> crs.location
> FROM cr_staging crs
> WHERE NOT EXISTS (SELECT *
> FROM cr
> WHERE cr.first_name = crs.first_name
> AND cr.last_name = crs.last_name
> AND cr.ssn = crs.ssn)
> Thanks...
>|||URI,
I think you gave me that before but I was getting an error when I used that
query. Will that update and insert records per my requirements?
Thanls
"Uri Dimant" wrote:
> Eric
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="c:\MyExcel.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
>
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
> > I need to expand off this query. Instead of data being imported from
> > cr_stating into cr. I need to import data from excel into cr_staging. Can
> > someone give me a good example please''?
> >
> > UPDATE cr
> > SET cr.location = crs.location
> > FROM cr
> > JOIN cr_staging crs ON cr.first_name = crs.first_name
> > AND cr.last_name = crs.last_name
> > AND cr.ssn = crs.ssn
> >
> > INSERT cr (first_name,
> > last_name,
> > ssn,
> > location)
> > SELECT crs.first_name,
> > crs.last_name,
> > crs.ssn,
> > crs.location
> > FROM cr_staging crs
> > WHERE NOT EXISTS (SELECT *
> > FROM cr
> > WHERE cr.first_name = crs.first_name
> > AND cr.last_name = crs.last_name
> > AND cr.ssn = crs.ssn)
> >
> > Thanks...
> >
>
>|||Eric
It does a simple SELECT statement from EXCEL file
INSERT INTO Table SELECT statement and the you can update it.
If I missunderstand something let me know.
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4245938B-E84F-4397-AAD8-1A415CC02BA6@.microsoft.com...
> URI,
> I think you gave me that before but I was getting an error when I used
that
> query. Will that update and insert records per my requirements?
> Thanls
> "Uri Dimant" wrote:
> > Eric
> > SELECT *
> > FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> > 'Data Source="c:\MyExcel.xls";
> > User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
> >
> >
> >
> > "Eric" <Eric@.discussions.microsoft.com> wrote in message
> > news:6C855B64-751D-4137-8B29-1932BE87D673@.microsoft.com...
> > > I need to expand off this query. Instead of data being imported from
> > > cr_stating into cr. I need to import data from excel into cr_staging.
Can
> > > someone give me a good example please''?
> > >
> > > UPDATE cr
> > > SET cr.location = crs.location
> > > FROM cr
> > > JOIN cr_staging crs ON cr.first_name = crs.first_name
> > > AND cr.last_name = crs.last_name
> > > AND cr.ssn = crs.ssn
> > >
> > > INSERT cr (first_name,
> > > last_name,
> > > ssn,
> > > location)
> > > SELECT crs.first_name,
> > > crs.last_name,
> > > crs.ssn,
> > > crs.location
> > > FROM cr_staging crs
> > > WHERE NOT EXISTS (SELECT *
> > > FROM cr
> > > WHERE cr.first_name = crs.first_name
> > > AND cr.last_name = crs.last_name
> > > AND cr.ssn = crs.ssn)
> > >
> > > Thanks...
> > >
> >
> >
> >|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:4245938B-E84F-4397-AAD8-1A415CC02BA6@.microsoft.com...
> I think you gave me that before but I was getting an error when I used
that
> query. Will that update and insert records per my requirements?
Eric,
You need to read the article I posted the link to. Read the examples on
how to create a linked server pointing to your spreadsheet, then replace
your 'cr_staging' table in the query with the linked server.