>>>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
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