sql server table records to sqlite
-
Hi, My database is on host (internet) and i wanna to get all records of one special table (it has about 1 million records) and insert into table in SQLite(in local-android mobile) . the tables are same and only have 10 columns. how can i do that with SP. which way is faster?
Is this a once off operation or do you need to repeat it. One time, use the CSV method. If it must be repeatable then you need to set up a service (WCF) between the databases, you should not connect an online database directly to a remote database.
Never underestimate the power of human stupidity RAH
-
thanks for Solution, but i wanna something like this cause the speed is very hight and wont take time :
SELECT tt.IdAmlak, tt.ParentId, tt.NameOpp, tt.Price, tt.[Square],
tt.OstanShahrMantagha, tt.PhoneAddress, tt.CountAmount
INTO TestTable
FROM tblTesting tttblTesting : Table in database Sql Server 2008 in server internet TestTable : Table in Database SQLite in local (android mobile)Well, if you can have an ODBC connection to the server on the web, and one to the android device, there's no reason why you couldn't do something like that. I'm not convinced it would be quicker than simply downloading the csv file and processing it locally in one go...
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
Is this a once off operation or do you need to repeat it. One time, use the CSV method. If it must be repeatable then you need to set up a service (WCF) between the databases, you should not connect an online database directly to a remote database.
Never underestimate the power of human stupidity RAH
thanks for solution I need to connect and get all info every 2 days. cause the data of that table change every 2 days.
-
Well, if you can have an ODBC connection to the server on the web, and one to the android device, there's no reason why you couldn't do something like that. I'm not convinced it would be quicker than simply downloading the csv file and processing it locally in one go...
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
thanks for solution can u give me a sample for
csv file and processing it locally
and i can do this also ,so plz give sample :
if you can have an ODBC connection to the server on the web, and one to the android device
-
thanks for solution can u give me a sample for
csv file and processing it locally
and i can do this also ,so plz give sample :
if you can have an ODBC connection to the server on the web, and one to the android device
To create a csv file, simply run a query on your sql server and save the results to a file. For the ODBC connection, create a couple of DSN's locally (one to the sql server, one to sqlite), then from a common location connect to both and run your query. This might be possible through a local SQL Server Management Studio copy, or through anything else that allows you to attach ODBC databases, even MS Access... Warning - MS Access will be very, very slow.
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
To create a csv file, simply run a query on your sql server and save the results to a file. For the ODBC connection, create a couple of DSN's locally (one to the sql server, one to sqlite), then from a common location connect to both and run your query. This might be possible through a local SQL Server Management Studio copy, or through anything else that allows you to attach ODBC databases, even MS Access... Warning - MS Access will be very, very slow.
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
i have googled what about this sp :
insert into CRM-CMS.dbo.tableName
select * from [217.66.216.77,1633].CRM-CMS.dbo.tblTesting -
i have googled what about this sp :
insert into CRM-CMS.dbo.tableName
select * from [217.66.216.77,1633].CRM-CMS.dbo.tblTestingTry just running the select portion of it and see if you return results. Your insert will need to use a field list though, not *. eg: insert into table1 (field1, field2) select field1, field2 from table2 Also, you would need to clear the data from your local table first if you are wanting to replace it with the data from your server.
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
-
thanks for solution I need to connect and get all info every 2 days. cause the data of that table change every 2 days.
Then you need to build a service between the databases, we use WCF.
Never underestimate the power of human stupidity RAH
-
Then you need to build a service between the databases, we use WCF.
Never underestimate the power of human stupidity RAH
can please give small sample?
-
can please give small sample?
-
Try just running the select portion of it and see if you return results. Your insert will need to use a field list though, not *. eg: insert into table1 (field1, field2) select field1, field2 from table2 Also, you would need to clear the data from your local table first if you are wanting to replace it with the data from your server.
Quad skating his way through the world since the early 80's... Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!
i got this code : but cannot run for SQLite : i get this error in sqlite :
SQLite error near "INTO" : syntax errorcmd.CommandText = @"SELECT top(5) IdAmlak, ParentId, NameOpp, Price, [Square],
OstanShahrMantagha, PhoneAddress, CountAmount
INTO Melk
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=217.66.216.77,1633;User ID=tinadbuser;Password=dbtin123ir'
).[CRM-CMS].dbo.tblTesting"; -
i got this code :
but cannot run for SQLite :i get this error in sqlite :
<code>SQLite error near "INTO" : syntax error </code>
<pre lang="sql">cmd.CommandText = @"SELECT top(5) IdAmlak, ParentId, NameOpp, Price, [Square],
OstanShahrMantagha, PhoneAddress, CountAmount
INTO Melk
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=217.66.216.77,1633;User ID=tinadbuser;Password=dbtin123ir'
).[CRM-CMS].dbo.tblTesting";</pre> -
i got this code : but cannot run for SQLite : i get this error in sqlite :
SQLite error near "INTO" : syntax errorcmd.CommandText = @"SELECT top(5) IdAmlak, ParentId, NameOpp, Price, [Square],
OstanShahrMantagha, PhoneAddress, CountAmount
INTO Melk
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=217.66.216.77,1633;User ID=tinadbuser;Password=dbtin123ir'
).[CRM-CMS].dbo.tblTesting";You don't want to do this using a linked server; cleanest way is to generate the CSV (there's documentation on the internet); to send the CSV, and to bulkload it. SQLite does not use TSQL. If you use multiple database-systems from different vendors, it'd be preferable to keep your SQL in the SQL92-dialect.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
i got this code :
but cannot run for SQLite :i get this error in sqlite :
<code>SQLite error near "INTO" : syntax error </code>
<pre lang="sql">cmd.CommandText = @"SELECT top(5) IdAmlak, ParentId, NameOpp, Price, [Square],
OstanShahrMantagha, PhoneAddress, CountAmount
INTO Melk
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=217.66.216.77,1633;User ID=tinadbuser;Password=dbtin123ir'
).[CRM-CMS].dbo.tblTesting";</pre>At a guess I would say SQL Lite does not support INTO so make the query like a normal insert statement.
Insert Melk (field, names, list) Select ...
Never underestimate the power of human stupidity RAH
-
You don't want to do this using a linked server; cleanest way is to generate the CSV (there's documentation on the internet); to send the CSV, and to bulkload it. SQLite does not use TSQL. If you use multiple database-systems from different vendors, it'd be preferable to keep your SQL in the SQL92-dialect.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Realy thanks! I have installed an app android that do same as what i wanna and i have installed it and got this file that have info but cannot read that. what type of data it have .is it SQLite or something else. how can i do that same? download the data file : http://upload.ugm.ac.id/221asnaf and download app : http://upload.ugm.ac.id/561A0829-MashaghelHamrah.apk[^] thanks in advanced !