How do you take input values from users in T-SQL?
I want to know how to take input from users in T-SQL. For example, how would I do a program which takes two numbers from user and adds them together? ...
sql
Should I include primary key for Audit Table in SQL?
I am creating an audit table for tracking changes done on a record in main table. Here audit table is the exact duplicate of main table (say Employee Table) but will only have 'inserts' for every changes happens in the main table. So it will have duplicates (same EmployeeIDs), so should I add separate Audit_ID for each entry? ...
How different is PostgreSQL to MySQL?
Hiya, I've been asked to support and take on a PostgreSQL app, but am a MySQL guy - is this a realistic task? ...
Sql query with joins between four tables with millions of rows
We have a transact sql statement that queries 4 tables with millions of rows in each. It takes several minutes, even though it has been optimized with indexes and statistics according to TuningAdvisor. The structure of the query is like: SELECT E.EmployeeName , SUM(M.Amount) AS TotalAmount , SUM(B.Amount) AS BudgetAmount ...
Select and filter nvarchar like a int
I have a nvarchar column BigMacs in table McTable in my MS SQL 2005 database with alfanumeric and numeric values. For example: 132 432adfad sfs54543 5256 And now i would like to do something like this: select Convert(BigMacs, int) from McTable where IsNumerc(BigMacs) = 1 AND Convert(BigMacs, int) > 6 But when I do this i get a err...
Why can't I get my CDatabase object to understand my Data Source Name?
I'm using the MFC class CDatabase. To establish a connection to SQL Server, I'm calling OpenEx() with a connection string. My problem is that the object seems unable to interpret the DSN part of the string. The connection string looks like this: ODBC;DSN=mySystemDSN;UID=myUsername;WSID=myMachineName;DATABASE=myDatabaseName;Trusted_C...
Most recent record in a left join
Imagine I have the following 3 tables in SqlServer: Customer (CustomerID, FirstName, LastName) Address (AddressID, CustomerID, Line1, City, State) Product (ProductID, CustomerID, Description) A customer can have multiple delivery addresses and mulitple products. What I would like to do is to list the number of customers for each Stat...
How can I Select the Top and Bottom Record, Per Group?
Say, I have a table in the database (SQL Server 2008) with data similar to this (but much, much bigger): | ID | SCORE | GROUP | ----------------------- | 10 | 1 | A | | 6 | 2 | A | | 3 | 3 | A | |----|-------|-------| | 8 | 5 | B | |----|-------|-------| | 4 | 1 | C | | 9 | 3 | C | |...
How can I merge two MySql tables?
How can I merge two MySql tables that have the same structure? The primary keys of the two tables will clash, so I have take that into account. ...
How can I update a table with data retrieved by a join on itself ?
I have the following data : SectorKey Sector foo 1 A null 2 B null ... ... ... 1 null a 2 null b 2 null c 1 null d 2 null e ... ... ... I want to update column Sector when it's null based on the value of sectorKey, ie I want Sector t...
SQL Query - Along the lines of a pivot table
I have a mock up of a sql query that will represent a real sql query. Create table #tmp ( Atype varchar(10), Btype varchar(10) ) insert into #tmp values ('a','x') insert into #tmp values ('b','x') insert into #tmp values ('a','y') insert into #tmp values ('a','y') insert into #tmp values ('b','z') insert into #tmp values ('b','y') s...
Trigger to capture schema changes in the Server
Is it possible to implement something like the following trigger CREATE TRIGGER [tr_AU_ddl_All_Server] ON DATABASE WITH EXECUTE AS self FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @data XML , @rc INT SET @data = EVENTDATA() EXEC @rc = __AU.dbo.AU_DDLLog @data GO BUT on the whole server. My idea is to cap...
inserting value in to SQL table and use SELECT statement
I am trying to insert values from one table in to the other, with an additonal parameter that is required. For instance: INSERT INTO table1(someInt, someOtherInt, name, someBit) SELECT someInt, someOtherInt, name FROM table2 someBit is not allowed to be null, and I need to set it to False, but I am not quite sure how use it in th...
mysql + update top n
I've got a query like this: update table set status = 1 where status = 2; but I'd only like to do this to the top 400. I tried adding a 'limit 0, 400' (like I would in a query) but that didn't work. I did some searching and mysql doesn't seem to support the TOP(n) command as sql server does. Any idea how I'd do this? edit: fo...
Using SQl Server CE; Possible to Insert Only If Not Exists?
I'm trying to verify a simple 1 field table to determine if a record exists before inserting a duplicate. if not exists (select * from url where url = ...) insert into url... Can someone Help? ...
Coalesce and Pivot in TSQL
I am having trouble figuring out how to coalesce or pivot on a SQL recordset that looks like this: ID VALUE GROUP 3 John 18 4 Smith 18 5 Microsoft 18 3 Randy 21 4 Davis 21 5 IBM 21 etc and I want formatted like this NEWVALUE GROUP Smith, John (Microsft) 18 Davis, Randy (IBM) 21 thanks for any su...
Connection timeout
Hi Guys, Im getting this error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding "Data Source=" + server + ";Initial Catalog=" + database + ";Integrated Security=SSPI;Connection Reset=False;" Connection pooling is default to true and I am closing all connections. Any...
Updates on PIVOTs in SQL Server 2008
Is there a way to perform updates on a PIVOTed table in SQL Server 2008 where the changes propagate back to the source table, assuming there is no aggregation? ...
Find if an SQLException was thrown because of a duplicate
I have a Java program that is agnostic from the database and I need to know, while inserting, if an SQLException was thrown because of a duplicate key. If I was using a single database driver I would simply use the ErrorCode, but since I can be using very different engines the ErrorCode are not the same. Has anyone done this before? An...
Sql Trigger - which table does it belong to?
Is there a way within a Sql Server 2005 Trigger to get the name and schema of the table that the trigger is attached to during execution? ...