How to import values from excel into table using ms sql 2000

Posted by Raghuldrag under Sql Server on 11/18/2014 | Points: 10 | Views : 333 | Status : [Member] | Replies : 3
Hi Friends,

I m already created a table and i wanna to insert values in that more than five hundred row ,that values are stored in Excel files,Here i ve the doubt is it possible to insert values from excel sheet?
i ve current data base of ms sql 2000 ,if it is possible means tell me how to insert values using query?



ex:
---

create table test
(
item_code int,
item_name varchar(50),
bill_qty float,
bil_date datetime
)


in that excel file also had the same column name names


Thanks


Rocky




Responses

Posted by: Bandi on: 11/18/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
INSERT INTO test
SELECT columnsList
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=<location of file and name>.xls',
'SELECT * FROM [<worksheet>$]')


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/18/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
refer
http://msdn.microsoft.com/en-us/library/aa176528%28v=SQL.80%29.aspx

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 11/18/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
http://www.mssqltips.com/sqlservertip/1430/import-excel-data-into-sql-server-using-copy-and-paste/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response