current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I want to import data from my Excel sheet into my SQL Server 2008 database.

I have an Excel sheet that contains different columns : 

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 tried this code but it's showing an error

Incorrect syntax near the keyword 'SELECT'.
Incorrect syntax near ')'.
Invalid object name 'SQL'.

This is my code 

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

In the first query @SQL, I retrieve all IDs from the table Material where the name(marque) equals to those in the Excel sheet.

In the 2nd query, @SQL1, I insert the serviceTag values from the Excel sheet into the table

In the last one @SQL2, I insert into the table material the IDs retrieved from the first query

Is my logic correct ?? Is this how I should proceed ?? Please I need help !!

share|improve this question

2 Answers 2

up vote 0 down vote

Try changing this line:

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

To this - i.e. wrap the @SQL with brackets:

SET @SQL2 = 'INSERT INTO Material (MARQUE) SELECT * FROM (' + @SQL + ')'
share|improve this answer
up vote 0 down vote

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

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 ESSAIE (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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.