I'm working on an SSIS package where I'm importing data from a CSV file into a SQL table. The only field that I'm concern with is the Username. This Username must be unique. I don't care whether first name or last name are the same or not. In the package, I imported the data from file into a temp SQL table. And then I used SELECT DISTI...
I have a table with a nvarchar column. This column has values for example: 983 294 a343 a3546f and so on. I would like to take MAX of this values, but not as text but like from numerics. So in this example numerics are: 983 294 343 3546 And the MAX value is the last one - 3546. How to do this in TSQL on Microsoft SQL? ...
I have a database which tries to acheive point-in-time information by having a master table and a history table which records when fields in the other table will/did change. e.g. Table: Employee Id | Name | Department ----------------------------- 0 | Alice | 1 1 | Bob | 1 Table: History ChangeDate | Field ...
I'd like to put the results of a stored proc into a temp table. It seems that the temp table must be defined beforehand and an INSERT INTO will not work. Anyone know how to get the schema of the recordset being returned from a select statement? sp_help only gets info on parameters. ...
I need to be able to get the CREATE scripts for existing tables within SQL Server 2008. I assume I can do this by querying the sys.tables somehow, however this isn't returning me the CREATE script data. ...
I have the following stored procedure which returns A, B, and the count in descending order. I am trying to use ROW_NUMBER, so I can page the records, but I want the first row number 1 to be the record with the highest count, so basically, if I return a table with 3 records and the count is 30, 20, 10, then row number 1 should correspon...
From this post How to use ROW_NUMBER in the following procedure? There are two versions of answers where one uses a SubQuery and the other uses a CTE to solve the same problem. Now then, what is the advantage of using a CTE (Common Table Expression) over a sub-query(thus, more readable what the query is actually doing) The only advant...
I have a long list of SPs (stored procedure) and Functions in my SQL server db. I could save them one by one by right clicking and script XXX to Alter To. Is there any way in TSQL to query all SPs and functions save them to xxx.sql files? For example, for sp_mySP1, I would like to save it to sp_mySP1.sql which is a text file. The databa...
What is the general guidance on when you should use CAST versus CONVERT? Is there any performance issues related to choosing one versus the other? Is one closer to ANSI-SQL? ...
What's the best way to extract the first word of a string in sql server query? ...
Consider the following TSQL: SET @WhereClause1 = 'where a.Date > ' + @InvoiceDate I get a date/string conversion error. @InvoiceDate is a datetime variable. What is the right syntax? ...
Is there a way to search for one value (in my case it is a UID of the type char(64)) inside any column of any table inside one MS-SQL database? I'm sitting in front of a huge database without any idea how the tables had to be linked together. To find that out I'd like to list all tables and there columns that contain a certain value in ...
Is there anybody out there writing unit tests for their TSQL stored procedures, triggers, functions ... etc. I've recently started making database and restores and installs part of our automated Cruise Control build process. Now I'm thinking about taking it to the next level where we do the install, then run through a list of stored pr...
I'm writing a user-defined function to extract values from an XML column in SQL Server which represents a simple dictionary of string key-value pairs. The only way I've made it work so far seems overly complex. Do you have any simplifying suggestions or tips for the DictValue function below? IF EXISTS (SELECT * FROM sys.objects WHERE...
Does anybody know if there is any unique identificator for a database that I could use to identify a database even it has been renamed or restored/copied from different server? This kind of id could be created by server when a database is created but I cannot find any. ...
Duplicate of: http://stackoverflow.com/questions/712443/tsql-varchar-string-manipulation-problem/712453#712453 I'm building a dynamic SQL statement out of parameters from a reporting services report. Reporting services passes MutiValue Parameters in a basic CSV format. For example a list of states may be represented as follows: AL,CA...
I have a variable which contains the following string: AL,CA,TN,VA,NY I have no control over what I get in that variable (comes from reporting services) I need to make it look like this: 'AL','CA','TN','VA','NY' How do I do this? ...
Why does SQL Server insist that the temp table already exists! one or the other will happen!! , so it will never be the case. declare @checkvar varchar(10) declare @tbl TABLE( colx varchar(10) ) set @checkvar ='a' INSERT INTO @tbl (colx) VALUES('a') INSERT INTO @tbl (colx) VALUES('b') INSERT INTO @tbl (colx) VALUES('c') INSERT IN...
Is there any way within a stored procedure for it to reference it's own name? Say I want it to print it's own name, but not to physically type the name in the stored procedure. Is there any cheater way to get the name or id from within the procedure itself without using the actual name to find the information? ...
How can I rewrite the query "select col1 from tbl1" so it splits the results into three columns - each column sorting the results vertically? So if the data I'm getting back is: aaa bbb ccc ddd eee fff ggg I need the query to return it as: aaa ddd ggg bbb eee ccc fff Is this possible? Thanks ...