Click here to Skip to main content
14,927,639 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello every one. I want to import data from my excel sheet to my sqlServer 2008 database.
I have an excel sheet that contains different columns :
Example (excel sheet)

Service tag | marque| Type | Serial
FGT3456 | DELL | UC | Optiplex 760

and my table has the same structure, but instead of varchar values, I have foreign keys ( the IDs )

Example (table material)

Service tag | marque| Type | Serial
FGT3456 | 1 | 18 | 27

What I want to do is to fetch into the column marque in the excel sheet, get all the marque values, compare the values to those in my table, get all them IDs and in the end insert the Ids into my table material.
(I wish I was clear)

I tried this code but it's showing this error 'Incorrect syntax near the keyword 'SELECT'. Incorrect syntax near ']'. Invalid object name 'SQL'.'

This is my code


SQL
ALTER PROCEDURE [dbo].[spx_Import]
	@SheetName varchar(20),
    @FilePath varchar(100),
	@HDR varchar(3),
	@TableName varchar(50)
AS
BEGIN
    DECLARE @SQL nvarchar(1000)
    DECLARE @SQL1 nvarchar(1000)
    DECLARE @SQL2 nvarchar(1000)
		SET @SQL = 'SELECT idMarque FROM MarqueMateriel WHERE marque = SELECT (marque) FROM OPENDATASOURCE'
		SET @SQL1 = 'INSERT INTO Material (Service tag) SELECT (service tag) FROM OPENDATASOURCE'
		SET @SQL2 = 'INSERT INTO Material (MARQUE) SELECT * FROM '+@SQL''
			
    SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR=' 
    SET @SQL = @SQL + @HDR + ''''''')...[' 
    SET @SQL = @SQL + @SheetName + ']'
    SET @SQL1 = @SQL1 + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
    SET @SQL1 = @SQL1 + @FilePath + ';Extended Properties=''''Excel 12.0;HDR=' 
    SET @SQL1 = @SQL1 + @HDR + ''''''')...[' 
    SET @SQL1 = @SQL1 + @SheetName + ']'
    SET @SQL1 = @SQL2 + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
    SET @SQL1 = @SQL2 + @FilePath + ';Extended Properties=''''Excel 12.0;HDR=' 
    SET @SQL1 = @SQL2 + @HDR + ''''''')...[' 
    SET @SQL1 = @SQL2 + @SheetName + ']'
    EXEC sp_executesql @SQL
    EXEC sp_executesql @SQL1
	EXEC sp_executesql @SQL2
	
END


Please I need your help :( my tutor will turn my next 3 days into hell if I don't get it done
Posted
Updated 22-Jul-14 2:07am
v2
Comments
[no name] 21-Jul-14 21:06pm
   
The only thing that I added to your code was a check to see if the SP already exists and changed the ALTER PROCEDURE to a CREATE and I got no syntax error using SS2008R2
IF OBJECT_ID ( 'spx_Import', 'P' ) IS NOT NULL
DROP PROCEDURE spx_Import;
GO
CREATE PROCEDURE [dbo].[spx_Import]
Member 10889990 22-Jul-14 5:28am
   
This is the line that has the error "SELECT idMarque FROM MarqueMateriel WHERE marque = SELECT (marque) FROM OPENDATASOURCE" this is where I want to run through all the marque column in my excel sheet and check if there is a record similar to it and then insert in the table Material (SQL2)

change
SQL
SET @SQL2 = 'INSERT INTO Material (MARQUE) SELECT * FROM SQL'

to
SQL
SET @SQL2 = 'INSERT INTO Material (MARQUE) SELECT * FROM OPENDATASOURCE'
   
Comments
Member 10889990 22-Jul-14 5:25am
   
In the first query SQL I retieve the Marque ID from the table MarqueMateriel
In the query SQL2 I insert into the material table all rows in the SQL. This is how I should do, because I have only IDs in my table material not varchar values.
The error is in this line:
SQL
SET @SQL2 = 'INSERT INTO Material (MARQUE) SELECT * FROM '+@SQL''

Replace it with:
SQL
SET @SQL2 = 'INSERT INTO Material (MARQUE) SELECT * FROM ' + @SQL + ''


Tip: When the error message is diplayed in Microsoft Sql Server Management Studio, double click on it to move cursor to the line with error.
   
Comments
Member 10889990 22-Jul-14 8:46am
   
I corrected it, Still have the same Error "Incorrect syntax near the keyword 'SELECT'. Incorrect syntax near the keyword 'SELECT'. Incorrect syntax near the keyword 'SELECT'. Incorrect syntax near 'OPENDATASOURCE'."
I found the solution, it's quite easy and should've think of it from the first time . I imported all the data from my excel sheet into a temp table, and fetch into my mark table get all IDs and insert them directly into my material table.
This is how I achieved this
SQL
USE [AxaStock]
GO


ALTER PROCEDURE [dbo].[spx_Import]
	@SheetName varchar(20),
    @FilePath varchar(100),
	@HDR varchar(3),
	@TableName varchar(50)
AS
BEGIN
    DECLARE @SQL nvarchar(1000)
    DECLARE @QUERY nvarchar(1000)
		SET @SQL = 'INSERT INTO ESSAIEIMPORT (serviceTag, periodeGarantie, periodeLeasing, marque, designation, serie, entite, dateDG, nCommande) SELECT serviceTag, periodeGarantie, periodeLeasing, marque, designation, serie, entite, dateDG, nCommande FROM OPENDATASOURCE'
		SET @QUERY = 'insert into Materiel (serviceTag, idMarque, idTypeMateriel, idSerieMateriel) select distinct serviceTag, idMarque, idTypeMateriel, '+
		'idSerieMateriel from ESSAIEIMPORT ess, MarqueMateriel mm, Serie s, TypeMateriel tm where mm.marque=ess.marque and tm.nomType=ess.designation and '+
		's.serieMateriel=ess.serie delete from ESSAIEIMPORT' 	
    SET @SQL = @SQL + '(''Microsoft.ACE.OLEDB.12.0'',''Data Source='
    SET @SQL = @SQL + @FilePath + ';Extended Properties=''''Excel 12.0;HDR=' 
    SET @SQL = @SQL + @HDR + ''''''')...[' 
    SET @SQL = @SQL + @SheetName + ']'
    EXEC sp_executesql @SQL
    EXEC sp_executesql @QUERY

	
END
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900