Import MSSQL Tables

Hi DJ,

SQL Express, and Management Studio Express, don't include the Import/Export wizard because that wizard is dependent on SSIS, a component that is not part of SQL Express. There are a number of different ways to accomplish this depending upon your need including Access.

Since you are going from SQL to SQL, I would recomend the following:

1. Script out the table from your source database and then use the script to create the same table in the target database.
2. Use BCP to export the data to a file and then to import the data from the file into the newly created table.

You can find information about BCP at http://msdn2.microsoft.com/en-us/library/ms162802.aspx.

Mike - SQL Express team

MikeWachal-MSFT at 2007-9-8 >

hi,

Richard Bysouth wrote:

Hi Before I start planning this whole thing, I just wanted to check that the BCP solution would meet my needs. Could someone please confirm that the following is possible? My requirements are: Be able to export data in a SQL Server Express instance from multiple tables to a file using a query. E.g. Export Products and all related product information. Say there is a PK ProductId (Identity column), so this is used as FK in all other related tables

no... BCP is "single object" based.. you can write a batch to export all [Products] first and [ProductsDetail] soon after that, but all Products will be exported and then all ProductsDetails will come... it's not a hyerarchical export like a dataset in memory image could be...

Be able to import that data into another SQL Server Express instance stored elsewhere. E.g. our customer’s headquarters to allow collating of data from multiple locations. When importing Products, ProductId will be set as it is an Identity column. This value will need to be used as the FK when importing data to all other tables.

yes... you'll need to bulk load all "package" related files... say you define a package (this is a home made definition, no such a term exists in BCP scenario) built of [Products] and [ProductsDetail] ... you'll have 2 separated files, 1 for each table...

the export of the [Products] object can include the Primary Key colum then related to the [ProductsDetail] object's columns...

but you can not define a relationship as you perhaps think, that's to say

[Products] new row = new Id that will be later consumed/used by [ProductsDetail] related columns...

you'll actually have

[Products] rowS (all the exported [Products] rows)

and in another file [ProductsDetail] rows... and the relationship among them must be already set at export time..

The above processes must be executed from our VB 2005 windows app without requiring interaction from the end user.

this can be done, shelling to BCP command line tool or via BULK INSERT Transact-SQL statements (http://msdn2.microsoft.com/en-us/library/ms188365.aspx) ... the first solution requires each workstation has SQL Server Client Tools locally installed, while the 2nd one requires the exported files to be reacheable from the SQL Server installed PC, as a full path will be specified to access the file(s)... this 2nd scenario obviously "drops" network shares in order to limit band-width related latency and throught-output..

becouse of the 2 limitations, I do usually prefer "INSERT INTO" DDL scripts, that can be executed remotely too (ok.. network trafic is involved , but no direct access to phisical files is required from the SQL Server installed pc) from client apps..

this scenario is even explained in a very elegant way in http://msdn.microsoft.com/msdnmag/issues/04/09/customdatabaseinstaller/, the best article I've ever read about database distribution, which is actually very similar to the path I personally follow as well.. I do not use vbs files, but the general idea is very similar..

as regards the DDL scripts, they can be provided by SSMSE as well, but individually for each object, but you can perhaps have a look at a free prj of mine, amScript, available at the link following my sign, which is a tool to output a bunch (or even a full database) objects as single or multiple DDL script files...

INSERT INTO statements generation will be provided soon (I hope ) in another separated free project "under construction"..

regards

AndreaMontanari at 2007-9-8 >

hi Richard,

yes, this can be a solution as well.. you define a dataset and it's relations, and then cycle among the master table and related details tables, feeding command objects to perform INSERT statements.. it's not very decoupled and generic but works as well ..

regards

AndreaMontanari at 2007-9-8 >