|
add:
Where dbo.Table_2.Name = 'James2' ?
Scott
|
|
|
|
|
Do a left join on the name and filter where table2.name is null
Select *
From Table1 T
Left Join Table2 T2 on T.Name = T2.Name
Where T2.Name is NULL
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi all,
I am looking at ways to automate the process of getting data from SQL Server 2008 into an Excel (2007) spreadsheet. I would like to set the value of a cell in the spreadsheet to the result of a SELECT COUNT query.
Is this possible?
Regards,
Anthony
modified on Thursday, August 26, 2010 5:21 AM
|
|
|
|
|
anthasaurus wrote: Is this possible?
Yup. Start here[^]
I are Troll
|
|
|
|
|
Greetings,
Apologies if this is a dumb question but I'm afraid my SQL knowledge is a little limited. Anyway I am using SQLCE as I don't need anything bigger at the moment but I have a problem when processing the following command
INSERT INTO [Contact]
([Name]
,[Tel]
,[Mobile]
,[Email])
VALUES
(N'Name'
,N'Tel'
,N'Mobile'
,N'Email');
SELECT @@IDENTITY AS ContactID
My problem is that although this functions correctly when issued by hand, my encapsulation of OLEDB only seems to permit a single command. Now this is the only occasion when I need to issue multiple commands so it seems a shame to have to mess up my existing code just for this instance.
So can anyone suggest how I might merge the above into a single command that INSERTs and gives me the ID of the inserted data?
Or is there a simple way with OLEDB & MFC that I can process multiple recordsets without adding too much overhead.
Any thoughts or suggestions would be appreciated.
Many thanks
Alan
|
|
|
|
|
Here is what I've done in the past ...
SQLtext = "Insert .... blah blah
Select scope_identity()"
' This returned the identity column value that was just created.
ID = sqlCmd.ExecuteScalar()
|
|
|
|
|
It should be possible as two separate commands, each encapsulated in their own SqlCeCommand class. An ExecuteScalar on the second select, re-using the connection of the previous command.
I are Troll
|
|
|
|
|
Sorry if this seems a dumb question but could this be integrated into the ACCESSOR class mechanism I am using at present? I have geared everything to use accessor classes which is working fine for everything but the INSERT operations.
It is just annoying because I know the insert is working but it does not give me back the ID of the item that was inserting. So when I came across the @@IDENTITY I thought I was sorted and as the INSERT does not return any results it looked like I could bunch the commands into the same accessor. Life is never as simple as I expected it to be
Alan
|
|
|
|
|
Spawn@Melmac wrote: could this be integrated into the ACCESSOR class mechanism I am using at present?
I have no idea what an Accessor is; a bit of explanation and some code would be nice.
Spawn@Melmac wrote: So when I came across the @@IDENTITY I thought I was sorted
That should return the last identity, and it should be possible to execute two SqlCeCommand [^] consecutively.
Something similar to the code below;
using (var con = new SqlCeConnection(connectionString))
using (var cmd = new SqlCeCommand())
{
con.Open();
cmd.Connection = con;
cmd.CommandText = "INSERT ...";
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText = "SELECT @@IDENTITY";
object recordId = cmd.ExecuteScalar();
}
How does an Accessor work? Is it used to execute query's?
I are Troll
|
|
|
|
|
Ok here you go but I should warn you I don't fully understand it myself.
I think of an accessor as a wrapper to the SQL like this (which is an extract from working code)...
class CDBHubDetails
{
public:
TCHAR f_LocSub[255];
int f_HubID;
BEGIN_COLUMN_MAP(CDBHubDetails)
COLUMN_ENTRY(1, f_LocSub)
END_COLUMN_MAP()
BEGIN_PARAM_MAP(CDBHubDetails)
SET_PARAM_TYPE(DBPARAMIO_INPUT)
COLUMN_ENTRY(1, f_HubID)
END_PARAM_MAP()
DEFINE_COMMAND_EX(CDBHubDetails, L" \
SELECT Hub.SubLoc \
FROM Location \
WHERE Hub.id = ?")
};
Now this get's used as follows
CCommand<CAccessor<CDBHubDetails > > rs;
rs.f_HubID = pHub->m_iHubID;
hr = rs.Open(m_oDB->session);
where I can then walk the recordset.
The thing is I have not figured out how to process the additional SELECT command I need to include in the INSERT operation so I can retrieve the ID of the item inserted.
Now I know I am being both optemistic, and lazy, but I was hoping that as the INSERT does not return a recordset itself, the SELECT @@IDENTITY would become the result. Silly me...
The responses thus far have given me some ideas but finding examples I can learn from is proving difficult. MSDN seems devoid of C++ examples in the documentation (although F# is there!).
Thank you for taking the time to look at this for me.
Alan
|
|
|
|
|
Doesn't ring a bell, I'm afraid. Still, executing both commands one after another should do the trick - without a need to combine them. @@IDENTITY should hold it's value until the next INSERT -statement is issued.
I are Troll
|
|
|
|
|
Dear,
I'm looking for a good comparison between ODP.Net and OleDb for Oracle.
So far on google I only found contradictory information on forums where one claims that ODP is faster while the other claims the opposite.
Why would you use ODP over OleDb or vice versa?
Additionally I know that OleDb uses the MDAC (now Windows DAC) layer to talk to the database (correct?), is this also the case for ODP?
many thanks.
V.
|
|
|
|
|
ODP is good. Unless you have differences between your development and production computer. ODP is different for 32 and 64 bit for some weird reason.
Other problem with ODP is that you have to dispose everything and I really mean everything you have created or your server would be down after some duration.
Other than that, I believe it is fine. I do not have a material to read though. This is just by the personal experience of.
|
|
|
|
|
d@nish wrote: Other problem with ODP is that you have to dispose everything and I really mean everything you have created or your server would be down after some duration.
What do you mean exactly, clean up the resources?
V.
|
|
|
|
|
ODP uses a lot of unmanaged code so unless you dispose every object, they will be lying around like forever.
Make sure you dispose every ODP object. For connection, close and then dispose. For command, dispose each parameter explicitly.
|
|
|
|
|
How do we design/plan for using in-memory data to modify and search extensively? what are it's pros cons?
|
|
|
|
|
FEMDEV wrote: what are it's pros cons?
Stating the obvious first; memory is faster, but volatile. Loose the power, you loose the data.
What kind of data? Are you talking about a typical database, flat files, a folder-hierarchy filled with Word-documents and indexed with Google Desktop Search, or something completely different?
In all cases, I'd say that you don't want to move your entire database to a RAM-disk. If you "must", then move only the most frequent read data there - providing an extra cache-layer should prove to be easier than having a buffer in memory that needs be synchronized to disk.
Now that's all been said, have a look at Velocity[^]
I are Troll
|
|
|
|
|
Hi,
How do you write a SQL statement to update records if the count of a field is greater than 1?
Thanks
|
|
|
|
|
update mytable set column='value'
where id in (select id from mytable group by id having (count(id)>1))
Hope it will help you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Somebody has been kind enough to foresee such question; have a look here[^].
FYI: I found the link by Googling SQL update
EDIT
unless count isn't the name of a field, and you intended to count some rows and use that as a selection criterium... Then look at the other reply.
/EDIT
|
|
|
|
|
try something like this ...
Update myItems<br />
set myField = 'more than 1 count'<br />
where item_class in <br />
(<br />
select item_class<br />
from myItems<br />
group by item_class<br />
having count(*) > 1<br />
)
|
|
|
|
|
Thanks guys. Just what I was looking for.
|
|
|
|
|
Then mark as answer.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Hi,
I have a large varbinary field in one of my tables, and I would like to download in parts for show a download progress indicator in my application.
How can I split the data sent in a SELECT query?
Thanks
|
|
|
|