I want to be able to insert data from a table with an identity column into a temporary table in SQL Server 2005. The TSQL looks something like: -- Create empty temp table SELECT * INTO #Tmp_MyTable FROM MyTable WHERE 1=0 ... WHILE ... BEGIN ... INSERT INTO #Tmp_MyTable SELECT TOP (@n) * FROM MyTable ... END The a...
What are the best practices for ensuring that your SQL can be run repeatedly without receiving errors on subsequent runs? e.g. checking that tables don't already exist before creating them checking that columns don't already exist before creating or renaming transactions with rollback on error If you drop tables that exist before cr...
I've already checked out the question http://stackoverflow.com/questions/633860/deleting-duplicate-records-using-a-temporary-table and it doesn't quite go far enough to assist me with this question: I have a table of approximately 200,000 address locations hosted on a SQL 2000 Server. This table has a huge problem with duplicate data in...
Our server application receives information about rows to add to the database at a rate of 1000-2000 rows per second, all day long. There are two mutually-exclusive columns in the table that uniquely identify a row: one is a numeric identifier called 'tag' and the other is a 50character string called 'longTag'. A row can have either a ...
I have a table T which has a column C which contains text that have the character & within it, but if I do the following query, it returns nothing, although I may have 10 rows. SELECT * FROM T WHERE Contains(C, 'a&b') ...
I have a table with a column I would like to update its values. Here is an example of TSQL codes: WITH Pieces(id, newdesc) AS ( SELECT itemid, REPLACE(REPLACE(description, 'DESC_A', 'DESC_B'), 'NEW_1', 'NEW_2') FROM myTable WHERE description like '%DESC_A%DESC_B%' ) -- SELECT * FROM Pieces UPDATE myTable SET description = newdesc //...
Hi! I'm trying to dynamically accept a table name depending on the conditions satisfied, also the column name is selected dynamically, and so is the comparison value, but I'm getting an error while running it. I'm writing this code in C# and my backend is SQL server 2005. Please help me. Here is the code: if( table=="studenttab"...
I have seen SQL that uses both != and <> for not equal. What is the prefigured syntax and why? I like != because <> reminds me of Visual Basic. ...
Hi, I've got a strange situation with some tables in my database starting its IDs from 0, even though TABLE CREATE has IDENTITY(1,1). This is so for some tables, but not for others. It has worked until today. I've tried resetting identity column: DBCC CHECKIDENT (SyncSession, reseed, 0); But new records start with 0. I have tried doi...
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? ...
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...
I have a relationship between two tables. The two tables PKs are int types. In one table (UserS), I need to supply the Username and get the corresponding ID (which is the PK). This is the standard ASP.NET user table when using forms authentication. However, in a related table, I want to supply the ID I find from the Users table to get ...
We have a large application mainly written in SQL Server 7.0, where all database calls are to stored procedures. We are now running SQL Server 2005, which offers more T-SQL features. After just about every SELECT, INSERT, UPDATE, and DELETE, the @@ROWCOUNT and @@ERROR get captured into local variables and evaluated for problems. If the...
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...
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? ...
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...
I am trying to create stored procedure which will decide which language to use based on a parameter passed? How can I do something like this ? declare @en varchar(50) = 'en' declare @fi varchar(50) = 'fi' select * from [@en].[TestingLanguagesInNameSpacesDelMe] select * from [@fi].[TestingLanguagesInNameSpacesDelMe] ...
I have a set of tables that's actually a stubby tree. At the top, there's a Customer, and below that Invoices, and Invoice Detail records. (In actuality, there's about two dozen of these tables all referring to the Customer but the principle should apply with just three tables.) What I'd like to do is to copy the Customer and all of t...
We all know that to select all columns from a table, we can use SELECT * FROM tableA Is there a way to exclude column(s) from a table without specifying all the columns? SELECT * [except columnA] FROM tableA The only way that I know is to manually specify all the columns and exclude the unwanted column. This is really time consumin...
Hi, I have a query that returns a result set similar to the one below (in reality it is far bigger, thousands of rows): A | B | C | D -----|----|----|----- 1 NULL | d0 | d0 | NULL 2 NULL | d0 | d1 | NULL 3 NULL | d0 | d2 | a0 4 d0 | d1 | d1 | NULL 5 d0 | d2 | d2 | a0 Two of the rows are consi...