Select Into MYSql Table
Mar 25, 2008
Hi guys,
I am trying to transfer data from a table in a SQL server database to an equivilent table in a MYSql database that resides on a different server.
I have previously used a php script to do the job but it proved very slow. I am dealing with a table that is growing and has 9.5 million records in this case and the proceedure may need to be carried out a number of times preday so I am hoping to find a better solution.
I am wondering anyone can point me in the direction of an alternative method.
Thanks,
Derick
View 1 Replies
ADVERTISEMENT
Aug 19, 2005
Hi,I want to create a select statement showing -->John -- happy -- Alice -- Mad -- SundayJohn -- Mad -- Alice -- happy -- MondayHere is my table structure:main (table)main_id int int primary keyperson_id_1 int references person(person_id)mood_id int references mood(mood_id)person_id_2 int references person(person_id)mood_id int references mood(mood_id)day char(10)person (table)person_id int primary keyperson_name char(15)mood (table)mood_id int primary keymood_type char(15)I think my table structure setup is bad....thanks if anyone can help!
View 2 Replies
View Related
May 24, 2006
Hi,
I have a problem to get the following query going which works fine in MySQL:
SELECT MAX(id), col2, col3, col4 FROM mytable GROUP BY col3
So, what I want is to select the row with the highest ID from a group of rows with the same value in col3. How would that be done in T-SQL?
Thanks,
Tom
View 8 Replies
View Related
Jun 19, 2008
Hi everybody,
I'm just writing a stored procedure in my MS SQL 2005 server. I wanted to ask if there is any possibility to execute a select statement to a MySql database on another server? Does anybody have an idea?
Best regards,
ALEX
View 2 Replies
View Related
Jan 29, 2004
Hi groupmates,
In MySQL, we can use limit for our selection. E.g.
select * from mytable limit 10, 20
means selecting [20] records of all information from the table [mytable] from the [10]th record
But in MS SQL, what can we do? Please help.
I am writing PHP to retrieve small data from a large volume of database. Please help
-stan
View 6 Replies
View Related
Feb 12, 2007
Good Morning
Has anyone successfully used cherry's oledb provider for MYSQL to create a linked server from MS SQLserver 2005 to a Linux red hat platform running MYSQL.
I can not get it to work.
I've created a UDL which tests fine. it looks like this
[oledb]
; Everything after this line is an OLE DB initstring
Provider=OleMySql.MySqlSource.1;Persist Security Info=False;User ID=testuser;
Data Source=databridge;Location="";Mode=Read;Trace="""""""""""""""""""""""""""""";
Initial Catalog=riverford_rhdx_20060822
Can any on help me convert this to corrrect syntax for sql stored procedure
sp_addlinkedserver
I've tried this below but it does not work I just get an error saying it can not create an instance of OleMySql.MySqlSource.
I used SQL server management studio to create the linked server then just scripted this out below.
I seem to be missing the user ID, but don't know where to put it in.
EXEC master.dbo.sp_addlinkedserver @server = N'DATABRIDGE_OLEDB', @srvproduct=N'mysql', @provider=N'OleMySql.MySqlSource', @datasrc=N'databridge', @catalog=N'riverford_rhdx_20060822'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'use remote collation', @optvalue=N'false'
Many Thanks
David Hills
View 7 Replies
View Related
Apr 21, 2006
Hello.
I use VS2005 and I was trying to make a little program able to connect to a MySQL 5.0.18 database through MyODBC 3.51.12. I can connect to the database and I can load a table in the Datagridview control, but I can't update the table: the wizard didn't generate the Update method. Even if I configure again the tableAdapter, the wizard generates only:
SELECT statement
table mappings
Fill method
Get method
The table is a very simple table: it has 3 columns and the first is a primary key.
If I create an identical table using Access database and make a program that connects through ODBC, I don't have the problem: the wizard generates also the UPDATE, INSERT and DELETE statements.
Where is the difference? What is the problem?
Thankyou so much for the help
Anthony R.
View 7 Replies
View Related
Aug 2, 2006
Dear All,
I have the table creation script and insret record script.
This is MySQl Format.
What changes I have to do so can I run this scripts into SQL Server 2000.
If any body has successfully done it then please tell me the procedure.
CREATE TABLE `activity` (
`id` bigint(20) NOT NULL auto_increment,
`object_type` varchar(60) default NULL,
`object_id` varchar(20) default NULL,
`person_id` bigint(20) NOT NULL default '0',
`activity_dtm` datetime NOT NULL default '0000-00-00 00:00:00',
`activity_type_cd` varchar(25) NOT NULL default '',
`description_code` varchar(200) default NULL,
PRIMARY KEY (`id`),
KEY `FK9D4BF30FB4715636` (`activity_type_cd`),
KEY `FK9D4BF30F270CDEE0` (`person_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
--
-- Dumping data for table `activity`
--
-- --------------------------------------------------------
--
-- Table structure for table `actv_type`
--
CREATE TABLE `actv_type` (
`code` varchar(25) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
`void_ind` char(1) NOT NULL default '',
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `actv_type`
--
INSERT INTO `actv_type` VALUES ('job_create', 'Created job', 'F');
INSERT INTO `actv_type` VALUES ('job_update', 'Changed job', 'F');
INSERT INTO `actv_type` VALUES ('job_void', 'Voided job', 'F');
INSERT INTO `actv_type` VALUES ('job_activate', 'Activated job', 'F');
INSERT INTO `actv_type` VALUES ('job_deactivate', 'Changed job to deactive', 'F');
INSERT INTO `actv_type` VALUES ('job_appl_create', 'Created application', 'F');
INSERT INTO `actv_type` VALUES ('job_appl_update', 'Updated application', 'F');
INSERT INTO `actv_type` VALUES ('intrv_create', 'Created interview', 'F');
INSERT INTO `actv_type` VALUES ('intrv_update', 'Updated interview', 'F');
INSERT INTO `actv_type` VALUES ('person_update', 'Update person', 'F');
INSERT INTO `actv_type` VALUES ('person_create', 'Create person', 'F');
INSERT INTO `actv_type` VALUES ('company_void', 'Voided company', 'F');
INSERT INTO `actv_type` VALUES ('company_create', 'Created company', 'F');
INSERT INTO `actv_type` VALUES ('company_update', 'Updated Company', 'F');
Thanks in Advance.
View 12 Replies
View Related
Mar 20, 2007
Does anyone know of a reference site where I can find a reference table to get a better idea of data type conversions that I should be using?
I have a MySQL 5.0 database which had a lot of tables (mostly empty) that I already have gotten transferred to SQL Server 2005. However, I am suspicious of some of the data type conversions that SQL Server did.
I would really like a good web site to bookmark to help me with this if there is such a reference. Can anyone help?
If not, the most specific example I have right now is a MySQL column that is expecting to accept currency and the MySQL data type is "Double". SQL Server 2005 translated this as a "float" data type. I normally use a "decimal" data type.
- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://www.servicerank.com/
View 2 Replies
View Related
Nov 29, 2007
Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
Thanks
View 1 Replies
View Related
Jul 31, 2014
I need to write a select statement that take the upper table and select the lower table.
View 3 Replies
View Related
Oct 15, 2007
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
View 1 Replies
View Related
May 23, 2007
I have a 13 GB MYSQL table with the following definition:
Code:
CREATE TABLE `WikiParagraphs` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Paragraph` text NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=73035281 ;
I query it like this:
Code:
select Paragraph from WikiParagraphs where ID in (1,2,3,4)
the 1,2,3,4 bit comes from the Sphinx FullText engine that gives me the IDs I need that match my query within about 500 milliseconds.
But retrieving the data itself takes approximately 3-6 seconds.
Obviously, I'd like to speed this query up.
Any ideas?
G-Man
View 1 Replies
View Related
Dec 29, 2003
Can anybody tell how to import a table with the text column from SQL Server 2000 to MySQL 4.0.17?
I tried this using ODBC connection but got an error message saying, "Query-based Insertion or updating of BLOB values is not supported".
View 9 Replies
View Related
Oct 1, 2007
I have a query that has the following structure
Select *
From Table
Where Condition And ... (some 'Exists' conditions)
When I run the query using field names the query gets much slower, and I cannot understand Why!
Select MyField
From Table
Where Condition And ... (some 'Exists' conditions)
I'm talking about three times slower using the Select MyField sintax.
Any ideas???
View 8 Replies
View Related
Mar 3, 2005
I have an internal Project Management and Scheduling app that I wrote internally for my company. It was written to use MySQL running on a Debian server, but I am going to move it to SQL Server 2000 and integrate it with our Accounting software. The part I am having trouble with is the user login portion. I previously used this:
PHP Code:
$sql = "SELECT * FROM users WHERE username = "$username" AND user_password = password("$password")";
Apparently the password() function is not available when accessing SQL Server via ODBC. Is there an equivalent function I could use isntead so the passwords arent plaintext in the database? I only have 15 people using the system so a blank pwd reset wouldn't be too much trouble.
View 7 Replies
View Related
Aug 28, 2015
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TESTÂ TO Satish.
View 8 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
Aug 16, 2007
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
View 3 Replies
View Related
Aug 17, 2005
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
Any suggestions?
Thanks!
GordonG
View 13 Replies
View Related
Dec 20, 2007
I have set up a 'Parameters' table that solely stores all pre-assigned selection values for a webform. I customized a stored query to Select the values from the Parameters table. I set up the webform. The result is that the form1.apsx automatically populates each DropDownList task with the pre-assigned values from the 'Parameters' table (for example, the stored values in the 'Parameters' table 'Home', 'Business', and 'Other' populate the drop down list for 'Type').
The programming to move the selected data from form1.aspx to a new table in the SQL database perplexes me. If possible, I would like to use the form1.aspx to Postback (or Insert) the "selected" data to a *new* column in a *new* table (such as writing the data to the 'CustomerType' column in the 'Customers' table; I clearly do not want to write back to the 'Parameters' table). Any help to get over this hurdle would be deeply appreciated.
View 1 Replies
View Related
Dec 7, 2007
I have the following data
MASTER
id
name
DETAIL
id
master_id
name
I want a perform a query where i can get all the rows of the master table which have no relationed rows in detail table.
How can I do that???
View 1 Replies
View Related
Mar 17, 2008
I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table?
View 3 Replies
View Related
Jan 20, 2014
I have two tables with the common column-name/ The first one include such columns as the name of football team, its country and budget. The second one include the team name, victories (quantity of it), lost games, and season. So I have the task to select such column as name, victories, lost games in 2nd table but only that ones that has budget over 1 mln? How to build select statement SELECT name, victories, lost g/ from TABLE2 WHERE budget >1000000 from TABLE1? Or should I equates the table1.name=table2.name??
View 6 Replies
View Related
Oct 16, 2014
I am trying to pull a min value of a date column from table 1, if table 1 does not have any record I need pull a date column from table 2. Table 2 will always have a unique record ID (No duplicate ID's).
Table 1
ID date
1 1/1/2014
1 1/5/2014
Table 2
ID date
1 1/5/2014
2 10/15/2014
Here is the desired result when running for ID = 1 (select Min(date) where ID = 1, I should be getting 1/1/2014 from Table 1.
If I am running the same query where ID=2 then I should be getting 10/15/2014 from Table 2. So basically I need to check if a value exists on Table 1 first, if there is no value on Table 1 and then to grab the value from Table 2.
View 4 Replies
View Related
Jan 28, 2008
I've been scratching my head over this one.
It should be pretty easy to do, but I keep going over the same problem.
I have 3 tables (Suppliers, Categories & Suppliers_To_Categories).
Suppliers_To_Categories contains which suppliers are related to which categories.
The set-up is so (simplified):
Suppliers
id
1 Supplier 1
2 Supplier 2
3 Supplier 3
4 Supplier 4
Categories
id
1 Category A
2 Category B
3 Category C
4 Category D
Suppliers_To_Categories
id supplierId categoryId
1 1 1
1 1 2
1 1 3
2 2 3
3 3 1
3 3 3
What I am after is an SQL statement which tells me for a particular category, which suppliers ARE NOT related to it, so they can be assigned.
E.g.
Category D has Suppliers 1-4 unassigned
Category C has Supplier 4 unassigned
Category B has 2, 3, 4 unassigned
Category A has Supplier 2, 4 unassigned etc...
I've got the other side of the SQL statement which tells me which suppliers are assigned to a category (see below):
SELECT Suppliers.*
FROM CatToSupplier INNER JOIN
Suppliers ON CatToSupplier.supplierId = Suppliers.supplierId
WHERE (CatToSupplier.CatId = @CatId)
This is probably really easy, but I've become unstuck!
View 13 Replies
View Related
May 29, 2001
I have an automatic data collection system. The large majority of the time, all data is electronically gathered. On occasion, the need arises to substitute a manually entered value into the data when a valuable piece of information cannot be electronically secured. When a piece of data is open to substitution, it arrives with a value of -9999 (a value not naturally occuring) in Table A. I want to be able to use a trigger on table A to get its substitution value from a corresponding column (Same name) in Table B. Tables A & B will have an identical schema. A is raw data, B is the manually updated table. Any value in the raw table with the content of -9999 gets its replacement from Table B.
I'm open to any ideas!!
RC =:((
View 1 Replies
View Related
Feb 23, 2007
Hi
Trying to figure out a fairly simple SQL query.
Basically i have two tables which have both a matching ID column. How ever, i need to find out which ID's are NOT present in BOTH the tables.
for example
table a -
id
12
13
14
15
table b -
id
12
14
15
Table 'a' has an id of 13 which is not in present in table 'b' and this is the value i need to select!
thanks in advance
View 14 Replies
View Related
Jul 20, 2005
I have two tables with a 1-many relationship. I want to write aselect statement that looks in the table w/many records and comparesit to the records in the primary table to see if there are any recordsthat do not match based on a certain field.Here is how my tables are setup:Task Code TableTCode,TCodeFName,ActiveTracking Table(multiple records)ID,User,ProjectCode,TCode,Hours,DateI want to look at the tracking table and see if there are any TCodelisted here that are not listed in the Task Code table.How do I write a SQL statement to do this?
View 6 Replies
View Related
Sep 27, 2006
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
View 2 Replies
View Related
Oct 11, 2006
This is my Table : date(m/dd/yy) points Work 9/9/06 3 Design 9/10/06 3 Design 9/11/06 3 Programming9/12/06 3 Design 10/9/06 3 Design 10/10/06 3 Design 10/11/06 3 Programming10/12/06 2 Design HOw to select in the below format Month/Year Design Programming9/06 9 310/06 8 3
View 3 Replies
View Related
Jul 11, 2007
I want to select a value from 2 tables , can you please teach me how to do?forexampleTable_AcctNoList Column_AcctIndex , Column_CompanyCode, Column_BankCode, Column_AcctNoTable_DataBackup Column_CompanyCode, Column_KeepMonth how can I reference to Table_DataBackup when I am in Table_AccnNolist and get the value Column_KeepMonth from Table_DataBackup in fact ... I have to reference more than 2 tables in a query.. just hope you can give me a simple exmple how to join 2 table.. in this 2 tables ( Table_AcctNoList�Table_DataBackup)what I actually need is select * from Table_AccNoList where KeepMonth = 6 ( that's what I need but just don't know how to write a correct query ) thank you very much
View 2 Replies
View Related
Jun 26, 2002
I am trying to do a subselect using the same table. Trying below but does not like syntax and get the error message "Server: Msg 107, Level 16, State 2, Line 1 The column prefix 'lsp2' does not match with a table name or alias name used in the query"
select lsp.last_file,lsp.source_database from log_shipping_plan_history as lsp
join
(select max(lsp2.end_time)as ltime,lsp2.source_database
from log_shipping_plan_history
where lsp2.activity = 0
group by lsp2.source_database) as lsp2
on lsp.source_database = lsp2.source_database
Am I missing something obvious?
View 1 Replies
View Related
