|
I have a stored procedure that look slike this (it's much bigger, this example is for simplicity):
INSERT INTO Table1 ( Field1,Field2,Field3 )<br />
<br />
SELECT Field1,Field2,Field3 from Table2 where Field4 = @Var
It is grabbing all of the data from one table and importing it into another
here is my problem, i need the proc to be able to tell if the value in Field1 in Table2 already already exists in Table1. If it does, i need it to update table1, otherwise do an insert, as it is new data.
I have seen how to do this with an individual record, but i need to be able to do it with a mass set. the above code currently handles about 29 rows from Table2
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Break this down into a SELECT first:
SELECT Field1, Field2, Field3
FROM Table2
WHERE Field1 NOT IN (SELECT Field1 FROM Table1)
Then merge them together:
INSERT INTO Table1 (Field1, Field2, Field3)
SELECT Field1, Field2, Field3
FROM Table2
WHERE Field1 NOT IN (SELECT Field1 FROM Table1)
The update is a little more problimatic:
UPDATE Table1
SET Field2 = (SELECT Field2 FROM Table2 WHERE Table1.Field1 = Table2.Field2),
SEt Field3 = (SELECT Field3 FROM Table2 WHERE Table1.Field1 = Table2.Field2)
WHERE Table1.Field1 IN (SELECT Field1 FROM Table2)
If you don't want redundant UPDATEs, do the UPDATE call first.
|
|
|
|
|
That is beautiful 
Here's a funny story. One of my coworkers came up with a solution for the problem i was trying to address with the above question. (As it turns out, this would not have worked for it).
However, the exact same stored procedure made changes to 2 other tables, and your code here was the perfect solution for them.
For the first time in almost a week, this damn app is actually working the way it was designed
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Hi, I have a question in regards to oracle database.. I have a oracle database installed on a server which is a linux system. And now I want to store data into the database which is chinese/jap/korean characters. I used INSERT INTO table VALUES ('X'), where X is a chinese character, but when I retrive the data back from the database it returns as a "?". So, I want to know is there any configuration or anything that I need to set in order to store multi byte char into the database?? I used (VARCHAR) in the database. Thanks
|
|
|
|
|
Hi there,
as I not tried with Oracle before yet. But In SQL, if u want to save unicode characters into DB, the query should be something like "values N('X')"
try with this.
<< >>
|
|
|
|
|
I am working with an access application as front end to a sql server database. One of the tables is a picture table with 2 fields, an Id field and an Image field.
J has problems with saving and retrieving bmp files using the stream object.
I would be happy if anyone has a solution.
The 2 sets of codes are:
Private Sub PickupPicture()
Dim db As ADODB.Connection
Set db = CurrentProject.Connection
Dim st As ADODB.Stream
Dim rs As New ADODB.Recordset
Dim SqlString As String
Dim MyPath As String
MyPath = CurrentProject.Path & "\Pictures\Temp.bmp"
SqlString = "SELECT Picture " _
& "FROM tbPicture " _
& "WHERE ((Id)=" & MyId & ");"
rs.Open SqlString, db, adOpenStatic, adLockReadOnly
If Dir(MyPath) <> "" Then
Kill MyPath
End If
Set st = New ADODB.Stream
st.Type = adTypeBinary
st.Open
st.Write rs.Fields("Picture").Value
st.SaveToFile (MyPath) 'full path for bmp file
st.Close
rs.Close
db.Close
UserForm1.Image1.Picture = LoadPicture("")
UserForm1.Image1.Picture = LoadPicture(MyPath)
End Sub
Private Sub SavePicture()
Dim db As ADODB.Connection
Set db = CurrentProject.Connection
Dim st As ADODB.Stream
Dim rs As New ADODB.Recordset
Dim SqlString As String
SqlString = "SELECT * FROM tbPicture;"
rs.Open SqlString, db, adOpenKeyset, adLockOptimistic
rs.AddNew
rs!ID = MinId
rs.Update
rs.Close
SqlString = "SELECT Picture " _
& "FROM tbPicture " _
& "WHERE ((Id)=" & MinId & ");"
rs.Open SqlString, db, adOpenKeyset, adLockOptimistic
Set st = New ADODB.Stream
st.Type = adTypeBinary
st.Open
st.LoadFromFile (Image1.Tag) 'full path for bmp file
rs.Fields("Picture").Value = st.Read
rs.Update
st.Close
rs.Close
db.Close
End Sub
Best regard
Erik Lund
|
|
|
|
|
Hello,
I am writing a client server application using VS2003 and MSDE 2000 Rel A. I've created a setup using a VS deployment project to handle both the installation of clients (GUI only) and server (MSDE + GUI) - the user selects an option using a custom dialog. I've also created a custom action to take care of the creation of the database and tables (if the user selected 'server' in the custom dialog). I would like to know whether this is a suitable place to install MSDE if it isn't already installed? I realise that I could use the MSDE Deployment Toolkit but this means maintaining a different setup project for both clients and servers.
Any advice would be much appreciated!
Many thanks,
Steve.
|
|
|
|
|
I have a table in my database called "country"
In it I have "country_id" and "country_name".
I want to write a query that outputs the countryname in order, but the first two would be out of order:
i.e.
UK
USA
Afghanistan
Albania
Algeria
American Somoa
Andora
...
Zambia
Zimbabwe
I know it's possible but stupidly just can't think how. I'd rather do this by SQL than on my ASP page as there will be a couple of places it will be called from.
Please help!
|
|
|
|
|
Add an extra column to the table for ordering. For each group have an order number, e.g. UK and USA could be order number 0, everything else could be order number 1. Then in your SQL you can do an ORDER BY OrderNumber, CountryName.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Aargh! You beat me by two minutes
I like the 0 and 1 idea. I was thinking 1, 2, 3, 4... but I thought that might take too long.
The man who smiles when things go wrong has thought of someone he can blame it on.
If you tell a man there are 300 billion stars in the universe, he'll believe you. But if you tell him a bench has just been painted, he'll have to touch it to be sure.
|
|
|
|
|
Yeah hadn't thought about doing it that way.
I remember somewhile ago using NULL values in the sql, to be able to play with it, but I've been hammering my head and just can't remember!
Cheers
|
|
|
|
|
You could add a "OrderRank" column where you give your own order (which may take a while with countries) and order by that or you could select the two countries into a table variable and then select the rest of the values except for the UK and US. Then just select everything out of the table variable.
The man who smiles when things go wrong has thought of someone he can blame it on.
If you tell a man there are 300 billion stars in the universe, he'll believe you. But if you tell him a bench has just been painted, he'll have to touch it to be sure.
-- modified at 7:14 Monday 10th October, 2005
|
|
|
|
|
Ok going to be really cheeky.. but what's the basic setup of code?
DECLARE @vCountryName varchar(30), @vCountryName2 varchar(30)
SET @vCountryName = "UK"
SET @vCountryName2 = "USA"
SELECT @vCountryName, @vCountryName2, CountryName
FROM Country
ORDER BY @vCountryName, @vCountryName2, CountryName
I am going to be needing to output it into a single result set.
|
|
|
|
|
A table variable is sort of like a tempory table only it doesn't have as much overhead as a temp table.
Here's an example using Northwind:
DECLARE @vTable TABLE (TID Int, TDesc Varchar(255))
INSERT INTO @vTable
SELECT
TerritoryID,
TerritoryDescription
FROM
Territories
WHERE
TerritoryDescription = 'Denver'
OR TerritoryDescription = 'Edison'
INSERT INTO @vTable
SELECT
TerritoryID,
TerritoryDescription
FROM
Territories
WHERE
TerritoryDescription <> 'Denver'
OR TerritoryDescription <> 'Edison'
ORDER BY TerritoryDescription
SELECT * FROM @vTable
(Sorry about the formatting. I've never been able to get it right  )
Personally I would use Colin's method unless there's no way you can add another column...
The man who smiles when things go wrong has thought of someone he can blame it on.
If you tell a man there are 300 billion stars in the universe, he'll believe you. But if you tell him a bench has just been painted, he'll have to touch it to be sure.
-- modified at 7:52 Monday 10th October, 2005
|
|
|
|
|
Yeah there's a bit more of an overhead with the temp table.
I can put another row in very easily, so will do Colin's route. Was just curious to exactly what you meant.
Thank you very much for your's and Colin's responses though.
Very appreciated.
|
|
|
|
|
You can use union all to achieve this. This is the sybase syntax. I SQL Server 2000 syntax should be the same.
select * from Country where country_name = 'UK' or country_name = 'USA'
union all
select * from Country where country_name <> 'UK' and country_name <> 'USA' order by country_name
|
|
|
|
|
Bingo!!! THANK YOU!!
The final 'order by' on the above in SQL Server, orders the entire rowset. But the above got me thinking, along with Colins original answer of adding a 0, 1 column.
I thought a group by on the individual columns may work, but since my country_id's are all over the place (secondhand data!), that was a no-go. Then thought about adding a 'magic' column to help with the order by.
Finally I've got this, which is exactly what I've been wanting to achieve (n.b. I've got several breakdowns of the UK: United Kingdom (Mainland), United Kingdom (Northern Ireland), etc ):
SELECT country_id, country_name, 0 as seed
FROM country
WHERE country_name LIKE 'United Kingdom%' OR country_name = 'United States'
UNION ALL
SELECT country_id, country_name, 1 as seed from country
WHERE country_name NOT LIKE 'United Kingdom%' AND country_name != 'United States'
ORDER by seed, country_name
I only vaguely remembered that there was a way to do this because I got asked it in an interview earlier in the year, but just couldn't remember how to do it!
Once again THANK YOU!!!!!!!
|
|
|
|
|
There is one more thing. If you want to avoid the seed to be a part of the query you can try the following
select ctm.country_id, ctm.country_name from
(SELECT country_id, country_name, 0 as seed
FROM country
WHERE country_name LIKE 'United Kingdom%' OR country_name = 'United States'
UNION ALL
SELECT country_id, country_name, 1 as seed from country
WHERE country_name NOT LIKE 'United Kingdom%' AND country_name != 'United States'
ORDER by seed, country_name) as ctm
-- modified at 12:32 Monday 10th October, 2005
|
|
|
|
|
Hi all...
I want to find whether a table exists in Access Database or not. I know how to do it in MS SQL Server, we can see the result using Select query from sysobjects table and we have the list of all Tables, SPs, Views, functions...etc. But if my program gets .mdb file i.e. Access Database, my program must find whether a perticular table is created in that database or not.
Is there any way to do it???
Thanx in advance....
|
|
|
|
|
Rohan_bhat_31 wrote: want to find whether a table exists in Access Database or not.
Try this.
SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Name = "tbl_name"
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hey..thanx buddy....
but where this table is stored??? and there must be some log files also generated behind the scene...like SQL Server...???
looking 4ward 2 see ur reply...
|
|
|
|
|
Rohan_bhat_31 wrote: but where this table is stored??? and there must be some log files also generated behind the scene...like SQL Server...???
Access is completely different than SQL Server. An Access database is basically a binary file, so there is no real "table".
There are no log files. No transaction nothing.
I guess the question becomes what are you attempting to do?
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hi,
How to get list of all registered databases on mySQL server?
Millan
|
|
|
|
|
hello, I have a question...how can I delete in MySQL the current row...the one where I am currently positioned? is this possible? Thank you.
|
|
|
|
|
hi
handling transaction in asp .net code behhind provides better performance or handling it in SQL Server's Stored procedures in queries like removing list of an entity?
thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
