tsql

TSQL Script Index Creation that can be rerun

I want to have a script of indexes that I can append to and rerun as new tables are added to my schema. For this reason, I want to skip creating indexes that already exist, but I have been unable to find a clean way to detect the index is already there. What I want to do is something like this: IF OBJECT_ID(N'[dbo].[Users].[IDX_LastName...

Whats the best way to get the past 24 hour results in T-SQL?

I'm working on creating a chart for my client and they want to get the total customer count over a 24 hour, 3 day, 1 week, 1 month, etc period. I'm honestly not the best with SQL, so generating these queries aren't my forte. In regards to getting the customers over 24 hours, I've come across two "where" statements that may work, but I'...

SQL Server - Need to gt back data based on nested rank

We have a feature that allows us to create SQL to get back data from one table based on a nested query that filters records based on matching criteria from another table. Now, we need to be able to get back data from the first table based on the top x records of the nexted query, rather than all matching records. For Example we want s...

"Distinct" column in SQL query

SELECT id, EmpNo FROM EmployeesTable EmpNo can be the same for 1 or more records in the results of the above query. I now want to add another column derived from EmpNo(lets call it EmpNo2) but only returning distinct values of EmpNo. For example if the above query returns 100 records but there are 69 distinct EmpNo values and i...

Handling 100's of 1,000,000's of rows in T-SQL2005

I have a couple of databases containing simple data which needs to be imported into a new format schema. I've come up with a flexible schema, but it relies on the critical data of the to older DBs to be stored in one table. This table has only a primary key, a foreign key (both int's), a datetime and a decimal field, but adding the coun...

Import website XML-feed to SQL Server

Hi All, Hope you all are doing well. I need to import an XML-feed from a website to my SQL Server database. I don't know much about XML. The feed structure is an bit complex. Here is the sample of that file: <line_feed> <FeedTime>1279519582927</FeedTime> <lastContest>4103839</lastContest> <lastGame>58629754</lastGame> <events> <event...

Primary Key: Slow Inserts??

Hello all, Defining a column to be a primary in table on SQL Server - will this make inserts slower? I ask because I understand this is the case for indexes. The table has millions of records. Thanks all for any help ...

LIKE function in SQL Server 2008 suggestion needed

I have a table Customer with 2 columns Name and Surname (sql Server 2008). User wants to search by Name-Surname - or just typing Jo Bloggs. Suppose a row is filled with CustomerId Name Surname 1 Jo Bloggs doing select * from customer where Name like '%Jo%' will find the records select * fr...

T-SQL A problem while passing CSV string into a stored procedure

Hi, I have that procedure which returns rows associated by ID with passed argument, i.e 1,5,7,9 ALTER PROCEDURE [dbo].[get_data] @MyCodes as varchar(max) = '' AS BEGIN DECLARE @query as nvarchar(max) set @query = 'SELECT name FROM user WHERE id IN (@p_MyCodes)' exec SP_EXECUTESQL @query, N'@p_MyCodes varcha...

T-SQL Distinct column problem when trying to filter duplicates out

I have the following data COL-1 COL-2 1 0TY/OK 1 0TY/OK 1 0TY/OK 1 0TY/OK 1 0TY/OK 2 2KP/L 2 2KP/L 2 2KP/L 2 2KP/L 2 2KP/L 3 7U5/2M 3 7U5/2M 3 7U5/2M 3 7U5/2M And i want to construct a select query to ret...

Conditional dynamic SQL with cursor

Hello, I have a query which uses a cursor to cycle through the results of a select statement. The select statement in short selects all of the records from a mapping table I have. One of the columns is 'SourceTableName'. I use this field to generate some dynamic SQL. I am looking to add a parameter to my stored procedure wrapped arou...

select data from 8 tabels into one temp table

I need to insert data from several tables with all the same field names into one temp table, i know i can use cursor/loop to do this, i wanted to know is there a quicker way of doing this. select from table 1, table 2, table 3, into #temptable. ...

Providing additional data when selecting distinct rows

I have a table of login events coming from Active Directory. One type of these events are machine logins, which include the IP address of the machine doing the login. This is handy, since it provides a timestamped way to determine what machine was on what IP at a given time. I'm trying to construct a query that'll give me a timestamped l...

SQLCMD: Prompt for Variable?

Coming from an Oracle background, Oracle's SQLPlus would let you indicate a variable. If the variable wasn't set, you'd be prompted to provide a value. I'm using SQLCMD, using the $([var_name]) syntax. In SSMS SQLCMD mode, I get: A fatal scripting error occurred. Variable tbl_name is not defined. ...for trying to run: SELECT ...

TSQL multiplying different datatypes

How can I multiply two types? I have one column with cost of type money. Another column that has the number of those products of type integer. How can I multiply these two values to result in a value representing the cost? Example: $2000 * 2 ...

Complex Linq-to-SQL where condition help

I have a perplexing SQL select statement (ugly) that I'm trying to write in LINQ. I'm working to get a single statment that will execute, not pre-selected data into lists that I have to send back to the server. DECLARE @StartDate DATETIME ; DECLARE @EndDate DATETIME ; SET @pStartDate = '20100501' SET @pEndDate = '20100531' SELECT r.com...

How do I execute a very long dynamic sql statement on SQL Server 2000?

Hello, I generate an sql string select statement that is around 25k characters long. Unfortunately the production server is SQL 2000. Is my only option to break the string up into 4k nvarchars? --ex. DECLARE @s1 NVARCHAR(4000) DECLARE @s2 NVARCHAR(4000) DECLARE @s3 NVARCHAR(4000) DECLARE @s4 NVARCHAR(4000) DECLARE @s5 NVARCHAR(4000) DE...

t-sql combine column values

Based on the following table ID Description ReleateID ----------------------------------- 1 some desc1. 50 1 some desc1. 60 2 some desc2. 50 2 some desc2. 70 3 some desc3. 80 How to get the following output ID Description AllRelatedIDs ---------------------------------- 1 some desc1. 50,60 2 some desc2...

create table specific id in isql

My company generates different readable ids for each table in the database with the format of [prefix]-[incrementing id] i.e. Inventory => INV-0001, INV-0002; (INCREMENTAL) Products => PROD-0001, PROD-0002, PROD-0003; (INCREMENTAL) The problem I am facing is that the ids are generated in c# code and not by the database so it makes it...

TSQL query to verify permissions and object exists

I am writing a query that will return if a table or stored proc exists within a database if a particular role has been assignned execute permissions to execute that particular stored proc. I guess I need to query the master database and wiggle thru that to get to the database and the table or stored proc that I am looking for. How c...