I have a SQL Server DB that has a table of products, and another table which contains a list of the sku variants of each product if it has one. I want to export all the products and their SKU's into excel. At the moment, I have a helper SQL function which performs the subquery against a product_id and concatenates all the SKU's into a c...
anyone can show me how to generate from this data ------------------------DATA-------------------------- Key ParentKey 5 NULL 25 5 33 25 26 5 27 5 34 27 28 5 29 5 to this XML result? ---------------------RESULTS-------------------------- <record key="5" parentkey = ""> <record key="25" parentkey = "5"> <record key="3...
Hello people! I have a table like this (simplified): ID | Name | Parent --------------------------------- 1 | IND | NULL 2 | INS | 5 3 | CON | NULL 4 | AUT | 1 5 | FIN | NULL 6 | PHA | 1 7 | CFIN | 5 8 | CMRKT | 7 DDL: CREATE TABLE [dbo].[tblIndustryCodes]( ...
Hi, I am relatively new to databases,so please forgive me if my query sounds trivial. I need to send bulk email to all the contacts listed in a particular table. The body of the email is stored in a .doc file which begins as Dear Mr. _. The SQL server will read this file, append the last name of the contact in the blank space and send it...
Simple question: Office debate about whether the keyword AS is necessary in our T-SQL statements. I've always used it in cases such as SELECT mycol AS Something FROM MYTABLE; I know you don't NEED it but is there any benefit of using it (or not using it)? I think it makes statements easier to read but people seem to disagree with me. ...
On table "A" depend about 30 other tables via FK to "A.Id". For integration testing I have to drop the table and recreate it to create a defined state. Because of the dependent objects their seem to be no way to delete and recreate the table. The error message is: Could not drop object 'dbo.A' because it is referenced by a FOREI...
I have a sql table of payroll data that has wage rates and effective dates associated with those wage rates, as well as hours worked on various dates. It looks somewhat like this: EMPID DateWorked Hours WageRate EffectiveDate 1 1/1/2010 10 7.00 6/1/2009 1 1/1/2010 10 7.25 6/10/2009 1 1/1/201...
Here's the caveat... If you have a table that has a single column and that column happens to be an identity column with identity_insert turned OFF, is it still possible to write a T-SQL insert statement for this table? ...
I'm interested in T-SQL source code for synchronizing a table (or perhaps a subset of it) with data from another similar table. The two tables could contain any variables, for example I could have base table source table ========== ============ id val id val ---------- ------------ 0 1 0 3 ...
how to convert a binary(128) from little endian to big endian in SQL Server? ...
We have a table value function that returns a list of people you may access, and we have a relation between a search and a person called search result. What we want to do is that wan't to select all people from the search and present them. The query looks like this SELECT qm.PersonID, p.FullName FROM QueryMembership qm INNER JOIN dbo...
hi im having trouble trying to get the following function to work. CREATE FUNCTION test ( @nt_group VARCHAR(128) ) RETURNS @nt_usr TABLE ( [name] [nchar](128) NULL , [type] [char](8) NULL , [privilege] [char](9) NULL , [mapped login name] [nchar](128) NULL , [permission path] [nchar](128) NULL ) AS BEGIN ...
I am converting to an integer primary key and am having trouble seeding the new column data with a count of integer numbers. Given an existing table: create table t1 ( Id uniqueidentifier, NewId int, Data nvarchar(100) ) How would I update existing rows with a count of numbers from 1 to the # of rows in the result set? So: |...
If I try to run this query in SQL Server 2005: SELECT 1 WHERE NOT ( 0 ) I get this error: Msg 4145, Level 15, State 1, Line 1 An expression of non-boolean type specified in a context where a condition is expected, near ')'. Sometimes when debugging complex WHERE statements I will cut out pieces and test a particular scenar...
I have a simple procedure that selects 1000 rows from a table. For the sake of clarity, here's what the stored procedure looks like: alter procedure dbo.GetDomainForIndexing @Amount int=1, @LastID bigint, @LastFetchDate datetime as begin select top (@Amount) * from DomainName with(readuncommitted) where LastUpdated > ...
OMG! What am I doing wrong? declare @WTF TABLE ( OrderItemId int ) SELECT TOP 20 OrderItemId as OrderItemId INTO [@WTF] FROM ac_OrderItems SELECT * FROM [@WTF] Problem A: This creates a PHYSICAL table called @WTF. WHY?? I thought this was in memory only?! Problem B: The last line of code, if I do select * from @WTF... WITHOUT the ...
I am passing an ad-hoc Insert statement from c# application to the sql server 2000/2005. Sometimes, if machine (where sql server is installed) datetime format is different than what I am passing in, it is throwing an error. e.g. : In Insert statement I am passing '2010-03-10 00:00:00-05:00' this but machine regional date setting is dif...
I'm using SQL Server 2005. Let's say I have a table for products and another table for prices so that I can track price changes over time. I need a query that fetches distinct products (easy part) plus each product's most recent price and the date it changed. Products Table: CREATE TABLE [dbo].[Products]( [ID] [int] IDENTITY(1,1)...
I have a requirement to determine the maximum Id int value for a set of tables in my database. The column is always 'Id' and is the primary key. Is there a simple way I can make this determination without resorting to a cursor or looping? ...
I have a table Title Name Type ------------------------------------------------ T1 A Primary T1 B Primary T2 B Primary T2 C Secondary T2 D Secondary I need the output t...