How to transfer data in a table in dataset to a table in sql server ?
i.e. I want to transfer the table values in excel to a table in sql server. I have populated the excel table in a dataset. I just want to know how to transfer from dataset to sql server.
Thanks
You can use the INTO clause to move the data into SQL Server because it creates a new table on destination or use the code below to load the data into SQL Server then load that table into a dataset. The reason is Excel since XP/2003 can use sql statements. Hope this helps.
/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Sheet1$
CREATE TABLE test_excel
(id int,
name varchar(255))
GO
INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$
SELECT *
FROM test_excel
/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Table1
SELECT *
FROM ExcelSource...Table2
http://msdn2.microsoft.com/en-us/library/ms188029.aspx
|||I cant understand ur code. Kindly explain it.
I want to export the data in excel to sql server table. I have fetched the data from excel and stored in dataset. Then i want to export it to sql server. How to do that?
|||That code is to insert Excel into SQL Server table before you put the table into a dataset, if you don't want that then use the link and create a SELECT INTO statement it create a table for you and now that I think about it you can create a SQL Server Table with ExecuteNonQuery in ADO.NET run a search in the forums you should find some links with code. Hope this helps.