|
Everyone can[^].
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.3
|
|
|
|
|
Depends on what Database you're targetting.
|
|
|
|
|
I have two tables that are identical, 1 table has old data and the other new data.
I want to copy the old to the new where the records are either blank or null is there an easy way I can do this.
Any help is appreciated.
Thanks in advance,
Michael
|
|
|
|
|
Which column is unique on both tables?
Syntax is like this:
Insert into table2 (col1,col2,col3)
select col1,col2,col3 from table1
In this select statement you have to include where clause with unique column in table1 and tale2
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.cacttus.com
|
|
|
|
|
Are they in the same database? Are they on the same server? Are they the same brand of database?
When I had to copy data from an Ingres database on a Unix system to an SQL Server database on Windows there were times I had to simply throw the records at the database one by one and catch-log-and-ignore any duplicate Exceptions encountered. Simple but effective, and quicker than trying to test each record first.
|
|
|
|
|
They are in the same DB on the same server.
I copy all the old records to a similar table
Delete the orig table's records
Start with a blank table, this is a starting point to build other info, I do not want many of the records from the old included which I do not know until I start new.
Add new data
Then I want to insert old data where new data is left blank and do not wish to overwrite new data.
The previous response said the following:
Insert into table2 (col1,col2,col3)
select col1,col2,col3 from table1
I am thinking if I used the above then added
Where col1 IS LIKE '' or col1 IS NULL
but I would have to do this for every column, I was hoping there would be a quick easy way
Michael
|
|
|
|
|
Might help to know which database vendor you are referring to.
Easy is relative.
You can do an update statement with a 'case' for each column.
|
|
|
|
|
SQL Server supports a rather convenient MERGE[^] command that lets you specify criteria of two records being 'identical', actions to take on finding a match, and actions to take on finding missing records. The documentation at the link supplies several large examples, so you should be able to construct your statement relatively easily.
|
|
|
|
|
I am getting an error message in an SQL statement that I don't understand
This is the statement:
UPDATE [QVSData].[dbo].[tbl_CentCatalogQVS]
SET [Energy] = (POWER(10,(4.8+([Magnitude]*1.5))))/POWER(10,3)
WHERE [AutoID] = 1
This is just a test hence restricted to 1 line to see if it works. It does not, and I am scratching my head as to why not.
This is the error:
Arithmetic overflow error for type int, value = 1995262314968882.700000
There is no int in the table. The affected fields here are:
The Mag field is a decimal field (10,4) with in this instance a value of 7.0000
The energy field is a decimal field (20,1)
Anyone have any ideas as to why this error is appearing?
Actually I make that value 1995262314968.8 so it seems that it is possibly falling over as it calculates the first part before doing the division to get to Kilojoules. Why is it using int?
To make it easier to figure out this error occurs if you run this:
DECLARE @mag decimal(10,4)
DECLARE @energy decimal(20,1)
SET @mag = 7
SET @energy = (POWER(10,(4.8+(@mag*1.5))))/POWER(10,3)
SELECT @energy
The you get:
Msg 232, Level 16, State 3, Line 5
Arithmetic overflow error for type int, value = 1995262314968882.700000.
(1 row(s) affected)
And the value is NULL
Sorry forgot to add: SQL Server 2005 Developer Edition
|
|
|
|
|
Yes I am replying to myself!
This fixed it
DECLARE @mag decimal(10,4)
DECLARE @energy decimal(20,1)
DECLARE @log float
SET @log = 10
SET @mag = 7
SET @energy = (POWER(@log,(4.8+(@mag*1.5))))/POWER(@log,3)
SELECT @energy
|
|
|
|
|
From your subject line I thought don't like people in positions of power/government/ etc 
|
|
|
|
|
Dividing by 10^3 is the same as subtracting 3 from 4.8+(@mag*1.5), so you should be able to avoid overflow if you rewrite your expression as follows:POWER(10,(1.8+(@mag*1.5))).
|
|
|
|
|
Thank you. Seems a good scheme. I have absolutely no idea how you came up with that. Maths is not one of my strong points!
|
|
|
|
|
|
We live and learn and live to learn another day! Thanks again. Once I have uncrossed by eyes I will see if I can assimilate any of that and become 1 of 10^666
Neat PDF that on Wolfram. Devilishly cunning stuff!
|
|
|
|
|
I found the following sp on this website, when I run by right clicking on the procedure using the command "Execute Stored Procedure" it works fine. However, when I run the sp from C# I get the following error:
//err.Message = "Microsoft SQL Native Client:
//CONFIG statement cannot be used inside a user transaction.\\42000 = 574;
//Microsoft SQL Native Client: Configuration option 'show advanced options'
//changed from 0 to 1. Run the RECONFIGURE statement to install.\\01000 = 15457"
ALTER PROCEDURE [dbo].[sp_Database_ResetMemory]
AS
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max server memory (MB)';
EXEC sp_configure 'max server memory (MB)', 512;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max server memory (MB)';
WAITFOR DELAY '00:00:15';
EXEC sp_configure 'max server memory (MB)', 2147483647;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max server memory (MB)';
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;
Can anyone tell me what the problem is.
Thanks in advance,
Michael
modified on Thursday, May 26, 2011 12:50 PM
|
|
|
|
|
My ex-colleague came across similar message. Not sure about this, but if sql server is running in mixed authentication mode and you are using sql authentication, this must have occurred.
// ♫ 99 little bugs in the code,
// 99 bugs in the code
// We fix a bug, compile it again
// 101 little bugs in the code ♫
|
|
|
|
|
|
I'm using c# and trying to access oracle
here's my code:
try
{
String connString = "Provider=MSDAORA;Data Source=127.0.0.1;User id=system;Password=akar;";
string query = "insert into test values(" + textBox2.Text + ", `" + textBox3.Text + "`, `" + textBox4.Text + "`);";
MessageBox.Show(query);
OleDbConnection myConnection = new OleDbConnection(connString);
OleDbCommand myCommand = new OleDbCommand(query, myConnection);
myConnection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();
}
catch (Exception ex)
{
MessageBox.Show(ex.StackTrace);
}
That code works just fine when I use select, but when I change it into insert it throws an ORA-00911 Invalid Character error at this code OleDbDataReader myReader = myCommand.ExecuteReader();
Can anyone help me, please?
|
|
|
|
|
Is it because you have copied and pasted your code from some kind of word processing program, and your single quotes are special characters rather than standard single quotes?
|
|
|
|
|
well, I've tried using "'" but it doesn't work either
|
|
|
|
|
Oh, hang on... you haven't listed the fields for your insert into...
insert into test (field1, field2, field3) values (...);
|
|
|
|
|
Here, I've changed my code into this:
string query = "insert into test(no, name, dob) values(" + textBox2.Text + ", '" + textBox3.Text + "', '" + textBox4.Text + "');";
But I still got the error
-ps:column no is int others are varchar
A hidden needle is way more effective than an unsheathed sword.
That is, in the hand of professionals.
What about you?
Just pray your enemies are blind
|
|
|
|
|
Hmmm... have you tried executing your sql directly to check that it's working as you expect?
|
|
|
|
|
yep, it works in sql
-ps:fyi, that code works when a button is clicked. after I've changed it into showing the query it shows this insert into test(no, name, dob) values(23, 'sawqwd', '322');
A hidden needle is way more effective than an unsheathed sword.
That is, in the hand of professionals.
What about you?
Just pray your enemies are blind
|
|
|
|
|
Well, if the code works when the button is clicked, and the sql works if you copy/paste it into Oracle and run it directly, there must be an error in the calling of the querydef...
Do you have the correct syntax, parameters, names etc?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
