Errors while trying to import data from Excel to SQL tables

Question

I am trying to import data from Excel to SQL Server tables but I am getting this error. Can you please suggest how to avoid these.

score 1 · Answer 1 · edited May 23 '17 at 12:16

1

This is probably caused by the ACYR_ISN column beeing set to identity.

See this post for how to resolve the issue: https://stackoverflow.com/a/20953512/2280148

edited May 23 '17 at 12:16

Community

1
1

answered Apr 18 '16 at 08:49

JohannesGbg

101
1
4

1

Taglines aren't allowed in posts here, particularly ones that advertise products, so I've removed that portion of your answer. You're welcome to place this information in your profile, if you'd like. – Brad Larson Apr 20 '16 at 02:05

score 1 · Answer 2 · answered Apr 26 '16 at 08:26

You can import excel file into MSSQL database by using T-SQL.

DECLARE @strQuery AS VARCHAR(600),
        @strFilePath as VARCHAR(200),
        @TableName AS VARCHAR(50),
        @EmpCode varchar(8), 
        @Name varchar(50)

Declare @DataTable as table
    (
       EmpCode varchar(8), 
       Name varchar(50)
     )


SET @strFilePath='\\Path\Excel.xlsx'
SET @TableName='Sheet1'

SET @strQuery='select * from openrowset('
    SET @strQuery=@strQuery+'''Microsoft.ACE.OLEDB.12.0'''+','
    set @strQuery=@strQuery+ '''Excel 12.0;Database='+@strFilePath+''''+','
    SET @strQuery=@strQuery+'''select * from ['+@TableName+'$]'''+')'

insert into @DataTable 
EXECUTE(@strQuery)


declare curStaff cursor for
select * from @DataTable
open curStaff
fetch from curStaff into @EmpCode, @Name
while @@FETCH_STATUS =0
    begin
      //Do your work here by using data
    fetch from curStaff into  @EmpCode, @Name 
end
close curStaff
deallocate curStaff

Errors while trying to import data from Excel to SQL tables

2 Answers2