|
with out knowing what you code is like and how you are inserting your data have you considered a union statement?
MySQL Union[^]
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
It sounds like your database is in serious need of normalization.
|
|
|
|
|
MySql Insert documentation[^]
As far as I can see you will need to use a loop of some kind, can I ask why you want to do it with no looping? you could easily do it simply like this in psuedo code
Open Database Connection
For i = 1 to 50
Insert into Datbase table the values
i++
next
Close Database
As others have said you may need to reconsider your database design.
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
by using for loop i can but is there any possibility to write a single Mysql query to insert 50 or more records. In Sybase they are using GO command like insert into tablename(columnname) values('abc') go 50 it wil insert 50 records.
|
|
|
|
|
The only way I could see you doing that in MySql is to do something like the following but I believe that it will be unmanageable and to be honest I personally only use this approach when I am trying to knock up some dirty data for R&D or testing never used it in a live system
insert into numbers (MyNumber)
select 1
union
select 2
union
select 3
union
select 4
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
According to the MySQL documentation:
INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
|
|
|
|
|
Why would you want to duplicate data this way?
|
|
|
|
|
Not sure if this is the right forum for this, but...
In VS2010, when I doule click a .sql file or try to deploy, I get "Object reference not set to an insgtanceof an object". The message appears a number of times one right after another. Sometimes a tab for the file opens with nothing in it. In all cases VS hangs.
This doesn't happen on my work PC, just my home PC.
Anyone know what's causing this? A Google search found this[^] but it's not the problem. I created the SQL DB project in VS as well as the .sql file.
Everything makes sense in someone's mind
|
|
|
|
|
Hi all,
i was trying to do a mutiple select with following fileds
ServiceCategoryName(field)->ServiceCategory(Table)
ServiceName(field)->Service(Table)
ServiceLocation(field)->Location(Table)
Created Date,SendersName,SendersEmaild(fields) ->ServiceItemForward (Table)
DB:SQLServer 2008
and the sp i wrote is following
what changes should i make to this
ALTER PROC [dbo].[usp__Search_ServiceItemForward]
@CategoryID int,
@ServiceID int,
@LocationName varchar(100),
@createdFromDate date,
@createdToDate date,
@sender varchar(100),
@senderEmail varchar(100)
AS
if(@createdFromDate is null)
set @createdFromDate =dateadd(d,-7, CONVERT(date, GETDATE()))
if(@createdToDate is null)
set @createdToDate=CONVERT(date, GETDATE())
Begin
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
SELECT dbo.ServiceItemForward.* FROM dbo.ServiceItemForward INNER JOIN
dbo.ServiceItems ON dbo.ServiceItemForward.ServiceItemID = dbo.ServiceItems.ServiceItemID INNER JOIN
dbo.Service ON dbo.ServiceItems.ServiceID = dbo.Service.ServiceID INNER JOIN
dbo.ServiceCategory ON dbo.Service.ServiceCategoryID = dbo.ServiceCategory.ServiceCategoryID
where
(@CategoryID=0 or ServiceCategory.ServiceCategoryID = @CategoryID)
and (@ServiceID=0 or Service.ServiceID = @ServiceID)
and (@LocationName=0 or ServiceItems.ServiceLocation = @LocationName)
and (ServiceItemForward.CreatedDate Between @createdFromDate and @createdToDate)
and(@sender is null or ServiceItemForward.SendersName=@sender)
and (@senderEmail is null or ServiceItemForward.SendersEmaild=@senderEmail)
COMMIT
End
please help me out in htis....
Thanks in advance
modified 13-Jan-12 5:48am.
|
|
|
|
|
You do realise in your null date test you are populating @createdToDate in both tests.
It would help to know what the problem is!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have cooreected that statement and the code was not wrkng.....
|
|
|
|
|
reogeo2008 wrote: I have cooreected that statement and the code was not wrkng....
If you post a problem, then describe what the code is supposed to do and what the error is. All we see is your code and the statement that it ain't workin', but you didn't explain what the desired result would be like or what you're trying to achieve.
Why is there a commit, if you are only selecting records?
Bastard Programmer from Hell 
|
|
|
|
|
Eddy Vluggen wrote: Why is there a commit, if you are only selecting records?
Just to be sure that he is committed to selecting records only. Nothing else! 
|
|
|
|
|
Not sure it belongs in this forum, I hope it does.
I have a SQL Server Express application with a few clients; some are running app1 (.NET WinForms), others app2 (also .NET WinForms). When one of the app1 clients changes some data in the database, all of the app2 clients (who may be viewing the same data) need to be notified about the data change within a few seconds.
How would you implement this? I could have all clients refresh their views all the time (no thanks), implement a direct client-to-client notification based on some interprocess comm scheme (I'd rather not), or maybe the database itself might be helping out. Suggestions are welcome.
TIA
|
|
|
|
|
 What is the world coming to? Luc asking questions!!!!
I've done similar things in the past, but not using SQL Server.
I don't know what IPC capabilities are in your server or could be easily grafted onto it.
Given these two points, I'd say:
1. Make the server do it. Client to client gets way too messy when you consider clients appearing/disappearing on the fly. At least the server has a reasonably current idea of who's out there.
2. How I did it was to use sockets with the following simple protocol:
client sends requests
at most one outstanding
first request of a session is a logon
server sends responses to requests OR asynchronous notifications
any socket error => forced logoff
(therefore unexpected logon at server => kill old session, start new)
HTH
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
Thanks Peter, yes I need to earn some Enquirer points too you know. 
I'm currently considering a UDP multicast, where producing clients just throw their "new critical data" message up in the air, and consuming clients react on it. I was hoping SQL Server itself would have some built-in mechanism, I need to read up on it I guess.
|
|
|
|
|
Sounds like your app is a little less demanding of accuracy. Mine was a multi-terminal raffle selling setup - server in the back room, one or two fixed PCs and some mobiles (J2ME phones with bluetooth-speaking battery-powered printers) wandering around amongst the patrons. Request/response were the obvious ticket sales (or not), logon/off, operator cash management, etc; the notifications were things like raffle closed, new raffle opening (i.e. clients, come and get the details)
I'd be tempted to write it up - there are some interesting design considerations - except that a lot of the code is Hall of Shame material. 
Cheers,
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
I don't mind reading an article with substandard code in it, as long as it says so while also offering valuable insight in concepts and/or design. And if the code really is horrible, we can always turn it into a "guess who wrote this code" competition in the appropriate forum...
|
|
|
|
|
I believe SQL Server has an event notification process that can inform a client of data change. That is as much as I know, thankfully I have never had to implement such a horrible scenario, to date I have always managed to talk the client out of this based on the cost and perceived (by me) fragility of the solution.
I have implemented a polling solution a long time ago (SQL Server 6.5) but was never really happy with the results.
[edit] have a look through Mika's[^] stuff I think he had some info on it at some stage [/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Luc Pattyn wrote:
How would you implement this?
Depends on the full specs, sensei. If there were no additional concerns, I'd simply have an update-trigger log the primary key and a datestamp in a table, and have the clients poll the table's count every second. That would also enable you to limit the update-logging to specific fields.
Something like an RSS idea for SQL
Bastard Programmer from Hell
|
|
|
|
|
Thanks Eddy. I was hoping for a solution that doesn't poll, something event driven. Maybe an SQL statement that actually blocks until a specified row/field gets modified or added (Yes I could spend an entire connection!).
|
|
|
|
|
My pleasure
An event upon data change still sounds like a trigger. Is CLR Integration on? You could write a trigger[^] in C#.
..and if you can spend a connection, you probably can spend a thread; monitoring a custom lightweight trace might be an option too.
Bastard Programmer from Hell
|
|
|
|
|
Hmm, I'm not familiar with triggers; the way I understand it, it causes some action to occur in the database, however I need other clients to get a notification (not just the one client that is causing the trigger to fire).
A simple scenario would be: client1 is looking at a customers list; client2 (different PC, different user) is looking at the same list; client1 adds/modifies a customer record, client2 should get his display updated, or at least get a warning the data is stale. Of course polling can do that easily, I hope for an event driven solution though.
|
|
|
|
|
Luc Pattyn wrote: Hmm, I'm not familiar with triggers; the way I understand it, it causes some action to occur in the database, however I need other clients to get a notification (not just the one client that is causing the trigger to fire).
A trigger, traditionally, executes a piece of SQL when data is inserted/updated/deleted from a table. You can check *which* fields change in the trigger. MSDN has an example on a CLR Trigger[^] that uses pipes to communicate with the outside world. I'm not sure whether you can launch a socket from there, but once you can catch the change, you can inform your clients - one way or the other.
My client would then typically use the INotifyPropertyChanged interface to propagate changes.
Bastard Programmer from Hell
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
