Update with new value in the column

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)

Update with new value in the column

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-29 : 04:13:14

Hi..
i would like to update my NO_OF_INSERTS column to 0,if there is no inserts for the perticular table .

this is my script for No_of_Inserts column,plz let me know wher to put the condition.

i am currently experiencing problem that all the rown in this column are replacing with total_no_of_records insted of no_of _inserts.

PRINT 'got to step 1'

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
'SELECT ''' + @TableName + '''as TABLE_NAME, Sum(case when INSERT_DATE >= ' + convert(varchar, @TODAY) + ' then 1 else 0 end) as INSERT_COUNT' +
' FROM [' + @TableName + ']'

print @SQL1
EXEC SP_EXECUTESQL @SQL1

Satya

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-29 : 04:22:19

I can't find any problem in your query!

But it will be a better replacement for your query!

Select @TableName,count(convert(varchar,INSERT_DATE,101)) from @TableName
where convert(varchar,INSERT_DATE,101)>=convert(varchar,@todat,101)
group by convert(varchar,INSERT_DATE,101)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-29 : 04:31:16

Hi..senthi,
i modified with appostrpies in the script and when executed error message

Msg 207, Level 16, State 1, Line 46
Invalid column name INSERT_DATE'.

this the modified script

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
'Select ''' + @TableName + '''as TABLE_NAME, count(convert(varchar,INSERT_DATE,101)) from @TableName
where convert(varchar,INSERT_DATE,101)> = ' + convert(varchar,@todaY,101)
group by convert(varchar,INSERT_DATE,101)

thanx

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 04:35:44

shouldnt it be?


SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
'Select ''' + @TableName + '''as TABLE_NAME, count(convert(varchar,INSERT_DATE,101)) from @TableName
where convert(varchar,INSERT_DATE,101)> = ' + convert(varchar,@todaY,101) +'
group by convert(varchar,'+ @TableName + ',101)'

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

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-03-29 : 04:40:15

quote:
Originally posted by satya068

Hi..senthi,
i modified with appostrpies in the script and when executed error message

Msg 207, Level 16, State 1, Line 46
Invalid column name INSERT_DATE'.

this the modified script

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
'Select ''' + @TableName + '''as TABLE_NAME, count(convert(varchar,INSERT_DATE,101)) from @TableName
where convert(varchar,INSERT_DATE,101)> = ' + convert(varchar,@todaY,101)
group by convert(varchar,INSERT_DATE,101)

thanx

Satya

Post your source table's DML Statement!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-29 : 04:44:56

visakh,its better now
still

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@TableName".

i already declared @TableName
at starting my script.

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 04:47:11

quote:
Originally posted by satya068

visakh,its better now
still

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@TableName".

i already declared @TableName
at starting my script.

Satya

oh you've one more instance inside


SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
'Select ''' + @TableName + '''as TABLE_NAME, count(convert(varchar,INSERT_DATE,101)) from '+ @TableName + '
where convert(varchar,INSERT_DATE,101)> = ' + convert(varchar,@todaY,101) +'
group by convert(varchar,'+ @TableName + ',101)'

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

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-29 : 04:53:33

senthil,my source table is a data base system table.
form that table i have taken only the coulumn whic is 'tablename'
and once i got all the tablename into new table which i created and i was trying to get INSERT_DATE from this RDB tables

this is my output

RDB_PAS_EAS_DETAILS_GEC 76412 76412 76412
RDB_PAS_URGENCY_CODE 6 6 6
RDB_PAS_REGISTRATION_DATA_TO_LOAD 1469 1469 1469
RDB_PAS_REGISTRATION_CONS_INIT_DATA_TO_LOAD 1789 1789 1789
RDB_PAS_WL_NAME_DATA_TO_LOAD 162 162 162

first column ROW_COUNT is fine but NO_OF_INSERTS AND NO_OF_UPDATES ALSO getting the same value ,it shouldent be like this,i am concern about this.

thanx

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-29 : 04:54:59

soo..visakh do u want me to declare that one variable again?

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 06:10:18

quote:
Originally posted by satya068

soo..visakh do u want me to declare that one variable again?

Satya

Nope..If you've already declared it then no need

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

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-29 : 06:17:44

visakh,

when executed an error message

Msg 207, Level 16, State 1, Line 3
Invalid column name 'RDB_PAS_EAS_DETAILS_GEC'.

this is the script

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
'Select ''' + @TableName + '''as TABLE_NAME, count(convert(varchar,INSERT_DATE,101)) from [' + @TableName + ']
where convert(varchar,INSERT_DATE ,101) >= ' + convert(varchar,@TODAY,101) + '
group by convert(varchar,'+ @TableName + ',101)'

Satya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 11:52:35

I cant see RDB_PAS_EAS_DETAILS_GEC in posted query. what are you passing as value for @TableName? Also it may be that error is throw by some other query, are you running this in a batch?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources