Hi Folks, I've two tables: TableA and TableB, joined by TableA.TableA_Id->1..n<-TableB.TableA_Id. A simple PK-FK. I need to extract distinct TableA records given a certain condition on TableB. Here's my 1st approach: SELECT * FROM TableA A INNER JOIN TableB B ON A.idA = B.IdA AND B.Date = '2009-01-10' ORDER BY A.Id; This is nice, bu...
I need to make two queries on two different tables and the data isn't really related. So when I call the stored proc through my code, I should be getting a DataSet with two DataTables, one DataTable for each query. How is that done in SQL Server stored procs? ...
I have the option of writing two different formats for a database structure: Article ------- ArticleID int FK Article_Tags ------------ ArticleTagID int FK ArticleID int FK TagText varchar(50) or Article ------- ArticleID int PK Article_Tags ------------ ArticleTagID int PK ArticleID int FK TagText varchar(50) FK Tag --- TagText v...
I hope to find a way to get the value in the Nth column of a dataset. Thus, for N = 6 I want SELECT (Column6Value) from MyTable where MyTable.RowID = 14 Is there a way to do this in TSQL as implemented in SQL Server 2005? Thanks. ...
Hi there, I have a SQL table like so PV Area CouterParty 851 M010 Name1 561 M011 Name2 869 M012 Name3 ... And I need to transpost it, using T-SQL (not reporting services or anything else), so that it looks like this: CounterParty M010 M011 M012 .... Name1 851 Name2 561 Name...
Scenario: Let's say I have two tables, TableA and TableB. TableB's primary key is a single column (BId), and is a foreign key column in TableA. In my situation, I want to remove all rows in TableA that are linked with specific rows in TableB: Can I do that through joins? Delete all rows that are pulled in from the joins? DELETE FROM ...
This is more of a syntax question I'm trying to write a store procedure or function that I can embed into a query such as: select * from MyBigProcOrFunction I'm trying to define a tabular function but i do not understand how to do it as I build tmp tables to work out the data before i finally have the return at the endtable. My mark up...
Why would someone use numeric(12, 0) datatype for a simple integer ID column? If you have a reason why this is better than int or bigint I would like to hear it. We are not doing any math on this column, it is simply an ID used for foreign key linking. I am compiling a list of programming errors and performance issues about a product, ...
T-SQL: 1(ANSI): convert(varchar(10),@DueDate, 102) < convert(varchar(10),getdate(), 102) 2(USA): convert(varchar(10),@DueDate, 101) < convert(varchar(10),getdate(), 101) Notice that these will return different results when year is considered. Why? What's the difference? Why isn't the operator taking year into consideration whe...
This is what I am trying to do Declare @Var varchar(50) Declare @Num1 varchar(50) Declare @Num2 varchar(50) Declare @Counter smallint Set @Counter=1 Set @Num1='Hello' Set @Num2='Hi' while (@Counter<2) begin Set @Var=N'@Num'+convert(varchar,@Counter) //Now I want to get the value of '@Num1' that is stored in @Var when @Count...
I have a column containing items that can be sorted by the user: DOC_ID DOC_Order DOC_Name 1 1 aaa 2 3 bbb 3 2 ccc I'm trying to figure out a way to properly initialize DOC_Order when the entry is created. A good value would either be the corresponding DO-CID (since it is autoassigned),...
I tried searching here for a similar solution but didn't see one so I was wondering what is the best way to accomplish the following. I have a table with 17 million + rows all have a unique ID. We have recently created a new table that will be used in conjunction with the previous table where the foreign key of the new table is the uni...
I'm working on a statement scanner for our updater (looking for statements that will cause problems with synchronized data) and I need to know which TSQL data types get resolved as Types.VARCHAR and which ones resolve to Types.LONGVARCHAR when you invoke DatabaseMetaData.getColumns()? ...
Here's my scenario: I have a simple stored procedure that removes a specific set of rows from a table (we'll say about 30k rows), and then inserts about the same amount of rows. This generally should only take a few seconds; however, the table has a trigger on it that watches for inserts/deletes, and tries to mimic what happened to a l...
In SQL/Transact SQL, I am trying to update a temp table to take the latest date from 3 different date columns (in that temp table), and put that MAX date into a "latest date" column. What is the best way to do this, using an update statement? ...
This is a bit of an open question but I would really like to hear people opinions. I rarely make use of explicitly declared temporary tables (either table variables or regular #tmp tables) as I believe not doing so leads to more concise, readable and debuggable T-SQL. I also think that SQL can do a better job than I of making use of tem...
On MS SQL Server 2005, I have a table that I want to replace. Many other tables have a Foreign Key reference to this table. Is there a script that I can run to simply roll over all these references from the old table to the new table? Or am I going to have to go through and specifically drop each of these foreign keys, and write a new...
Is it possible to determine the type of a local variable at runtime in TSQL? For example, say I wanted to do something along these lines: IF ( @value IS INTEGER ) Or IF ( TYPEOF(@value) = <whatever> ) Does anyone know of any way to accomplish this? EDIT: This is not for a specific task, this is more of a general knowledge questio...
I'm trying to test a proposition that one of our vendors presented to us for accessing their product database and it regards to queries and transactions that span multiple servers. I've never done this directly on the database before and to be frank, I'm clueless, so I'm trying to mock up a proof that this works at least conceptually. ...
This is locale specific to the US wherein it considered that the start of a week is Sunday; I want to be able to ask SQL to give me the date of the next Sunday relative to today [getDate()]. If today is Jan 15 it should return Jan 18; if today were Sunday it should return the following Sunday which is the 25th. This would be trivial to w...