Archive

Posts Tagged ‘sql’

Insert comma-delimited string value to table column in Sql

March 3, 2010 1 comment

I have string like ‘1,22,3,49,6,7,8’

I want to insert each comma separated value in a table column…my output look like this:

COLUMN
1
22
3
49
6
7
8

Here is the solution:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        <Author,,Name>
— Create date: <Create Date,,>
— Description:    <Description,,>

— =============================================
create PROCEDURE [dbo].[sp_InsertCommaSeparatedValues]

AS
BEGIN

DECLARE @FormId VARCHAR(5000)
DECLARE @FormRead varchar(5000)
DECLARE @FormWrite varchar(5000)

SET @FormId = ‘1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,’
SET @FormRead =’1,0,1,0,1,0,1,0,0,0,1,1,1,1,1,1,1,1,1,’
SET @FormWrite = ‘1,0,1,0,1,0,1,0,0,0,1,1,1,1,1,1,1,1,1,’

WHILE (CHARINDEX(‘,’, @FormId) > 0)
BEGIN

DECLARE @Form VARCHAR(30)
SET @Form = SUBSTRING(@FormId,0, CHARINDEX(‘,’, @FormId))
PRINT @Form

DECLARE @FRead VARCHAR(30)
SET @FRead = SUBSTRING(@FormRead,0, CHARINDEX(‘,’, @FormRead))
PRINT @FRead

DECLARE @FWrite VARCHAR(30)
SET @FWrite = SUBSTRING(@FormWrite,0, CHARINDEX(‘,’, @FormWrite))
PRINT @FWrite

INSERT INTO tbl_UserFormRights(Fk_UserId,Fk_FormId,[Read],Write)
VALUES(1,@Form,@FRead,@FWrite)

SET @FormId = SUBSTRING(@FormId, CHARINDEX(‘,’, @FormId) + 1, LEN(@FormId))
SET @FormRead  = SUBSTRING(@FormRead, CHARINDEX(‘,’, @FormRead) + 1, LEN(@FormRead))
SET @FormWrite  = SUBSTRING(@FormWrite, CHARINDEX(‘,’, @FormWrite) + 1, LEN(@FormWrite))

END

END

Migratation Tool for Sql Database To MySql Database

February 15, 2010 Leave a comment

Various migration tools are available for converting sql database to mysql database.  I personally use the Ispirer – Sqlways migration tool for my projects,  it supports conversion of tables and stored procedures ,you can download it from below:

http://www.ispirer.com/download

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

August 25, 2009 3 comments

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.

Learn Sql Server 2008 New Features With Demos, Presentations

May 25, 2009 1 comment

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

Sql Server Functions

February 24, 2009 Leave a comment

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/