exporting the datas from a view to a external table in SQL Server

[php]

Hi,

- i'd like to send the data from a view to an external table, not possible in SQL server 7.

- another solution would be perhaps to generate a script such as :
CREATE TABLE ...
( tabledef ... )
AS
SELECT ..... (with an inner join)

but this doesn't work neither ....

- i have no experience of temporary tables , perhaps this could help ??

Any idea would be very helpful and apreciated !

Best regards
Philippe Peeters

 

[tlbroadbent]

Please explain a little more.
[ol][li]What is the source of your data? I assume the data is in a SQL table.
[li]What is the destination table? External could mean many things - Oracle, Access, Excel, text, etc.
[li]Does the data to be exported require any manipulation or transformations?
[li]What does the View you refer to look like?[/ol]Data can be exported from SQL server in various ways. Tables which hold the data and views which provide alternate ways of looking at the data can be used as source for export. The data seen in a view can certainly be exported in SQL 7. Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[php]

1-The source of my datas is a request between 2 tables of a
SQL server database,

2-The destination should normaly be an access table,
but i can do with an sql server table

3- the datas don't need any transformations but they are dynamical , i can't store them

4- the view script was :

CREATE VIEW dbo.VIEW_transaction
AS
SELECT transaction_id, transaction_time, transaction_state,
totalOrder, name, email
FROM log_Transactions INNER JOIN
clients ON customer_id = idclient

Thanks again,

Best regards,

Philippe Peeters

 

[tlbroadbent]

You can use DTS to transfer data from your SQL server to the external destination.
[ol][li]Start the DTS Export Wizard,
[li]In the 1st screen, select the source
[li]In the 2nd screen, select the destination
[li]In the 3rd screen ("Specify Table Copy or Query&quot, check "Use a query to specify the data to transfer."
[li]In the 4th screen, enter "Select * From VIEW_transaction" in the query statement box.
[li]In the 5th screen ("Select Source Tables&quot, select the destination table.
[li]Continue through the remainder of the screens to export the data.
[li]You can even create a job to run the export on a schedule.[/ol]Hope this helps. Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[php]

Thanks very much for your answer Terry,

but (ooops!) i can't use the DTS export wizard because
of the setup of SQL Server has not been in this way .

Is there an other way to proceed ?

Thanks again in advance.

Philippe

 

[tlbroadbent]

For my sake, explain why DTS is not available in your SQL 7 setup.

Here are some other options.
[ol][li]You can BCP the data using the view. See "bcp Utility" in SQL Books Online. However, you'll only be able to export to a text file. You can then import the text file to the destination table.

[li]You can add a linked server in SQL 7 and use transact SQL to copy the files. See "Configuring Linked Servers" and "sp_addlinkedserver" in SQL BOL. The syntax would then look like.

Insert destserver.dbname.dbo.tblname
Select * from VIEW_transaction

[li]You can link the SQL server view in Access and then make an append query to copy the SQL data to an Access table.[/ol]Let us know if you need more info.
Terry

;-) I never worry about the future. It comes soon enough. -Albert Einstein

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[php]

i'm back,

(shame on me ...) i made a confusion between DTS and DTC !

So, as you've noticed it in your second mail ,
IT IS possible to export datas from a view to a file / or another table , by checking "Use a query to specify the data to transfer."
This DOES work allright

You taught me this Terry, thanks a lot again !

Philippe Peeters