Couple of queries in 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)

Couple of queries in SQL

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 04:41:21

Hi..

i got a couple of queries
1)In the below script i would like to select only TABLE_NAME starts with 'LOAD_PAS' could u pls tell 'Where' to use 'like' statement in this script.

2)when i exectued first time script running fine with out any error when i executed second time i am getting an error
'Msg 2714, Level 16, State 6, Line 2
There is already an object named 'DAILY_RECORD_COUNT' in the database.'
i dont want to insert into new table every time when i executed is there any way to get rid of this error.

USE PAS_LOAD
(SELECT tab.name AS TABLE_NAME,

ind.rows as RECORD_COUNT
INTO DAILY_RECORD_COUNT

FROM sys.tables tab INNER JOIN

sys.sysindexes ind ON tab.object_id = ind.id AND ind.indid < 2);

Satya

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-15 : 04:54:31

Just put this at the end:

WHERE tab.Name LIKE 'LOAD_PAS%'

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 04:57:31

HI..
I already tried but iam getting an error message at 'where'.

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'WHERE'.

USE PAS_LOAD
(SELECT tab.name AS TABLE_NAME,

ind.rows as RECORD_COUNT
INTO DAILY_RECORD_COUNT

FROM sys.tables tab INNER JOIN

sys.sysindexes ind ON tab.object_id = ind.id AND ind.indid < 2)
WHERE TABLE_NAME LIKE 'LOAD_PAS%'

Satya

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-15 : 05:21:58

Sorry, I didn't notice the braces the first time. Get rid of them, you don't need them.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Sachin.Nand

2937 Posts

Posted - 2010-04-15 : 05:22:38

SELECT * FROM(SELECT tab.name AS TABLE_NAME,

ind.rows as RECORD_COUNT
INTO DAILY_RECORD_COUNT

FROM sys.tables tab INNER JOIN

sys.sysindexes ind ON tab.object_id = ind.id AND ind.indid < 2)T
WHERE TABLE_NAME LIKE 'LOAD_PAS%'

PBUH

satya068
Posting Yak Master

233 Posts

Posted - 2010-04-15 : 05:44:07

hi..could u tell me how to include getdate() as RUN_DATE inthe below SP

SELECT @SQL =
'Insert into DAILY_RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*)as ROW_COUNT ' +

'FROM ['+ @TableName+']'

Satya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources