|
Start here[^]. Should help.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
I want to import data from excel to sql server 2005. I am using C# for this purpose. I successfully exported the data of general format
to varchar. But, the problem arises while exporting the data of date type.
I have used date format in excel and want to export it in sql server 2005's datetime field.
The code that I have used is:-
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "Data_Master_Inventory";
sqlBulk.ColumnMappings.Add("VendorRegistrationNo", "VendorRegistrationNo");
sqlBulk.ColumnMappings.Add("ProductCode", "ProductCode");
sqlBulk.ColumnMappings.Add("SerialNo", "SerialNo");
sqlBulk.ColumnMappings.Add("VendorProvidedSerialNo", "VendorProvidedSerialNo");
sqlBulk.ColumnMappings.Add("ModelName", "ModelName");
sqlBulk.ColumnMappings.Add("ProductCategoryCode", "ProductCategoryCode");
sqlBulk.ColumnMappings.Add("InventoryDate", "InventoryDate");
sqlBulk.WriteToServer(dReader);
Please provide me necessary solution.
|
|
|
|
|
|
sir
i have installed the sql server 2008 developer edition on windows xp3 successfully,
,along with reporting services. and i have configure reporting services configuration manager through wizard and completed successfully and the services are running ie sql server reporting services(MSSQLSERVER) state is running.
but when i goes from start---> all programs---> microsoft sqlserver2008 iam not getting sql server business intelligence. how to get can you help me please
|
|
|
|
|
i have project with with database file in sql 2000 this is library.sql.how can i open this file.
|
|
|
|
|
You can open it in Notepad or Visual Studio or SQL Server Management Studio at least. Right-click and go Open With. If you don't know this really basic stuff...
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
I am a student of SQL Server. I have been using the SQL Server 2008R2 Enterprise trial version. The trial version has now expired. I just purchased the SQL Server 2008R2 developers version for $50.00. My question is should I install the Developers version over the trial version or try and delete the trial version first before installing the Developers version. 
|
|
|
|
|
I would remove the enterprise version. SQL Server has a history of problems with installed versions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It is better to backup your entire existing databases and uninstall the expired version. Then install the new one with the licence key.
Wonde Tadesse
MCTS
|
|
|
|
|
Hi All,
I want to update multiple columns of multiple rows of one table. Single/Multiple column update for single/Multiple row is quite easy but i want to update the rows of one table on the basis of rows of other table.
Here I demonstrate that what i want with single query
Table1 - History
Fields and Data
Date SId Country Price High Low ........
1 Dec 1 US 2 3 1 values...
1 Dec 2 US 3 4 2 values...
1 Dec 3 US 4 5 3 values...
1 Dec 4 US 5 6 4 values...
2 Dec 1 US 7 8 5 values...
2 Dec 2 US 8 9 6 values...
2 Dec 3 US 9 10 7 values...
2 Dec 4 US 10 11 8 values...
Table2 _ LatestPrice
Fields and Data
SId Country Price High Low ........
1 US 1 2 1 values...
2 US 1 2 1 values...
3 US 1 2 1 values...
4 US 1 2 1 values...
I want to update the LatestPrice table data on the base of field SID and Country from History table data.Here i pick the only highest date data from History table. Now i want to update data in LatestPrice table. so there are two approaches
One way is the pick one by one record from resultset and update the LatestPrice on the base of field SID and Country. so there are mulitple queries depends on the no. of records in resultset
Other way is write singe query in this way that they find automatically SId and Country and update the data.
So i was unable to write single query for update the Data.
Please suggest that how can i do this taks
any help will be appreciated
modified 3-Dec-11 6:52am.
|
|
|
|
|
you can try the following syntax
Update LatestPrice set price = LatestPricesFromHistory.price,...other columns From
(
here goes query to find highest date data from History table
) as LatestPricesFromHistory
where LatestPrice.SID = LatestPricesFromHistory.SID and LatestPrice.Country = LatestPricesFromHistory.Country
"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
modified 4-Dec-11 23:11pm.
|
|
|
|
|
Hi Varsha,
i tried as following
UPDATE LatestPrice SET price = LatestPricesFromHistory.price,
FROM ( SELECT *
FROM history
WHERE Date1 = (
SELECT max( Date1 )
FROM history ) ) AS LatestPricesFromHistory
WHERE LatestPrice.Sid = LatestPricesFromHistory.SId and LatestPrice.Country = LatestPricesFromHistory.Country
but it give me following error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'From { select * from history where Date1=(select max(Date1) from history' at line 1.
Please suggest
|
|
|
|
|
I think MySQL does not support Update-Select.
I have used this syntax in SQL server 2005.
try this one for MySQL :
Update LatestPrice,
( Your Query for finding Latest Prices goes here) as LatestPriceFromHistory
Set price = LatestPriceFromHistory.price
where LatestPrice.SID=LatestPriceFromHistory.SID and LatestPrice.Country = LatestPriceFromHistory.Country
"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
|
|
|
|
|
Hi Varsha,
i update the query as you suggested. and following is the original query
UPDATE issdetails_new a,<br />
(<br />
SELECT secid, country, PriceDate,<br />
OPEN , High, Low, Close, Ask, midval, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades,<br />
COMMENT , LocalCode<br />
FROM hist_prices<br />
WHERE MktCloseDate = (<br />
SELECT max( MktCloseDate )<br />
FROM hist_prices )<br />
) AS b<br />
SET a.PriceDate = b.PriceDate,<br />
a.Open = b.Open,<br />
a.High = b.High,<br />
a.Low = b.Low,<br />
a.Close = b.Close,<br />
a.Midval = b.Midval,<br />
a.Ask = b.Ask,<br />
a.Last = b.Last,<br />
a.Bid = b.Bid,<br />
a.BidSize = b.BidSize,<br />
a.AskSize = b.AskSize,<br />
a.TradedVolume = b.TradedVolume,<br />
a.MktCloseDate = b.MktCloseDate,<br />
a.Volflag = b.Volflag,<br />
a.TradedValue = b.TradedValue,<br />
a.TotalTrades = b.TotalTrades,<br />
a.Comment = b.Comment,<br />
a.LocalCode = b.LocalCode WHERE a.SecId = b.SecId AND a.Country = b.Country
Its works well. Thanks a lot
but there are one more problem
in table issdetails, there are 9500 records and in table hist_prices there are 41,50,000 records so it takes 11 -13 minute for update the table issdetails. Any option to reduce the execution time ? Please suggest
|
|
|
|
|
Sorry I don't know much about MySQL, but you can google for "Indexing in MySQL".
"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
|
|
|
|
|
Thanks a ton Varsha
Your suggestion works well Now It takes only 8 - 9 seconds
|
|
|
|
|
The typeface of the font used in your name is annoying.
Bastard Programmer from Hell 
|
|
|
|
|
Sorry... I changed it 
Thanks for the suggestion.
"Insanity is doing the same thing over and over again but expecting different results.” — Rita Mae Brown
|
|
|
|
|
I'll probably take some heat for suggesting a Merge, but as I assume there might be new SIds and countries appearing in the history table...
MERGE INTO LatestPrice lp
USING (
SELECT Max(DATE),SId,Country,Price,High,Low
FROM history h
GROUP BY SId,Country,Price,High,Low
ON lp.SId = h.SId AND lp.Country = h.Country
WHEN MATCHED THEN UPDATE
SET lp.Price = h.Price
,lp.High = h.High
,lp.Low = h.Low
WHEN NOT MATCHED THEN
INSERT (lp.sid,lp.Country,lp.Price,lp.High,lp.Low)
VALUES (h.SId,h.Country,h.Price,h.High,h.Low)Another approach is ofcourse to not have a separate table for the LatestPrice, but a view. Which of course might have performance issues.
CREATE OR REPLACE VIEW LatestPrice (lastdate,SId,Country,Price,High,Low)
AS (
SELECT Max(DATE) lastdate,SId,Country,Price,High,Low
FROM history h
GROUP BY SId,Country,Price,High,Low
)
Edit: changed the view.
modified 2-Dec-11 13:59pm.
|
|
|
|
|
thanks a lot for you suggestion.
View option is not applicable for my requirement. since I need to update the data in table.
Also i tried the Merge option as you suggested but with a little modification and it is the original query
MERGE INTO issdetails_new lp USING ( SELECT Max( MktCloseDate ) , secid,country, PriceDate,<br />
OPEN , High, Low, Close, Ask, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades,<br />
COMMENT , LocalCode<br />
FROM hist_prices<br />
GROUP BY secid, PriceDate,<br />
OPEN , High, Low, Close, Ask, Last, Bid, BidSize, AskSize, TradedVolume, MktCloseDate, Volflag, TradedValue, TotalTrades,<br />
COMMENT , LocalCode ) AS h ON (lp.Secid = h.Secid and lp.country=h.country)<br />
WHEN MATCHED<br />
THEN UPDATE SET lp.PriceDate = h.PriceDate,<br />
lp.open = h.open,<br />
lp.Low = h.Low,<br />
lp.high = h.high,<br />
lp.close = h.close,<br />
lp.ask = h.ask,<br />
lp.last = h.last,<br />
lp.bid = h.bid,<br />
lp.bidsize = h.bidsize,<br />
lp.asksize = h.asksize,<br />
lp.TradedVolume = h.TradedVolume,<br />
lp.MktCloseDate = h.MktCloseDate,<br />
lp.Volflag = h.Volflag,<br />
lp.TradedValue = h.TradedValue,<br />
lp.TotalTrades = h.TotalTrades,<br />
lp.Comment = h.Comment,<br />
lp.LocalCode = h.LocalCode
but it gives me following error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MERGE INTO issdetails_new lp USING ( SELECT Max(MktCloseDate),secid,Pric' at line 1
Please suggest where i am doing wrong.
|
|
|
|
|
That's not a very descriptive error message. And my experience with mysql is none.
But I can see one error in the USING query, you have "Country" in the SELECT but not in the GROUP BY.
|
|
|
|
|
HI Jorgen,
I think this is not the cause for error. since it only not include the country in Grouping.
|
|
|
|
|
You're right, it seems that the problem is that MySQL isn't supporting the Merge command.
But no problem, they have one of their own instead[^].
|
|
|
|
|
HI Jorgen,
My problem is solved. please check this thread.
Thanks for suggest new way of update and Insert. I was not aware of this way.
|
|
|
|
|
Difference between varchar and Nvarchar?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
