dataset to sql server

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.