how to fetch data from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to fetch data from a table

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2010-04-08 : 07:32:25

Hi all,

I have created a sp to get the details of table name who column have varchar and nvarchar
as data type.now i want to fetch each table name and the corresponding column name and
have to print a update statemnt like this

update dbo.FIS_Organization

set --Full_Name = dbo.DecodeUnicodeData(Full_Name,'#',';'),

Abb_Name = dbo.DecodeUnicodeData(Abb_Name,'#',';')

how i can loop inside the temp table and fetch each data

procedure i have done is like this

CREATE PROCEDURE FSP_FunctionUpdate

AS
BEGIN
declare @Column VARCHAR(100)
declare @Table VARCHAR(100)
declare @txt AS VARCHAR(200)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

CREATE TABLE #TEST
(
ID INT IDENTITY(1,1),
TABLENAME NVARCHAR(200),
COLUMNNAME NVARCHAR(200)
)

INSERT INTO #TEST
(

TABLENAME ,
COLUMNNAME
)

SELECT DISTINCT col.TABLE_NAME,col.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS col

INNER JOIN sys.tables tab on tab.name=col.TABLE_NAME

WHERE tab.type ='u' and col.DATA_TYPE in ('varchar','text','nvarchar','char')

SELECT * FROM #TEST

END
GO

please help me in this issue

Regards,
Divya

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-08 : 07:42:43

Assuming all your tables are in the dbo schema, then:


SELECT 'UPDATE dbo.' + TABLENAME + ' SET ' + COLUMNNAME + 
' =dbo.DecodeUnicodeData(' + COLUMNNAME + ',''#'','';'')' 
FROM #TEST

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 07:53:56

do you need a separate temp table for this? I think seeing your query you can directly use INFORMATION_SCHEMA.COLUMNS view

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

divyaram
Posting Yak Master

180 Posts

Posted - 2010-04-08 : 08:12:33

quote:
Originally posted by visakh16

do you need a separate temp table for this? I think seeing your query you can directly use INFORMATION_SCHEMA.COLUMNS view

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

i have done like this....
how we can use view for that

ALTER PROCEDURE FSP_FunctionUpdate

AS
BEGIN
declare @Column VARCHAR(100)
declare @Table VARCHAR(100)
declare @txt AS VARCHAR(200)

DECLARE @MIN INT, @MAX INT
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

CREATE TABLE #TEST
(
ID INT IDENTITY(1,1),
TABLENAME NVARCHAR(200),
COLUMNNAME NVARCHAR(200)
)

INSERT INTO #TEST
(
TABLENAME ,
COLUMNNAME
)

SELECT DISTINCT col.TABLE_NAME,col.COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS col
INNER JOIN sys.tables tab on tab.name=col.TABLE_NAME
WHERE tab.type ='u' and col.DATA_TYPE in ('varchar','text','nvarchar','char')

SET @MIN =1
SELECT @MAX =COUNT(*) FROM #TEST

WHILE @MIN <=@MAX
BEGIN

SELECT @Column = COLUMNNAME,
@Table=TABLENAME
FROM #TEST
WHERE ID =@MIN
SET @txt = 'UPDATE '+@Table +' SET '+@Column+'='+'dbo.DecodeUnicodeData'+'('+@Column+','+'''#'','';'''+')'
PRINT @TXT

SET @MIN=@MIN +1
END
END
GO

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 08:20:49

nope i was just suggesting you can use views directly in query rather than putting result in temp table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources