insert dataset into alternate database without creating the insert statement manually RRS feed

  • Question

  • I have a dataset that I pulled from Informix via ODBC using a select like "select * from table where date=yesterday".  During the select I set adapter.AcceptChangesDuringFill = false; and all is well there.


    Now I need to insert this dataset into a SQL Server 2005 table of the same name and schema.

    I would really like to do this without creating the insert statement manually if at all possible as these tables are very large and I have to do several different tables.


    The datasets contain between 20,000 and 30,000 records on most days.


    Any assistance would be really appreciated.




    Thursday, December 13, 2007 5:45 PM


All replies

  • You could just create a linked server query which gets the data from Informix and insert into SQL Server table the question is do you need a DataSet to handle such operation if you are not using the data in .NET?  The clean alternative is automated SSIS package that moves the data as needed, SQL Server can do this since version 7.0.  Hope this helps


    Thursday, December 13, 2007 10:22 PM
  • That is a great idea.  I did some initial looking in SSIS and it should fit the bill perfectly.  Problem is I know NOTHING about this product.  Do you have any good books to recommend or sites?  I have no idea what to direct to what at this point.


    Can you you give me a quick list of items to link a ODBC table to the SQL twin.


    Thursday, December 13, 2007 11:36 PM
  • To use SSIS you will need the developer edition of SQL Server which cost under $40 on the web or free with VS2005 pro and above because it comes with a SQL Server Business Intelligence development studio you can use to develop the SSIS packages. The most important things are correct Informix drivers and permissions in both SQL Server and Informix. The first link is a recent thread in this forum about Informix Linked server. The second link covers basic how to about SSIS packages and there is a forum here in the SQL Server section if you need help.



    Friday, December 14, 2007 12:29 AM