>>>>I have to SQL Databases with the same exact tables. I'll call one new_data and the other source_data. I want to transfer data from nine of the tables in my source_data database into tables of the same name in my new_data database.
>>>>
>>>>I'm not sure how to properly begin. I have created a project with a database container. I have setup two new data sources in my ODBC Manager and two new connections, one to each data source.
>>>>
>>>> Do I want to use Updateable Remote Views to do this?
>>>>
>>>>I was going to create Remote Views for each of the tables that need to be updated in my new_data database, but I don't know if that's what I want to do. I'm not sure of the SQL statements I want to use. This is not part of an application. I'm just needing a quick way to transfer the data from the old database into the new one.
>>>>
>>>>Thank you for the help.
>>>
>>>Elgin,
>>>If it's not part of an application (might be) then directly use SQL server tools like DTS or backup and restore.
>>>With ODBC you'd connect to SQL server and have your commands with fully qualified paths. ie:
>>>
>>>insert into NewDb..Table1 Select * from OldDb..Table1
>>>
>>>Cetin
>>
>>I'm using MSDE. I do not have SQL Server nor Enterprise Manager. Can you briefly tell me what DTS is?
>>
>>My source database is d:\c-srv\aa\hoasource.mdf and my new database base is d:\c-srv\aa\hoa_data.mdf.
>>
>>Can you clarify that INSERT INTO command? I would like to learn this syntax and expand on it, as I may develop a simple app for my own use in the future.
>>
>>Thank you for you kind assistance.
>
>Elgin,
>I suggest to buy SQL server developer edition (42-49$) or download SSE (SQLServerExpress2005). Also there is web based SQL server management in sql downloads area. With MSDE it's very hard to make an easy progress (though there are tools for it too on internet).
>
>DTS is a tool in SQL server (Data Transformation Services). Using DTS you could connect to another datasource (be it SQL server or another like VFP database) and import tables as you wish.
>
>.mdf are MasterDataFiles and as files they wouldn't help much. You still need to use SQL server services to use them.
>
>Per Insert into command:
>-During connecting to SQL server (take MSDE as SQL server) you don't need to specify a database name (you might). ie:
>

>lnHandle = SQLStringConnect('Driver=SQL server;'+;
>  'server=servernamehere;Integrated Security=SSPI')
>

>-After connecting you can query (or insert/modify) any database providing the fully qualified database name. ie:
>

>SQLExec(m.lnHandle, 'select * from NorthWind.dbo.Customers','results1')
>SQLExec(m.lnHandle, 'select * from Pubs.dbo.Authors','results2')
>SQLDisconnect(m.lnHandle)
>select results1
>browse title "Customers from Northwind Database"
>select results2
>browse title "Authors from Pubs Database"
>

As you can see connection string didn't have anyone of these databases in its declaration. Still you can query from 2 different databases. This is like using a table in foxpro from 2 different databases with fully qualified paths. ie:
>
>use c:\My Path1\Customer.dbf alias t1 in 0
>use c:\My Path2\Customer.dbf alias t2 in 0
>
>OK now to insert into. 'Insert into' in SQL server (and in VFP9) supports this syntax:
>
>

>INSERT INTO targetTable
>   SELECT fieldList
>   FROM sourceTable
>   WHERE criteria
>

>
>Combining this with previous SQLExec:
>

>lnHandle = SQLStringConnect('Driver=SQL server;'+;
>  'server=servernamehere;Integrated Security=SSPI')
>
>Text to m.lcTransfer noshow && easier to write
>insert into myNewDb..myTable1
>   select * from myOldDb..myTable1
>
>insert into myNewDb..myTable2
>   select * from myOldDb..myTable2
>
>insert into myNewDb..myTable3
>   select * from myOldDb..myTable3
>endtext
>
>SQLExec(m.lnHandle, m.lcTransfer)
>SQLDisconnect(m.lnHandle)
>

You can transfer one or more tables at once this way. Note that this insert into and would append data to existing tables. Another option (if there is no data yet or tables would be a copy):
>
>

>text to m.lcTransfer noshow
>use myNewDb
>drop table myTable1
>
>select * into myNewDb..myTable1 from myOldDb..myTable1
>endtext
>SQLExec(m.lnHandle, m.lcTransfer)
>

There are other usefull variations in SQL server too.
>Note: You also don't need VFP in between to do this. You could write the commands in notepad and save with .sql extension and then use isql or osql to run the scripts.
>Cetin

Cetin,

Thank you very much for this information. I will study it. I will definitely get one of the more up to date options for manipulating this data. I will likely try the DTS option for now. I will try the code you have given me and will likely make use of it when needed.

Thank you.