Bulk Insert from Excel [Programming SQL]

From: Steve Kass on 29 Jul 2006 16:52

If the Excel spreadsheet only has one row, and you are correctly
seeing ProjectoID as the column name with the OPENROWSET
statement, it could be like this:

SELECT ProjectoID
FROM dbo.ProjectoItems_tbl
WHERE ProjectoID = (
SELECT ProjectoID
FROM OPENROWSET(... <same as before> )
)

If there is more than one row in the Excel "table", you will
have to identify it further with a WHERE clause based on
other columns:

SELECT ProjectoID
FROM dbo.ProjectoItems_tbl
WHERE ProjectoID = (
SELECT ProjectoID
FROM OPENROWSET(... <same as before> )
WHERE ...
)

SK

Manuel wrote:

>Steve,
>
>As I said in the previous post, you've anwered my question and the import
>works beautifully. But I've another question.
>Suppose that the first column is named ProjectID.
>I want to run a query before the insert to prevent duplication. I´m using
>Dreamweaver 8.0 and ColdFusion. Usually I run a query Like;
>
><cfquery name="q1" datasource="DatabaseName">
> SELECT ProjectoID
> FROM dbo.ProjectoItems_tbl
> WHERE ProjectoID = '#Form.ProjectoID#'
></cfquery>
><cfif q1.recordcount GT 0>
><cfoutput>message alerting the record already exists</cfoutput>
></cfif>
>
>How do I refer to ProjectID in the Excel spreadsheet to substitute in query
>q1 '#Form.ProjectoID#'?
>
>Thanks for your time and your help.
>
>

From: Manuel on 30 Jul 2006 05:42

Hi Steve,
Thanks for your reply.
I´m still missing something. Please be patient as I´m a 58 year old guy that
decided to learn some programming just a year ago.
I really do have more than one row in the excel Spreadsheet.
Suppose I have 3 columns. 'ProjectoID', 'Column 2' and 'Column3' named the
same in the database table. The Spreadsheet will have many rows (number
unknown).
How do I refer to them in the second WHERE clause? Would it just be:
WHERE Column2 = 'Column2' AND Column3 = 'Column3'

What I need to accomplish is to prevent the insertion of the Spreadsheet if
someone hits F5 or reloads the browser or the Spreadsheet has not been
modified.
Thanks again.
--
Manuel

"Steve Kass" wrote:

> If the Excel spreadsheet only has one row, and you are correctly
> seeing ProjectoID as the column name with the OPENROWSET
> statement, it could be like this:
>
> SELECT ProjectoID
> FROM dbo.ProjectoItems_tbl
> WHERE ProjectoID = (
> SELECT ProjectoID
> FROM OPENROWSET(... <same as before> )
> )
>
> If there is more than one row in the Excel "table", you will
> have to identify it further with a WHERE clause based on
> other columns:
>
> SELECT ProjectoID
> FROM dbo.ProjectoItems_tbl
> WHERE ProjectoID = (
> SELECT ProjectoID
> FROM OPENROWSET(... <same as before> )
> WHERE ...
> )
>
> SK
>
> Manuel wrote:
>
> >Steve,
> >
> >As I said in the previous post, you've anwered my question and the import
> >works beautifully. But I've another question.
> >Suppose that the first column is named ProjectID.
> >I want to run a query before the insert to prevent duplication. I´m using
> >Dreamweaver 8.0 and ColdFusion. Usually I run a query Like;
> >
> ><cfquery name="q1" datasource="DatabaseName">
> > SELECT ProjectoID
> > FROM dbo.ProjectoItems_tbl
> > WHERE ProjectoID = '#Form.ProjectoID#'
> ></cfquery>
> ><cfif q1.recordcount GT 0>
> ><cfoutput>message alerting the record already exists</cfoutput>
> ></cfif>
> >
> >How do I refer to ProjectID in the Excel spreadsheet to substitute in query
> >q1 '#Form.ProjectoID#'?
> >
> >Thanks for your time and your help.
> >
> >
>

From: Steve Kass on 30 Jul 2006 09:26

Manuel,

You can join the Excel "table" like any other database table

SELECT T.ProjectoID
FROM dbo.ProjectoItems_tbl AS T
JOIN (
SELECT ProjectoID
FROM OPENROWSET(... <same as before> )
) AS E
ON T.ProjectoID = E.ProjectoID

or for example, to insert rows that are not already in the table:

INSERT INTO dbo.ProjectoItems_tbl
FROM (
SELECT ProjectoID
FROM OPENROWSET(... <same as before> )
) AS E
WHERE NOT EXISTS (
SELECT * FROM dbo.ProjectItems_tbl AS T
WHERE T.ProjectID = E.ProjectoID
)

Probably for what you need here, there is nothing you need
to do differently because you are working with Excel, other
than refer to the Excel data using OPENROWSET.

SK

Manuel wrote:

>Hi Steve,
>Thanks for your reply.
>I´m still missing something. Please be patient as I´m a 58 year old guy that
>decided to learn some programming just a year ago.
>I really do have more than one row in the excel Spreadsheet.
>Suppose I have 3 columns. 'ProjectoID', 'Column 2' and 'Column3' named the
>same in the database table. The Spreadsheet will have many rows (number
>unknown).
>How do I refer to them in the second WHERE clause? Would it just be:
>WHERE Column2 = 'Column2' AND Column3 = 'Column3'
>
>What I need to accomplish is to prevent the insertion of the Spreadsheet if
>someone hits F5 or reloads the browser or the Spreadsheet has not been
>modified.
>Thanks again.
>
>

From: Steve Kass on 30 Jul 2006 09:26

From: Manuel on 30 Jul 2006 10:55

Thanks once again Steve.
I'm afraid I haven´t made the scenario clear enough. I´ll try and to make
things easier "projecto" in portuguese becomes "project".
I have a main table called "project_tbl" with PK "projectID". I have a
secondary table called "projectItems_tbl". "project_tbl.projectID" =
"projectItems_tbl.projectID".
Then I have the Excel Spreadsheet where the first column is called
"projectID", followed by other columns where the description or parameters of
Items are described like "Length", "Width", "Height".
So, I can have in this Spreadsheet, many rows with the same "projectID", as
all those items belong to the same "project".
Actually what I thought, was just to compare all the columns on the first
row of the spreadsheet and find out if that instance was already in
"projectItems_tbl". That would mean that either the page was reloaded, or
inadvertently someone try to insert what was already inserted. As a
procedure, the insertion is only to be executed after a new spreadsheet is
created for items of the next "projectID" overwriting the previous one, so it
keeps the same name.
I hope my portuguese-english makes sense to you.
Thanks so much.

Manuel

"Steve Kass" wrote:

> Manuel,
>
> You can join the Excel "table" like any other database table
>
> SELECT T.ProjectoID
> FROM dbo.ProjectoItems_tbl AS T
> JOIN (
> SELECT ProjectoID
> FROM OPENROWSET(... <same as before> )
> ) AS E
> ON T.ProjectoID = E.ProjectoID
>
> or for example, to insert rows that are not already in the table:
>
> INSERT INTO dbo.ProjectoItems_tbl
> FROM (
> SELECT ProjectoID
> FROM OPENROWSET(... <same as before> )
> ) AS E
> WHERE NOT EXISTS (
> SELECT * FROM dbo.ProjectItems_tbl AS T
> WHERE T.ProjectID = E.ProjectoID
> )
>
> Probably for what you need here, there is nothing you need
> to do differently because you are working with Excel, other
> than refer to the Excel data using OPENROWSET.
>
> SK
>
> Manuel wrote:
>
> >Hi Steve,
> >Thanks for your reply.
> >I´m still missing something. Please be patient as I´m a 58 year old guy that
> >decided to learn some programming just a year ago.
> >I really do have more than one row in the excel Spreadsheet.
> >Suppose I have 3 columns. 'ProjectoID', 'Column 2' and 'Column3' named the
> >same in the database table. The Spreadsheet will have many rows (number
> >unknown).
> >How do I refer to them in the second WHERE clause? Would it just be:
> >WHERE Column2 = 'Column2' AND Column3 = 'Column3'
> >
> >What I need to accomplish is to prevent the insertion of the Spreadsheet if
> >someone hits F5 or reloads the browser or the Spreadsheet has not been
> >modified.
> >Thanks again.
> >
> >
>

First | Prev | Next | Last
Pages: 1 2 3 4 5
Prev: Dateformat
Next: Reading .LDF