Import Export in SQL Server 2005

In case of Sql Server 2000, we have the option for import / Export data using DTS by creating objects.

Doing this we are getting the default value of colms of tables int the destination database.

But in case of SQL Server 2005 i am not getting this feature.

If i am using the Import /Export I am not getting the default values.

So first i am generating the script for table and create table in the destination database and then using import/export option to copy data.

Is there any other option to do this?

DEFAULT values are elements of the table schema. If you are not getting the default values, there are two possible explanitions.

1. Your script that creates the tables does not include the DEFAULT values.

2. Your data has empty strings instead of NULL values (DEFAULT values are ONLY used if the data is missing or NULL.)

|||

Yah what you are saying is right.

But what i am asking is , by default i am not getting default values while using import/ export to a new data base.

For that i am first generating script for tables and the table syntax include the default. and then using import/export data.

Is it possible, with out generating the script for tables from the existing database will get the default value in the new database.

|||Yes it is possible -but ONLY if the missing data is [NULL], NOT empty string [''].|||

You need to use the SQL Server specific version of import/export, look here for more info;

http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx

sql

Import Export in SQL Server 2005

In case of Sql Server 2000, we have the option for import / Export data using DTS by creating objects.

Doing this we are getting the default value of colms of tables int the destination database.

But in case of SQL Server 2005 i am not getting this feature.

If i am using the Import /Export I am not getting the default values.

So first i am generating the script for table and create table in the destination database and then using import/export option to copy data.

Is there any other option to do this?

DEFAULT values are elements of the table schema. If you are not getting the default values, there are two possible explanitions.

1. Your script that creates the tables does not include the DEFAULT values.

2. Your data has empty strings instead of NULL values (DEFAULT values are ONLY used if the data is missing or NULL.)

|||

Yah what you are saying is right.

But what i am asking is , by default i am not getting default values while using import/ export to a new data base.

For that i am first generating script for tables and the table syntax include the default. and then using import/export data.

Is it possible, with out generating the script for tables from the existing database will get the default value in the new database.

|||Yes it is possible -but ONLY if the missing data is [NULL], NOT empty string [''].|||

You need to use the SQL Server specific version of import/export, look here for more info;

http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx

Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Hi, I'm a Student, and since a few months ago I'm learning JAVA. I'm creating an application to call and compare times. For this I create in Excel a time table which is quite big and it would be a lot of typing work to input one by one the data in each cell in SQL Server, considering that I have to create 8 more tables. I was able to retreive the data from excel usin the JXL API of JAVA but it doesn't give all the funtions to perform math operations as JDBC. That's why I need to move the tables from Excel to SQL.

I found this sitehttp://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx which gives a code to do so, but I guess that some heathers are missing or maybe I don't know which compiler to use to run that code, I would like you help to identify which compiler use to run that code or if there is some vital piece of code missing.

// Connection String to Excel Workbookstring excelConnectionString=@."Provider=Microsoft
 .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended
 Properties=""Excel 8.0;HDR=YES;""";// Create Connection to Excel Workbookusing (OleDbConnection connection=
new OleDbConnection(excelConnectionString)){ OleDbCommand command=new OleDbCommand
 ("Select ID,Data FROM [Data$]", connection); connection.Open();// Create DbDataReader to Data Worksheetusing (DbDataReader dr= command.ExecuteReader()) {// SQL Server Connection Stringstring sqlConnectionString="Data Source=.;
 Initial Catalog=Test;Integrated Security=True";// Bulk Copy to SQL Serverusing (SqlBulkCopy bulkCopy=
new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName="ExcelData"; bulkCopy.WriteToServer(dr); } }}

On the other hand in this forum I that someelse use that link but implements a totally different code which I'm not able to compile alsohttp://forums.asp.net/p/1110412/2057095.aspx#2057095. It seems this code works as I was able to read, but I do not know which language is used.

Dim excelConnectionStringAsString ="Provider=Microsoft .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;"""

' Using

Dim connectionAs OleDbConnection =New OleDbConnection(excelConnectionString)

Try

Dim commandAs OleDbCommand =New OleDbCommand("Select ID,Data FROM [Data$]", connection)

connection.Open()

' Using

Dim drAs DbDataReader = command.ExecuteReader

Try

Dim sqlConnectionStringAsString = WebConfigurationManager.ConnectionStrings("CampaignEnterpriseConnectionString").ConnectionString

' Using

Dim bulkCopyAs SqlBulkCopy =New SqlBulkCopy(sqlConnectionString)

Try

bulkCopy.DestinationTableName =

"ExcelData"

bulkCopy.WriteToServer(dr)

Finally

CType(bulkCopy, IDisposable).Dispose()

EndTry

Finally

CType(dr, IDisposable).Dispose()

EndTry

Finally

CType(connection, IDisposable).Dispose()

EndTry

Catch exAs Exception

EndTry

The Compilers I have are: Eclipse, Netbeans, MS Visual C++ Express Edition and MS Visual C# Express Edition. In MS Visual C++

Thanks for your help.

Regads,

Robert.

Hi,

From your description, it seems that you want to connect to your EXCEL table by OLDEB from your application, right?

If so, you can build your connection string in the following way, and create the oledbcommand object to execute your query statement.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ExcelFilePath;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
string SQLQuery = "SELECT * FROM [sheet1$]".
// excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

For details, see:

http://support.microsoft.com/kb/326548 (Excel Part)

Besides, If your application runs in 64-bit mode, all of the components it uses must also be 64-bit. There is no 64-bit Jet OLE DB Provider, so you get the message described. You would receive a similar error when trying to connect to a database using OLE DB or ODBC if there is no 64-bit version of the specified OLE DB provider or ODBC driver.

http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=123311

Thanks.

|||

if you really would like be sure that your import is successfully, do not use OLE DB or any other tolls to export your data from excel. Just use excel to export data to any common format like DBase, ACCESS, flat file and next import it to place you want. It will save you a lot of problems because the best tool to read Excel data is Excel itself and do not use other tools to do it. do not use SQL server SSIS also, it use OLE DB and does not work correctly with some excel shits.

Import DT_DBDATE into a SQL TAble with datatype of datetime but without the timestamp

I created a SSIS package and creating a derived column named: Date...set datatype as DT_DBDATE....I do not want the timestamp on date...then I want to load this Date into a SQL server database table, with datatype of datetime, but it will load here with the timestamp which I do not want. Any ideas? I did change datatype of the SQL Server Destination datatype to DT_DBDATE but it will change it back to DT_DBTimestamp. thxSQL Server has no data type that contains just a date. If you insert just a date (insert into table (your_datefield) values ('07/16/2007')) you'll get a result stored in the database as '07/16/2007 00:00:00' or something like that. No way around that at the moment. Keep your eyes on SQL Server 2008 though.