Hello all, I want to do some calculations when my table data is changed. However, I am updating my table manually and copy pasting about 3000 rows in once. That makes my trigger work 3000 times, but I want it to do the trigger only once. Is there a way to do that ? Thanks. ...
Possible Duplicates: Parameterizing a SQL IN clause? SQL Server SP - Pass parameter for IN array list? I need to search for a haphazard set of integers on two different tables: SELECT col_1, col_2 FROM LIKES_NUMBERS WHERE col_1 IN (1,2,3,5,7,1021,10041411) SELECT col_one, col_two FROM LIKES_NAMES WHERE ...
My question is pretty simple but I just couldn't find the setting to change this. I am using Visual Studio 2008 as my SQL stored procedure editor. After I switched to Window 7, the SQL style in VS2008 changed and is now all over the place compare with the styles I used to have in Window XP VS2008. It shouldn't be the Operating System pro...
I have a table that has a newid() for the primary key field (Id) in the stored procedure i need to return this newly created guid back as a return parameter, i could create the guid beforehand and insert into the database, but that would defeat the purpose of a newid(). Is there a good way to handle this? INSERT INTO [SUBS].[dbo].[Sub...
Hello all, I have a big query and I am tring to improve it part by part, however due to the caching mechanism, and simplicity of t-sql codes, I don't have a reliable environment for testing the speeds. The queries that I am tring to improve about speeds are all last about 1 or 2 seconds, so I can't see the difference clearly. And creati...
I need to change the values of a PK/FK (add 10000) on 2 tables. How do I tell the two tables involved that they should not care about referential integrity during the update, but to care after. I don't want to have to drop and recreate the relationships if I don’t have to. ...
I'm updating tables with millions of records and I need to be as efficient as possible. Is there a point at which adding more criteria to the where clause will actually hurt rather than help? For example, if know I want to set a column to 3 I could use this query: update mytable set col = 3 Or I could update the record only if it's d...
Please look at following image, I have explained my requirements in the image. I can't use here WHERE UsageTypeid IN(1,2,3,4) because this will behave as an OR condition and fetch all records. I just want those records, of first table, which are attached with all 4 ShipmentToID . All others which are attached with 3 or less Shipmen...
Hi all, I want to create a table trigger for insert and update. How can I get the values of the current record that is inserted/updated? ...
Hi all, I am looking for a way to call a stored procedure for each record of a select statement. SELECT @SomeIds = ( SELECT spro.Id FROM SomeTable as spro INNER JOIN [Address] addr ON addr.Id = spro.Id INNER JOIN City cty ON cty.CityId = addr.CityId WHERE cty.CityId = @CityId ) WHILE @SomeIds IS NOT NULL BEGIN ...
I'm trying to make what seems to be an advanced use of NHibernate with sql server functions. I'm using NHibernate's ICriteria interface to supply paging, sorting and filtering for my listviews. one of business objects is an aggregation of items from 3 different tables. in order to do this aggregation in the DB I've used a transact-sql fu...
Hi all, I am currently having problems calling a stored procedure async from within a insert-update-trigger. For this I m using the service broker. --message type CREATE MESSAGE TYPE [TheMessage] VALIDATION = NONE --contract CREATE CONTRACT [TheContract] ([TheMessage] SENT BY ANY); --queue CREATE QUEUE [TheQueue] WITH ACTIVATION (STA...
I want to retrieve identity column value in ms access from an autoincremented column. Basically i m running a transaction in which i have to write two insert queries. 2nd query will be containing autoincremented value that was generated from query 1. how can i do that ? ...
I'm trying to update multiple columns in a MS SQL statement using a sub-query. A search led me to something like: UPDATE table1 SET col1 = a.col1, col2 = a.col2, col3 = a.col3 FROM (SELECT col1, col2, col3 from table2 where <expression>) AS a WHERE table1.col1 <expression> http://geekswithblogs.net/phoenix/archive/2009/10/13/update-mu...
I have a table like this (of course there are many more values but you get the idea): ID Name --- ---- 1 A 1 B 2 C 3 D 4 A 4 D 4 E 4 F 4 G 4 H I want to write a query that would output this, given that an ID cannot have more than 6 names. ID Name1 Name2 ...
I have created an INSTEAD OF INSERT trigger on a view in my database. I want to know which columns are included in the column list of the INSERT statement on the view. If you read the MSDN documentation for triggers the UPDATE() and COLUMNS_UPDATED() functions should satisfy this requirement. However, during my testing I found that re...
I have a table with an identity field. What the best SQL query to get the Ids of all the deleted records from this table? ...
here is the table and data like: id name 1 test1 2 test2 3 test3 4 test4 5 test5 6 test6 From above data i want the data like if i pass the id as parameter and return the data from from up and gown by order Example if i pass the id as parameter = 4 then it should be return upline 2 row and downline 2 row for particular id,...
This is a follow up to the question Nested stored procedures containing TRY CATCH ROLLBACK pattern? In the catch block I use a stored procedure to report (reraise) the error by reading from ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE(), etc. As described here I also have a check so that it can determine if the error has already been ...
I need to create a hash key on my tables for uniqueness and someone mentioned to me about md5. But I have read about checksum and binary sum; would this not serve the same purpose? To ensure no duplicates in a specific field. Now I managed to implement this and I see the hask keys in my tables. Do I need to alter index keys originally cr...