|
Is it possible to write an INSERT statement to add a row to a table with just a single column which is an identity column ?
e.g
Create Table Amit (Id int Identity(1,1))
Then what would be my insert statement.
Note:
Please note i don't want to use set auto_identity amit On 
Plase reply....
Amit
|
|
|
|
|
INSERT INTO Amit DEFAULT VALUES
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi I have a MS Access Database locked with password. However i found its password. But Whenever i open it i see the encrypted data. But when i open it in Wordpad it display the Encrypted data with Decrypted data. Please tell me where is the Decrypted data is located in in MDB file. Or is there any possibility to hide the data.
|
|
|
|
|
use "Access Password Recovery" soft can reset your passowrd,and then enjoy it!
to get soft,use the key "ms access password" search google.com 
|
|
|
|
|
i have table contain tow date getdate and expiredDate how can i get the deferent between thes dates
|
|
|
|
|
well if u r using Oracle 9i u can simply subtract the two dates to get the number of days b/w them.
and in SQL Server I think there are some functions like DateDiff()
go chk it out urself... u can use the books online given in the sql server menu. and search over these type of contents or use index or whatever u know what I mean!
Hope it helps...
Rocky
|
|
|
|
|
Hi friends,
Please tell me what is Connection Pooling and Object Pooling.
Thanks in advance.
Thanks and Best Regards,
R. Sangeetha Priya
Prya
|
|
|
|
|
I am analyzing some access database queries and came across this...
UPDATE BCQOH <br />
LEFT JOIN BCQOH_w_Cost ON BCQOH.[2ndItemNumber] = BCQOH_w_Cost.[2ndItemNumber] <br />
SET BCQOH.Cost = [BCQOH_w_Cost]![Cost];
Is the exclamation mean not equal? If so is this just an easier way to write it than a WHERE table1.field NOT EQUAL to table2.field?
|
|
|
|
|
Unless BCQOH.Cost is a boolean field, I would think that its just another form of the '.' operator. In Access the notation used is [table]![field].
Just guessing...
>>>-----> MikeO
|
|
|
|
|
I was playing with SQL the other day and ran across a way to automatically create XML Documents with just T-SQL and Built in funtionality of SQL Server 2005. It goes like this;
SELECT * FROM [Table]
FOR XML AUTO, ELEMENTS
Then you click on the results. A new window opens with your Schema / Data in place.
I thought it was cool, and I hope it helps someone.
|
|
|
|
|
Hi,
I have posted a couple of questions on the forum this week, and no one has been able to help me the way I need help.
I have a database on my local machine, I need to take that exact database and upload it to the hosting server, without loosing any data or PK and FK relations, and I need the Identity Specification to remain the way that I set it.
If I import it to the hosting server then all the Identity Specifications are set to No again. I get huge errors when trying to import the data from my local ASP Membership tables to the hosting servers tables. Because of all the PK and FK constraints.
Please can some one help. I am really frustrated.
I am using SQL Server 2005.
Regards
ma se
|
|
|
|
|
Use Backup & Restore. You will have to establish security since you are changing domain/workstation. See BOL.
|
|
|
|
|
Hello c'pians,
I have got two SQL databases on seperate machines (master/slave). I am able to connect to both the databases using Enterprise Manager. Both databases
have identical tables. What i would like to do is write a trigger which fires when a new row is inserted into the master db and inserts the same data into the slave database.
What i would like to know is, is it possible to write a trigger which can insert data into a completly seperate database?
|
|
|
|
|
It is possible and fairly straight forward. I am doing this from memory as I only have SQL Server 2005 in front of me (so forgive if directions are slightly off). Open Enterprise Manager, create a linked server on the master DB to the slave DB. Then you will be able to access the slave DB in your trigger basically as if you were acessig another (local) DB. I hope this helps.
|
|
|
|
|
Yes, use linked server. Steps would be
a) Create Linked server for your slave on master server by using enterprise mgr or sp_addlinkedserver.
b) Write Trigger on master table...and do somothing like as below
Insert into Slave.Table values(..............)
where slave is linked server name.....
Simple........
Amit
|
|
|
|
|
hey guys... cheers ever so much for this.. I will be looking into this first thing monday morning.
|
|
|
|
|
|
I want to insert data coming from remote server to database(.mdb),but i dont know how to do htis.
Do i need to make connction through odbc?how?
Pls i ahve no idea abt database programing in vc++ so if anyone can help me out..
Thanks.
|
|
|
|
|
well no buddy's gonna tell u the whole story abt database programming out here... but I suggest u should visit www.msdn.com or use msdn at home and check out the articles on ADO or may be ADO .NET and stuff like that.
there are things like datasets, dataadapters, odbc, etc to watch out for
wish u best of luck...
Rocky
|
|
|
|
|
The stored procedure gives me what I want, which is a ranking with points for each store in the company based on certain fields, but I also want to compare this week's vs. the same week last year transactions. I thought I could just do another select command, but it's not working. The same I did use, worked fine on it's own - but when I insert it into the already existing code, it dies... any suggestions would be greatly appreciated.
Signed - newbie 
=====SAMPLE STORED PROCEDURE====
SELECT TransID, StoreNo, StoreName, TotalPoints, TotalTransactions,
RANK() OVER (ORDER BY TotalPoints DESC) AS Ranking
FROM (
-- SubQuery Starts here
select WeeklyReports.TransID, WeeklyReports.StoreNo, Stores.StoreName,
-- Calculate TotalPoints first
rank() over (order by abs(WeeklyReports.ActualVsTheoFoodCost) desc)+
rank() over (order by abs(WeeklyReports.Cash) desc)+
rank() over (order by WeeklyReports.SalesVsBudgetPercent)+
rank() over (order by WeeklyReports.Overtime desc)+
rank() over (order by WeeklyReports.Deletes desc)+
rank() over (order by WeeklyReports.Puw112 desc)+
rank() over (order by WeeklyReports.Puw58 desc)+
rank() over (order by WeeklyReports.PuwDay desc)+
rank() over (order by ROUND((WeeklyReports.Voids/WeeklyReports.NetSales)*100,2) desc)+
rank() over (order by ROUND((WeeklyReports.AllVoids/WeeklyReports.NetSales)*100,2) desc) as TotalPoints,
WeeklyReports.TotalTransactions,
This is where I run into problems. If I delete this section, the stored procedure works find, but I would really like it to work.
<br />
--SELECT DISTINCT WeeklyReports.TransID, WeeklyReports.StoreNo, WeeklyReports.WeekEndDate, WeeklyReports.TotalTransactions, <br />
-- WeeklyReportsLY.WeekEndDate AS LastYearDate, WeeklyReportsLY.TotalTransactions AS LastYearTransactions, <br />
-- (WeeklyReports.TotalTransactions - WeeklyReportsLY.TotalTransactions) AS TransPM<br />
--FROM WeeklyReports INNER JOIN<br />
-- WeeklyReports AS WeeklyReportsLY ON DATEADD(week, - 52, WeeklyReports.WeekEndDate) = WeeklyReportsLY.WeekEndDate<br />
--WHERE (WeeklyReports.TotalTransactions is not null and WeeklyReports.TotalTransactions <> 0 and<br />
-- WeeklyReportsLY.TotalTransactions is not null and WeeklyReportsLY.TotalTransactions <> 0)<br />
And this is the continued part of the good procedure.
abs(WeeklyReports.ActualVsTheoFoodCost) as FoodCostVariance,
rank() over (order by abs(WeeklyReports.ActualVsTheoFoodCost) desc) as ptsFoodCostVariance,
abs(WeeklyReports.Cash) as Cash,
rank() over (order by abs(WeeklyReports.Cash) desc) as ptsCash,
WeeklyReports.SalesVsBudgetPercent as SalesVsBudgetPct,
rank() over (order by WeeklyReports.SalesVsBudgetPercent) as ptsSalesVsBudget,
WeeklyReports.Overtime,
rank() over (order by WeeklyReports.Overtime desc) as ptsOT,
WeeklyReports.Deletes,
rank() over (order by WeeklyReports.Deletes desc) as ptsDeletes,
WeeklyReports.Puw112,
rank() over (order by WeeklyReports.Puw112 desc) as ptsPuw112,
WeeklyReports.Puw58,
rank() over (order by WeeklyReports.Puw58 desc) as ptsPuw58,
WeeklyReports.PuwDay,
rank() over (order by WeeklyReports.PuwDay desc) as ptsPuwDay,
ROUND((WeeklyReports.Voids/WeeklyReports.NetSales)*100,2) as VoidPct,
rank() over (order by ROUND((WeeklyReports.Voids/WeeklyReports.NetSales)*100,2) desc) as ptsVoidPct,
ROUND((WeeklyReports.AllVoids/WeeklyReports.NetSales)*100,2) as AllVoidPct,
rank() over (order by ROUND((WeeklyReports.AllVoids/WeeklyReports.NetSales)*100,2) desc) as ptsAllVoidPct,
MAX(WeeklyReports.WeekEndDate) AS LastWeekEndDate, WeeklyReports.WeekEndDate
from WeeklyReports
inner join Stores on WeeklyReports.StoreNo = Stores.StoreNo
group by WeeklyReports.WeekEndDate, Stores.StoreName, WeeklyReports.TransID,
WeeklyReports.StoreNo, WeeklyReports.SalesVsBudgetPercent,
ABS(WeeklyReports.ActualVsTheoFoodCost), abs(WeeklyReports.Cash),
WeeklyReports.Overtime, WeeklyReports.Deletes,
WeeklyReports.Puw112, WeeklyReports.Puw58, WeeklyReports.PuwDay,
ROUND((WeeklyReports.Voids/WeeklyReports.NetSales)*100,2),
ROUND((WeeklyReports.AllVoids/WeeklyReports.NetSales)*100,2),
WeeklyReports.TotalTransactions
having (WeeklyReports.WeekEndDate =
(select MAX(WeekEndDate) as LastWeekEndingDate from WeeklyReports as WR2))
) as WeeklyStoreRanking
-- SubQuery ends here
GROUP BY StoreNo, StoreName, TotalPoints, TransID, TotalTransactions
ORDER BY TotalPoints DESC
===END SAMPLE STORED PROCEDUE===
-rngd
|
|
|
|
|
I am working on combining two tables(tbl1,tbl2) from one database into one table(tbl3) in a seperate database. tbl1 and tbl2 have the same column names as tbl3 but they lack a location ID column which will be used in tbl3.
Currently I can get the information transferred over to tbl3 via SELECT * INTO tbl3 FROM tbl1--tbl2, tbl1 and tbl2 both have the same primary key column but will have different locationID columns in the new table. I'm pretty sure I will have to use the SET IDENTITY_INSERT ON for tbl3 but how can I keep track of which table the data is coming from? Also how can I set the location ID, as this coincides with the primary field.
<modify> I am trying to do all of this in a stored procedure. I was thinking of setting the tbl2 ID values to '100 + originalValue' as both tables currently have the same values in the ID column.
Thanks in advance for any help.
-- modified at 17:53 Thursday 5th October, 2006
A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder<br\> An American football fan -
Go Seahawks!
Lil Turtle
|
|
|
|
|
Add a new field to tbl3, say, Source varchar(10)
Insert Into tbl3
Select *, 'tbl1' /* Assuming Source field is the last field */
From tbl1
Insert Into tbl3
Select *, 'tbl2' /* Assuming Source field is the last field */
From tbl2
Farhan Noor Qureshi
|
|
|
|
|
I'm trying to find a good query tool; something I can type SQL into, preferably with a Query-By-Example grid. I'm connecting to several database types including Teradata, DB2, Sybase, and SQL Server 2005 (all through ODBC).
Currently I'm using MS Access 2003--I cannot believe this is the ultimate front-end query tool. Any recommendations?
--
"stop thoughting; start thinking, and quit trying to predict unpredictable thinking"
- dMoney (2005)
|
|
|
|
|
|
Hi gurus,
I've created a few database programs using Access' mdb in the past. Now that I got a bigger project that may handles about 10 user (and more data in the same time), I am considering using a better solution. The problems I have with Access database are:
1. The password can be cracked quite easily.
2. It gets less responsive as the file get larger (and more likely to get corrupted, I heard).
3. It takes some time to save changes to the file, creating bottleneck and (sometimes) non-updated data for other users (I think this is the downside of file-based database, as processing is handled by each client).
However, what I like about it are:
1. All in one file, so it's easier to backup.
2. Password not linked to windows, so (supposedly) only I who can open it.
3. Free.
At first, I am considering using SQL Express 2005, but since it links the security to Windows login, I can't prevent users from opening the database (since the program I am creating will be run under Admin login).
So, I am asking for advice: what database should I use now? I have no experience with other database other than Access. I don't need fancy bells and whistles. Preferably:
1. Password is not linked to windows. So only I and the program who know the password.
2. Server-client type (processing is handled by the server machine), so that changes are saved instantly and network traffic can be reduced greatly.
3. Can handle at least 5 users concurrently.
4. Can be backed up easily.
5. Free (this might not be possible?
Thanks in advance for any input, it will be greatly appreciated.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
