Is there anyway to loop through a table variable in T-SQL? DECLARE @table1 TABLE ( col1 int ) INSERT into @table1 SELECT col1 FROM table2 I use cursors as well, but cursors seem less flexible than table variables. DECLARE cursor1 CURSOR FOR SELECT col1 FROM table2 OPEN cursor1 FETCH NEXT FROM cursor1 I would like to be ...
how can i decide this problem? SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[GetDataById] () RETURNS INT AS BEGIN DECLARE @query NVARCHAR(500) DECLARE @j INT SET @query=N'select * from catalog' EXEC sp_executesql @query RETURN @j END When I try to exec this one: select dbo.GetDataById() I get an ...
So, I've been searching around and I've found things similar to my problem, but I need more help to get a real solution. I'm trying to construct a query that will return 2 columns of data, the first column should be a list of the column names themselves and the second should be the value of that column. Visually it would look like this...
I've got a problem I was wondering if there's an elegant solution to. It is a real business problem and not a class assignment! I have a table with thousands of records, some of which are groups related to each other. The database is SQL 2005. ID is the primary key. If the record replaced an earlier record, the ID of that record is i...
Hello All I have a huge problem with the REPLACE SQL function in Microsoft SQL Server Management Studio Express. When I do following query SELECT REPLACE('ArticleNumber', 'S401', 'I0010') SELECT REPLACE('ArticleNumber', 'S302', 'I0020') SELECT REPLACE('ArticleNumber', 'S303', 'I0030') SELECT REPLACE('ArticleNumber'...
Using a TSQL update command against a SQLServer database, how can I update a column of type FLOAT with the smallest possible double value? The smallest possible double value in hex notation being 3ff0 0000 0000 0001 (http://en.wikipedia.org/wiki/Double%5Fprecision) ...
AIM : Convert a resultset to XML and assign the result to a variable Result Set : M000017690 324067342 324067349 324067355 324154449 Converted XML : <Products> <Product ProductId="324067342" /> <Product ProductId="324067349" /> <Product ProductId="324067355" /> <Product ProductId="324154449" /> <Product ProductId="M0000176...
We sometimes write dataport scripts that rename tables or columns and have other complex logic. We also have SQL in IF statements referencing those old columns or tables. We have a standard to use IF statements to make our SQL Scripts multi run friendly. However occasionally, even though the if statement evaluates to false, the code w...
How to check if the global Temporary table exists in SQL server, if yes then delete that global temporary table? I am trying to execute this: IF OBJECT_ID('##Table', 'U') IS NOT NULL DROP TABLE ##Table ...but it is not working. ...
Hello, Lets say I have a table: -------------------------------------- | ID | DATE | GROUP | RESULT | -------------------------------------- | 1 | 01/06 | Group1 | 12345 | | 2 | 01/05 | Group2 | 54321 | | 3 | 01/04 | Group1 | 11111 | -------------------------------------- I want to order the result by t...
Sorry about a vague title, but here's what i am trying to do. I have the following two tables TABLE_PERSON | TABLE_PHONE | col_Name | col_Name col_phoneID col_phoneNbr -------- | -------- ----------- ------------ Clark Kent | Clark Kent 1 111-111-1111 Bruce Wayne | Clark Kent ...
I have this code in a trigger. if isnull(@d_email,'') <> isnull(@i_email,'') begin update server2.database2.dbo.Table2 set email = @i_email, where user_id = (select user_id from server2.database2.dbo.Table1 where login = @login) end I would like to update a table on another db server, both are MSSQL. the query above works for m...
I'm writing a forum. I've a TOPICS table, and a POSTS table. In the POSTS table, I have a TopicId field, and a date field. I want to get all the topics, ordered by their last post date, in a single SQL query. How can I do this? EDIT: The post's date is in the POST table. I want to check what is the last post of every post, then checki...
I am attempting to wipe and repopulate test data on SQL CE. I am getting an error due to FK constraints existing. Typically in Sql2005 I would ALTER TABLE [tablename] CHECK/NOCHECK CONSTRAINT ALL to enable/disable all constraints. From what I could find in my searching, it seems that this might not be supported in CE. Is that true? ...
environment: SQL server 2008 enterprise edition. I am using Rollup clause and you how it shows aggregation at various levels WITH NULL values showing different levels of rollups e.g. ROllup(year,month,week) would show subtotals at each level. I want it rolled up and yet want to see only highest elvel of aggregation. so I dont want to s...
I need to perform a pivot on an XML column in a table, where the XML contains multiple elements with a number of attributes. The attributes in each element is always the same, however the number of elements will vary. Let me give an example... FormEntryId | FormXML | DateCreated =========...
I am new to SQL Server 2008 database development. Here I have a master table named ‘Student’ and a child table named ‘Address’. The common column between these tables is ‘Student ID’. My doubts are: Do we need to put ‘Address Id’ in the ‘Address’ table and make it primary key? Is it mandatory? ( I won’t be using this ‘Address Id’ i...
This is a follow up to my previous question, in t-sql SELECT SCOPE_IDENTITY() returns a BIGINT, I did the following to get it to return an INT: DECLARE @X INT INSERT ... SELECT @X = SCOPE_IDENTITY() -- if i don't include the line below, it will return a BIGINT SELECT @X Why does it return a BIGINT unless I do SELECT @X at the...
I am trying to write T-sql script which will find "open" records for one table Structure of data is following Id (int PK) Ts (datetime) Art_id (int) Amount (float) 1 '2009-01-01' 1 1 2 '2009-01-05' 1 -1 3 '2009-01-10' 1 ...
I need to update the first digit of numbers. For example, 3003. I only want the first '3' to be changed to '2' and don't want the last digit '3' to be changed. Something like the following faulty query: update table set defaulttopicorder = replace(defaulttopicorder, left(defaulttopicorder,1), 2) where .... ...