Inserting Data Into Table Using Select Query

In SQL, sometimes we need to select some data from one table and insert data into another table, this can be done as follows with a simple sql query:

Insert into Table1 (c1, c2, c3, c4, c5)
(Select c1, c2, c3, c4, c5 from Table2)
But make sure that both the tables have same fields, data type..

Read More......

Getting Last Modified/Created Table, Stored Procedure, Function.. in Sql Server

Sometimes, we want to know how many tables, stored procedures or functions we have created on a particular day, or on which tables, sps or functions, modifications have occured. All this can be known through a very simple sql query, which is as below:

select name,type,type_desc, create_date, modify_date from sys.objects

where convert(varchar,modify_date,101) = convert(varchar,getdate(),101)

order by modify_date desc

Here

name : - "Its the table or sp or function name"

type: - "To identify a table or sp or function etc.. P, S, U"

type_desc: -"Description whether it is table, sp etc.."

- S = System Table, PK = Primary Key,U = User Table,

- P= Stored Procedure, TF = Table Value Function,

- FN = Scalar Function, D= Default Constraint

create_date: - "Date when it is created"

modify_date: - "Last modified datetime of table, sp,.. etc"

In above query, in place of getdate(), you can pass the date you want, also you can get complete list after removing where condition, just giving order by modified date from sys objects..

Very important when we want to generate scripts for particular tables, sps or function for a particular date.

Read More......

How to Get Table Structure Using SQL Query

Sql life made so easy..

See how..

View/Copy, structure of table in sql in just one line query….

select * from tablename where 1<>1

cheers…

Read More......

Import Excel Spreadsheet Data into SQL Server Database Table

There are many ways to import/export excel data into sql server table.

Using SqlBulkCopy Common Method for sql server 2005/2008:

http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

In SqlServer 2005 Using Integration Services

http://www.builderau.com.au/program/sqlserver/soa/How-to-import-an-Excel-file-into-SQL-Server-2005-using-Integration-Services/0,339028455,339285948,00.htm

Using Sql Server 2005 Import and Export Wizard

http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm

Using Sql Server 2008 Import and Export Wizard

http://dotnetslackers.com/articles/sql/Importing-MS-Excel-data-to-SQL-Server-2008.aspx

Read More......

Get Only Date from Datetime in Sql

Sometimes we need to have only date from datetime field in sql.

There are number of ways to accomplish that:

Suppose we take current date

select getdate()

now to take only date from current date excluding time, we do as follows in various ways:




select DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
select convert(varchar,getdate(),101)
select convert(char(8),getdate(),1)
select cast(cast(getdate() as int) as datetime)
select cast(convert(varchar,getdate(),110) as datetime)

Read More......

SQL SERVER – Fix : Management Studio Error : Saving Changes in not permitted. The changes you have made require the following tables to be dropped and

I encountered problem when modifying table in SQL Server 2008, Once the table is created open the table in SSMS by clicking on the table name and selecting “Design.” Try to include another column to the existing table and click on save (CTRL+S). It will prevent it from saving and will emit the following error in popup.
Saving Changes in not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created

i searched the internet.. i got solution from pinal dave's blog

http://blog.sqlauthority.com/2009/05/18/sql-server-fix-management-studio-error-saving-changes-in-not-permitted-the-changes-you-have-made-require-the-following-tables-to-be-dropped-and-re-created-you-have-either-made-changes-to-a-tab/

Read More......

Download Cheatsheets - .Net, Sql Server, Php, Regular Expressions, CSS, Ajax,Javascript....

A cheat sheet or crib sheet is a concise set of notes used for quick reference.

You can download cheatsheets of various technologies like:

.Net

Actionscript

Ajax

Javascript

C#

HTML

Sql Server

CSS

JAVA

PHP

Regular Expressions

MySql

Asp/Vbscript

etc..

from

http://www.addedbytes.com/cheat-sheets/

http://www.cheat-sheets.org/

Read More......

Learning Sql Server Joins In Form of Venn Diagrams

Learn Basic Concepts of Sql joins like
Inner Join
Outer Join
Cross Join
Self Join
etc..
in the most simpler manner by pinal dave in the form of venn diagram.
He has explained sql joins with proper examples.. very good article for newbies on sql server

http://dotnetslackers.com/articles/sql/SQL-SERVER-JOINs.aspx

Read More......

Learn Sql Server 2008 New Features With Demos, Presentations

New to Sql Server 2008 - No need to worry, microsoft always is helpful in such cases.. Now you can learn new features of sql server 2008 interactively with fun, as it includes presentations, demos, hands on labs.. etc You can download the SQL Server 2008 Training Kit from the following link and follow the steps as given there.. also there are some basic system requirements need to be fulfilled while installing this toolkit.. all these are given below:

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=e9c68e1b-1e0e-4299-b498-6ab3ca72a6d7

Read More......

Set Language in Sql Server

When you are working with multi lingual web applications, you may need to retrieve data like month name, day name etc to be displayed in different languages. For example month "June" is in English, its dutch translation would be "Juni" .This is possible with "Set Language languagname" feature of sql server. where language name is the name of language you want to set.. you can complete list of languages that sql support using

"select * from sys.syslanguages"

Below is one example using set language, retrieving day and month name in different languages:
DECLARE @Today DATETIME
SET @Today = '6/22/2009'

SET LANGUAGE Deutsch
SELECT DATENAME(month, @Today) AS 'Month Name in Dutch'
SELECT DATENAME(dw, @Today) AS 'Day Name in Dutch'

SET LANGUAGE us_english
SELECT DATENAME(month, @Today) AS 'Month Name in English'
SELECT DATENAME(dw, @Today) AS 'Day Name in English'

Read More......

Sql Server Functions

Most of us are unaware about various sql server functions..

we are only limited to functions we are knowing. but there are other useful functions which should be known.. do have a look they might be useful to you…

http://get-shorty.com/shorty/60464/

Read More......