Generate Insert Statement in SQL server

--Run below procedure first,
--Too execute the procedure ,
--exec generateInsertSt_sp 'authors'
--This will generate a select statement, Copy that result set run again.


create proc dbo.sp_generateInsertSt
(
@tableName sysname,
@newtableName sysname=NULL

)

as
begin


declare @column varchar(200)
DECLARE @sql varchar(5000)


if @newtableName is null
set @newtableName=@tableName

set @sql='select ''insert into '+@newtableName+'( '

select @sql=coalesce(@sql+COLUMN_NAME+',','')
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE not in ( 'timestamp','image','sql_variant')
and TABLE_NAME=@tableName

set @sql=left(@sql,len(@sql)-1)+') select ''+'

select @sql=coalesce(@sql+
case
when DATA_TYPE='uniqueidentifier' THEN '''''''''+cast('+COLUMN_NAME+' as nvarchar(75))+'''''','','
when DATA_TYPE='varchar' THEN '''''''''+'+COLUMN_NAME+ '+'''''','','
when DATA_TYPE='char' THEN '''''''''+'+COLUMN_NAME+ '+'''''','','
when DATA_TYPE='nvarchar' THEN '''''''''+'+COLUMN_NAME+ '+'''''','','
when DATA_TYPE='nchar' THEN '''''''''+'+COLUMN_NAME+ '+'''''','','
when DATA_TYPE='text' THEN '''''''''+'+COLUMN_NAME+ '+'''''','','
when DATA_TYPE='ntext' THEN '''''''''+'+COLUMN_NAME+ '+'''''','','
when DATA_TYPE='datetime' THEN '''''''''+convert(varchar,'+COLUMN_NAME+ ',121)+'''''','','
when DATA_TYPE='smalldatetime' THEN '''''''''+convert(varchar,'+COLUMN_NAME+ ',121)+'''''','','
else '''''+cast('+COLUMN_NAME+' as nvarchar)+'','','
end,''
)
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE not in ( 'timestamp','image','sql_variant')
and TABLE_NAME=@tableName

SET @sql=left(@sql,len(@sql)-3)
set @sql=@sql+''' from '+@tableName
print (@sql)
--exec (@sql)
end

Tree in a desert

Tree in a desert

Attention IE users

Are you using Internet Explorer for browsing? Why dont you try firefox instead of IE.Download and experience it. You ll see the difference.it just a click away from by site to download Firefox.Click on image in my site(at the top) where you can see Firefox.Cheers.

Cats that look like Hitler (Kitlers :))

Click here to see more
Cats that look like Hitler

My frequent SQL forum

My frequent sql forum

Strange behavior of MS SQL

Strange behavior of MS SQL
Let me show that with an example.

set nocount on
--- create table----
CREATE TABLE #partMaster (
PART_CODE VARCHAR(18),
PART_TYPE CHAR(1),
PART_STATUS CHAR(1),
)
go
CREATE TABLE #master(
PRODUCT VARCHAR(18)
)

go
---insert statement
INSERT INTO #partMaster (PART_CODE, PART_TYPE, PART_STATUS)
SELECT 'ABC123', 'F', 'A' UNION ALL
SELECT 'DEF123','K','A' UNION ALL
SELECT 'ABC456','R','A' UNION ALL
SELECT 'DEF456','R','I'

INSERT INTO #master(PRODUCT)
SELECT 'ABC123' UNION ALL
SELECT 'DEF123' UNION ALL
SELECT 'ABC456' UNION ALL
SELECT 'DEF456'

go
--- select statement before delete
select * from #partMaster
select * from #master
go
--delete statement
DELETE FROM #master
WHERE PRODUCT IN ( SELECT PRODUCT
FROM #partMaster
WHERE PART_STATUS IN ('I', 'D')
AND PART_TYPE NOT IN ('F', 'N', 'K', 'D')
)
GO
--- select statement after delete
select * from #partMaster
select * from #master
go
--drop table
drop table #master
drop table #partMaster


oops! all the records in #master is gone.
u can see there is no 'PRODUCT' column in #partMaster,which is in the delete statement.We expect sql will throw error.No!,it will delete all the records in #master.
whats happened is sql server run time engine would look 'PRODUCT' column first in #partMaster,it would look in #master table if it is not found in #partMaster.All condition matches here in where clause 'PRODUCT=PRODUCT' all records will delete.

Now u think this case wont happened.yes it can,Imagine u had column 'PRODUCT' in #partMaster and dropped later but missed to change ur scripts.

Beware!

Dynamic Crosstab in SQL Server

create PROC sp_CrossTab
@table AS sysname, -- Table to crosstab
@onrows AS nvarchar(500), -- Grouping key values (on rows)
@onrowsalias AS sysname = NULL, -- Alias for grouping column
@oncols AS nvarchar(1000), -- Destination columns (on columns)
@sumcol AS sysname = NULL, -- Data cells
@whereclause as nvarchar(500)= NULL -- where clause
AS
DECLARE
@sql AS varchar(8000),
@NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

-- step 1: beginning of SQL string
SET @sql =
'SELECT' + @NEWLINE +
' ' + @onrows +
CASE
WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
ELSE ''
END


CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(1000)
SET @keyssql =
'INSERT INTO #keys ' +
'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
'FROM ' + @table

print @keyssql
EXEC (@keyssql)


DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys

WHILE @key IS NOT NULL
BEGIN
SET @sql = @sql + ',' + @NEWLINE +
' SUM(CASE CAST(' + @oncols +
' AS nvarchar(100))' + @NEWLINE +
' WHEN N''' + @key +
''' THEN ' + CASE
WHEN @sumcol IS NULL THEN '1'
ELSE @sumcol
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS [' + @key+ ']'

SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
END

SET @sql = @sql + @NEWLINE +
'FROM ' + @table + @NEWLINE
if @whereclause is not null
SET @sql = @sql+ 'WHERE ' + @whereclause + ' '+ @NEWLINE
SET @sql = @sql+ 'GROUP BY ' + @onrows + @NEWLINE
SET @sql = @sql+ 'ORDER BY ' + @onrows

PRINT @sql + @NEWLINE -- For debug
EXEC (@sql)

---Reference SQL Magazine
----------------------------------------
Eg:

Use Northwind
go
EXEC sp_CrossTab
@table = 'Orders',
@onrows = 'MONTH(OrderDate)',
@onrowsalias = 'OrderMonth',
@oncols = 'YEAR(OrderDate)'


USE pubs
go
EXEC sp_CrossTab
@table = 'sales',
@onrows = 'stor_id',
@oncols = 'YEAR(ord_date)',
@sumcol = 'qty'

How to change the collation against all the tables and columns within a database

Onlyway is to create alter statement from INFORMATION_SCHEMA.COLUMNS.

SELECT
'ALTER TABLE ' + TABLE_NAME +
' ALTER COLUMN ' + COLUMN_NAME +
' ' + DATA_TYPE +' '+
CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
END
+' COLLATE SQL_Latin1_General_CP850_BIN '+
CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END

FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')

A good source for SQL Server DBA

Core DBA
and
Transactional SQL


thanks to Randy Dyess for the Article.

SQLServer -Store Multiple Values in a Single Value

Question(http://www.dbforums.com/showthread.php?t=1201269)
I have a table called SEARCHCRITERIA. It consists of a USERID column and a CRITERIA column. Users will be able to search for other users based on a set of criteria. There are 5 total criteria a user can choose. They can choose as few as none or all five. I'd like to store the criteria chosen as a single number in the SEARCHCRITERIA table. Then use a function to parse out the criteria. For example:

CRITERIAID CRITERIA CRITERIAVALUE
1 AGE 2
2 SEX 4
3 GRADE 8
4 LOCALE 16
5 REGION 32

Answer

--Yes its possible using bitwise operation and (&) operator


eg:
--creating table

create table #c
(
CRITERIAID int,
CRITERIA varchar(100),
CRITERIAVALUE int
)
go
create table #search
(
userid int,
searchSumValue int
)
go
---insert sample records
set nocount on
insert into #c values (1,'Age',2)
insert into #c values (2,'sex',4)
insert into #c values (3,'Grade',8)
insert into #c values (4,'Locale',16)
insert into #c values (5,'Region',32)
go

----sample data in search table

insert into #search select 1,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (2,4,16)
insert into #search select 2,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (4,8,32)
insert into #search select 3,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (2,4,8,32)
insert into #search select 4,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (2,4,8,16,32)
insert into #search select 5,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (2)
insert into #search select 6,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (16,32)
go

-----------select multiple values using bitwise (&) operator------

--userid 1 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=1
--userid 2 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=2

--userid 3 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=3
--userid 4 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=4

--userid 5 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=5
--userid 6 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=6

Indian Yoga Vs Irish Yoga

For all you yoga fans out there......

Indian Yoga














Irish Yoga