Never been to CodeSnippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world (or not, you can keep them private!)

Showing 11 - 20 of 36 total

On This Page:

  1. 1 Limit equivelent for MSSQL
  2. 2 SQL Update with Values from Second Table
  3. 3 Getting column name
  4. 4 Scripting schema updates in MSSQL #1
  5. 5 Retrieve MSSQL table information
  1. 6 vi find/replaces for adding a table prefix to a SQL dump
  2. 7 Удалить все VoIP записи от AquaGatekeeper
  3. 8 INSERT... ON DUPLICATE KEY UPDATE...
  4. 9 Update From sample
  5. 10 get firstname and surname from string

Limit equivelent for MSSQL

to mysql sql mssql limit clause paging pagination paginating offset limitting by chrelad on Oct 12, 2007
Props to this site for the answer:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=850&lngWId=5
// MySQL limit clause
SELECT emp_id,lname,fname FROM employee LIMIT 20,10

// MSSQL equivelent
select * from (
 select top 10 emp_id,lname,fname from (
    select top 30 emp_id,lname,fname
    from employee
   order by lname asc
 ) as newtbl order by lname desc
) as newtbl2 order by lname asc

"The main thing to remember is to deal with all your ORDER BY clauses and also to use the inner TOP as the sum of what you are looking for. In this example - 30 since you start at 20 and want 10 rows (20+10=30)."
_

SQL Update with Values from Second Table

to sql update by ngwells on Oct 09, 2007
// Update multiple fields in a table based on values in a second table. This prevents you from having to use multiple sub-queries. 

UPDATE table
SET col1 = a.col1, col2=a.col2
FROM anotherTable a
WHERE a.anotherTableID = 1

Getting column name

to sql by baburk on Sep 19, 2007 1 Comments
// description of your code here

// insert code here..



I wants to get the number of column in a table and also every column name one by one both in sql and in T SQL.

Anybody help.

Scripting schema updates in MSSQL #1

to sql tsql database mssql schema update tables microsoft columns query scripting information_schema alter altering by chrelad on Aug 08, 2007 1 Comments
This SQL statement will alter a table only if a column in the table to be altered does NOT contain a column named 'FavoriteColorId'.

From: http://haacked.com/

IF NOT EXISTS
(
    SELECT * FROM [information_schema].[columns]
    WHERE    table_name   = 'Customer'
    AND      table_schema = 'dbo'
    AND      column_name  = 'FavoriteColorId'
)
BEGIN
    ALTER TABLE [dbo].[Customer]
    ADD FavoriteColorId int

Retrieve MSSQL table information

to sql server tsql mssql table microsoft column information properties sysproperties systables data type types length columns by chrelad on Aug 08, 2007
Retrieve tons of information (length, data type, name, etc.) of columns for a table. Works in (T|MS)SQL (all I've tested it in).

SELECT * FROM information_schema.columns WHERE table_name = 'Customers'

vi find/replaces for adding a table prefix to a SQL dump

to regex sql vi vim findreplace subtitution by jamiew on Jun 11, 2007
Was importing a DB but wanted to prefix the tables, but the file was too large for TextMate. Here's all the vim substitutions

%s/DROP TABLE IF EXISTS `/DROP TABLE IF EXISTS `prefix_/g
%s/CREATE TABLE `/CREATE TABLE `prefix_/g
%s/INSERT INTO `/INSERT INTO `prefix_/g
%s/LOCK TABLES `/LOCK TABLES `prefix_/g
%s/ALTER TABLE `/ALTER TABLE `prefix_/g

Удалить все VoIP записи от AquaGatekeeper

to sql freeradius korovka aquagatekeeper by rige on Apr 17, 2007 
delete from korovka_radacct WHERE "nasipaddress" = '81.4.195.254' AND "h323calltype" = 'VoIP';

INSERT... ON DUPLICATE KEY UPDATE...

to sql by LMZ on Apr 09, 2007 3 Comments
Please note: "INSERT... ON DUPLICATE KEY UPDATE..." can also use a compound (unique) key to check for duplication. This is very userful. For example:
If you have a log table to log hits to different websites daily, with "site_id"-s and "time" fields, where neither of them are primary keys, but togethether they are unique, then you can create a key on them, and then use "...ON DUPLICATE KEY..."

Table logs:
id: INT(11) auto_increment primary key
site_id: INT(11)
time: DATE
hits: INT(11)

Then:
CREATE UNIQUE INDEX comp ON logs (`site_id`, `time`);


And then you can:
INSERT INTO logs (`site_id`, `time`,`hits`) VALUES (1,"2004-08-09", 15) ON DUPLICATE KEY UPDATE hits=hits+15;

Excellent feature, and it is much faster and briefer then using first a select, then issuing either an update or an insert depending on the value of the select. You also get rid of the probably necessary table-lock during this action.

from: http://dev.mysql.com/doc/refman/5.0/en/insert.html

Update From sample

to sql update from by horhito on Jan 30, 2007
// sample "update from" sql statement

UPDATE 
	[Crm].[tbPartyLegalEntityData]
SET 
	[CompanyType] = ape.[CompanyType],
	[EmployeesCount] = ape.[EmployeesCount]
FROM 
	[Crm].[tbPartyLegalEntityData] p
INNER JOIN 
	App.[tbApplicationPersonEmployer] AS ape 
ON 
	ape.[Bulstat] = p.[Bulstat]
WHERE 
	ape.[Bulstat] <> '!' 
	AND [ape].[EmployeesCount] IS NOT NULL 
	AND [ape].[CompanyType] IS NOT NULL

get firstname and surname from string

to sql filter surname firstname by indiehead on Jan 15, 2007
// takes a string and rips out the firstname and surname from it

select 
substring(login_id, 1, patindex('% %', login_id)) as firstname,
substring(login_id, patindex('% %', login_id), len(login_id)) as surname

from c_contact
and login_id <> ''
Showing 11 - 20 of 36 total

On This Page:

  1. 1 Limit equivelent for MSSQL
  2. 2 SQL Update with Values from Second Table
  3. 3 Getting column name
  4. 4 Scripting schema updates in MSSQL #1
  5. 5 Retrieve MSSQL table information
  1. 6 vi find/replaces for adding a table prefix to a SQL dump
  2. 7 Удалить все VoIP записи от AquaGatekeeper
  3. 8 INSERT... ON DUPLICATE KEY UPDATE...
  4. 9 Update From sample
  5. 10 get firstname and surname from string