Exporting SQL views to files? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Exporting SQL views to files?

Author

Topic

northyen.dk
Starting Member

16 Posts

Posted - 2012-02-20 : 09:23:49

Hi,

I've been tasked to export data from an ERP system to files, and for that I need some general pointers towards how to go about the task. My knowledge about SQL is basic.

The source and destination are very different, and my plan is to setup a number of SQL views, that orders and collect the data, and create some kind of export functions that validates and outputs the required format (with UTF-8 encoding).

An example on this, could be exporting customer details. The destination file looks like this (yes, it's an inverse question mark, don't ask :-):

AccountNumber¿CustomerName¿CustomerAddress¿CustomerAddress2¿CustomerCountry¿CustomerNumber¿CustomerFixedValue

With data it could look like this:

10203040¿Bingo Corp¿Bingo Road 1¿*blank¿Denmark¿0¿ABC123

The format rules are:

AccountNumber: Max length 10, mandatory
CustomerName: Max length 30, mandatory
CustomerAddress: Max length 30, optional, if blank value shall be *blank
CustomerAddress2: Max length 30, optional, if blank value shall be *blank
CustomerCountry: Max length 30, mandatory, only a set of values from another table is legal.
CustomerNumber: Integer, optional, if blank/null the value shall be 0.
CustomerFixedValue: Always ABC123

I'd like to setup the rules in a seperate table, that describes the output format, and then check against this table, for validation errors. Ie. if the AccountNumber is 15 in length, I need to know about this.

The end goal is to end up with a SQL view, that I can use in Excel, validate data, and when everything checks out good, I'd like to run an export, presumably by using osql?
'll end up with 70 SQL views, if I setup the export like so.

In total a whole 2200 columns spread across 70 files, are to be handled, so it's quite a task.

Any kind of help is appriciated.

gedwards
Starting Member

19 Posts

Posted - 2012-02-21 : 08:50:20

More details on 'why' you are exporting might be helpful.
And 'Source and Destination are very different' - not sure what you mean by that.

I would think that the ERP has a back end relational database engine that should handle data types and if NULL is allowed.
Excel has a query tool that can attach to another data source, and Access can also do the same.
Data in 2 places is something I try to avoid, and prefer something that can be refereshed by the user if possible.
And 'export' vs. something like Access being a front end to a SQL back end could be a better solution.

Greg E

northyen.dk
Starting Member

16 Posts

Posted - 2012-02-21 : 09:59:41

The reason for export is that the current ERP system is going to be phased out, and a new one is going to take over. And for that to happen, I need to export a number of files containing the data from the current ERP system. The current ERP system is old, doesn't support UTF-8, and even the inverse question mark, is going to be a problem. So I need to work the data externally. The source and destination is different in the sense described in the example.

The solution to all this could be setting up the SQL views, so they match the destination file setup, ie. the sequence of columns in the view matches the sequence of columns in the file. And when that's done, I presume I could "easily" export the view into the flat file, with the desired encoding and correct column separator tag.

Unless I'm completely off target here?

Zar Shardan
Starting Member

5 Posts

Posted - 2012-03-20 : 17:24:58

Are both source and destination DBs SQL Server ?
If you can put both Source and Destination DBs on the same SQL server Instance you could quite easily copy very large amounts of data by simply doing a series of these:

INSERT INTO DestDB.SomeSchema.SomeTable
(
[list of destination columns]
)
SELECT [list of source columns]
FROM SourceDB.SomeSchema.SomeSrcView

If you can't get them on the same server, but they are still both SQL Server DBs - you could set up database links (http://msdn.microsoft.com/en-us/library/ms190479.aspx) first and use the above approach.

www.livedoco.com - View your SQL Server structure in your browser. Live.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources