Simple update/set should be working

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

Simple update/set should be working

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-03 : 14:53:40

SQL 2008 R2

So i have an excel project which uses MSQuery to pull data from a sql table, query as to start and end dates for the data desired, and creates a pivot chart for the end user. Traditionally, what i've done is export the data from sql into excel and create a new pivot chart after farming out the date ranges i want. I want to eliminate some of those export and hack steps by using MSQuery in excel to link to the sql table directly and allow me to specify the date range for the pivot chart. The only problem is, the data in a number of the columns is an integer followed by a "KB" label. I need to strip that "KB" garbage from each entry before the MSQuery sees it, or the data in the pivot chart will bomb. So, what i've done, on the sql side, is to create a "like" table in the same db (dbtablestats_nolabels) and will link my MSQuery to THAT table. I'm writing a script to update the data in dbtablestats_nolabels with the data in dbtablestats (the table WITH the "KB" labels"). Once those columns are updated, i then run a second set of Update/Set commands against the data in the new table to REPLACE the 'KB' after each integer with ''. That will leave all of the necessary values in a clean integer format for excel to build the pivotchart from. The problem is, i'm getting arguments from sql on my script to do the first set of updates. My update statement looks like this:

USE sizing_db
SELECT NAME, ROWS, reserved, DATA, index_size, unused, datecreated, uniquekey FROM DBTableStats ds
UPDATE DBTableStats_nolabels
SET NAME=Name FROM DBTableStats, ROWS=ROWS FROM DBTableStats, DATA=DATA FROM DBTableStats, Index_size=index_size FROM dbtablestats, unused=unused FROM dbtablestats, datecreated=datecreated FROM dbtablestats, uniquekey=uniquekey FROM dbtablestats
GO

UPDATE DBTableStats_nolabels
SET
reserved=REPLACE (reserved, 'KB', ''),
[data]=REPLACE ([data], 'KB',''),
index_size=REPLACE (index_size, 'KB',''),
unused=REPLACE (unused,'KB','')

I'm getting "ambiguous column name" very early on in the execution of the script.

Any observations or ideas are appreciated.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-03 : 15:02:29

First

You are making a copy of data, probably, into another table that you want to modify

Just use the sql statement that generates whatever report you want to display it as you want it from the original table

Second

I didn't read the manifesto

Third

Works for me


CREATE TABLE #DBTableStats_nolabels (reserved varchar, [data] varchar, index_size varchar, unused varchar)
GO

 UPDATE #DBTableStats_nolabels
	SET reserved=REPLACE (reserved, 'KB', '')
	, [data]=REPLACE ([data], 'KB','')
	, index_size=REPLACE (index_size, 'KB','')
	, unused=REPLACE (unused,'KB','')
GO

DROP TABLE #DBTableStats_nolabels
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-03 : 15:04:27

And the first part, all you need is

SELECT NAME, ROWS, reserved, DATA, index_size, unused, datecreated, uniquekey FROM DBTableStats ds
INTO DBTableStats_nolabels

But please take my advice and don't do this

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-03 : 15:07:02

It's a system stored proc that generates the data in dbtablestats. i've already proposed the idea of modifying it (the sproc) in terms of how it outputs the data, minus the "KB"'s on those columns. I was denied by powers above my pay grade for such as this output has several purposes, at least one of which "likes" to see the KB label on the data.

I don't.

I lose.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-03 : 15:13:54

I see, so you basically want to do Math, Right?

I'm just guessing, BUT you can still do that if you want from the base table. For Example

SELECT SUM(REPLACE([reserved],'KB',''))
FROM DBTableStats

Does that work

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-03 : 15:21:24

Math - correct.
I will plug that in to the msquery on the excel side and see if it reads it correctly.

thanks

James

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-03 : 15:24:21

Let me/ us know..because if you have spaces or other chars, it will be a problem as well

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-03 : 15:38:18

well, it appears that the problem is that the data in the base table is set as varchars (which is why it is also carrying the "KB" characters) and varchar won't "sum". Before i decided to link directly to the sql table, i was using a vbscript within excel to strip the kb characters out of those columns and convert the remaining numerical characters to numerical values. It would seem that, at some stage in the process, i'm going to be doing a cast and/or convert to change these varchars to ints in sql. what say you?

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-03 : 15:51:44

WHAT a MAROON

D'oh

SELECT SUM(CONVERT(int,(REPLACE([reserved],'KB','')))
FROM DBTableStats

Someone told me about implicit conversions though, somewhere, one time

Does this return anything?

SELECT [reserved] FROM DBTableStats
WHERE ISNUMERIC(REPLACE([reserved],'KB','')) = 0

YES, I KNOW, that it's not 100% acurate

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-03 : 16:02:10

Ack. This is why i wanted to avoid doing the coding in MSQuery. it has a shiny button labeled "SQL" in it's editor., but the coding aint sql. it's sql-esque-ish-if. I'm still leaning toward my original idea of my "like" table on the sql side that i update to and just dump the nonnumerical chars from. i'm going on a hunger strike until i can accomplish it my way. (after i finish my last graham cracker). ugh.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-03 : 16:30:47

Are you using SQL Server Management Studio?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-03 : 16:34:47

indeed.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-03 : 16:37:44

so cut and paste the code in a query window and just execute it

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources