Help on sql - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help on sql

Author

Topic

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2008-09-17 : 19:28:10

CREATE TABLE TableMaster
(
table_name VARCHAR(500)
)

INSERT INTO TableMaster (table_name) VALUES ( 'datatable1' )
INSERT INTO TableMaster (table_name) VALUES ( 'datatable2' )
.
.

INSERT INTO TableMaster (table_name) VALUES ( 'datatable10' )

datatable1, datatable2 .. are physical tables IN DATABASE.
TableMaster will KEEP growing WITH TABLE names IN DATABASE.

Assuming each TABLE has COLUMN number. i need TO UPDATE this number FOR ALL tables IN TableMaster

how i can do without CURSOR.?

mk_garg

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-09-17 : 22:36:57

Is there a reason why you don't get this info from INFORMATION_SCHEMA.tables OR
SELECT *
FROM sysobjects
where type = 'U' ?

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2008-09-17 : 22:42:35

Yes TableMaster will store tables i need to update.
even if i get it from INFORMATION_SCHEMA.table, i can get list of tables.

Then i need to update listed table.
is their any way to update all tables in one go instead of using cursor to update 1 at a time.

Thanks

mk_garg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 00:16:43

quote:
Originally posted by mk_garg20

Yes TableMaster will store tables i need to update.
even if i get it from INFORMATION_SCHEMA.table, i can get list of tables.

Then i need to update listed table.
is their any way to update all tables in one go instead of using cursor to update 1 at a time.

Thanks

mk_garg

update each tables? with what data?

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2008-09-18 : 00:32:44

just update number = number * 2 will do the job..

thanks

mk_garg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 00:37:40

quote:
Originally posted by mk_garg20

just update number = number * 2 will do the job..

thanks

mk_garg

do all tables have the number column?

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2008-09-18 : 00:44:59

yes

mk_garg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 01:00:51

quote:
Originally posted by mk_garg20

yes

mk_garg

DECLARE @Table varchar(100)

SELECT @Table=MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES


WHILE @Table IS NOT NULL
BEGIN
SET @Sql='Update '+@Table + ' SET number=number * 2'
EXEC (@Sql)
SELECT @Table=MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME>@Table
END

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2008-09-18 : 01:25:00

OK thanks

Any other solution to avoid dynamic sql.

Thanks

mk_garg

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 01:53:41

quote:
Originally posted by mk_garg20

OK thanks

Any other solution to avoid dynamic sql.

Thanks

mk_garg

EXEC sp_MSforeachtable 'UPDATE ? SET number=number*2'

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2008-09-18 : 01:57:50

Thanks

mk_garg

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-18 : 03:54:31

Make use of the result and run

SELECT 'Update '+TABLE_NAME+' set number=number * 2'
FROM INFORMATION_SCHEMA.TABLES

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources