query equivalent: t-sql

Showing posts with label t-sql. Show all posts

Wednesday, March 28, 2012

lop deleted row

Is it possible to find who was deleted row from sql server 2000 with t-sql,
query analyzer?No. but you can use third-party tools:
www.lumigent.com
www.red-gate.com
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Nikolami" <nikolami@.nyc.com> wrote in message
news:dv3qam$40u$1@.ss405.t-com.hr...
Is it possible to find who was deleted row from sql server 2000 with t-sql,
query analyzer?|||After the fact? Not really, SQL Server doesn't track that information. You
may be able to recover some of the transaction details by using a log
reader, e.g. see http://www.aspfaq.com/2449
"Nikolami" <nikolami@.nyc.com> wrote in message
news:dv3qam$40u$1@.ss405.t-com.hr...
> Is it possible to find who was deleted row from sql server 2000 with
> t-sql, query analyzer?
>|||Not if you don=B4t save that information somewhere, or you use a
logreader for this.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks guys in advice

Wednesday, March 21, 2012

loop through a tables columns

is there a way using t-sql to loop through a tables columns?
i want to build a long string with this type of logic
foreach (column in table.columns)
{
strValues += column.name + "=" + row.value
}
thank you for your helpAbraham,
Can you give me some sample data and desired results to work with?
Not exactly sure what you're asking for.
HTH
Jerry
"Abraham Andres Luna" <abe@.rdk.com> wrote in message
news:e8wfGkB0FHA.2460@.TK2MSFTNGP10.phx.gbl...
> is there a way using t-sql to loop through a tables columns?
> i want to build a long string with this type of logic
> foreach (column in table.columns)
> {
> strValues += column.name + "=" + row.value
> }
> thank you for your help
>|||What are you trying to accomplish? Is this supposed to be some kind of
data interchange format? If so, how about using SELECT ... FOR XML to
generate an XML fragment instead?
If you really want to do this dynamically then take a look at this proc
for inspiration:
http://vyaskn.tripod.com/code/generate_inserts.txt
David Portas
SQL Server MVP
--|||Abraham Andres Luna wrote:
> is there a way using t-sql to loop through a tables columns?
> i want to build a long string with this type of logic
> foreach (column in table.columns)
> {
> strValues += column.name + "=" + row.value
> }
>
No, you will need to use a client tool for this. The best you can do in
T-SQL is (air code):
select 'col1=' + cast(col1 as varchar) + ... + '; colN=' + colN from table
I know you want to avoid this exercise, but the only way would be to use a
client tool: for example: an ADO Recordset.
Oh, I suppose you could go to a great deal of trouble to create a dynamic
sql statement by looping through the result of querying the
information_schema.columns view, but I would not advise this.
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||ty for the answer, that proc was a big help
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129225553.008794.38510@.g14g2000cwa.googlegroups.com...
> What are you trying to accomplish? Is this supposed to be some kind of
> data interchange format? If so, how about using SELECT ... FOR XML to
> generate an XML fragment instead?
> If you really want to do this dynamically then take a look at this proc
> for inspiration:
> http://vyaskn.tripod.com/code/generate_inserts.txt
> --
> David Portas
> SQL Server MVP
> --
>

Monday, March 19, 2012

lookup values in another table

alright, I'm sure this is a simple solution, but I really don't know much about T-SQL. I have two tables, [temp], and [SectionChanges]. I am using SQL 2005 SP2. Here are the schemas:

Code Snippet

[dbo].[temp](

[Section] [smallint] NOT NULL,

[Mnemonic] [nvarchar](50) NOT NULL,

[Test Name] [nvarchar](200) NOT NULL,

[Date] [smalldatetime] NOT NULL,

[Patient Type] [smallint] NOT NULL,

[Nurse Unit] [nvarchar](20) NULL,

[Client#] [int] NOT NULL,

[Item_Qty] [smallint] NOT NULL,

[InputDate] [smalldatetime] NOT NULL,

[Bill_Item_ID] [int] NOT NULL,

[Charge_Item_ID] [int] NOT NULL

)

[dbo].[SectionChanges](

[Mnemonic] [nvarchar](50) NOT NULL,

[Test Name] [nvarchar](200) NOT NULL,

[Section] [smallint] NOT NULL

)

The data I want to use is:

Code Snippet

SELECT Mnemonic, [Test Name], Section, Bill_Item_ID
FROM temp
WHERE (Section = 0) OR
(Section >= 18)

I want to match the two tables on Mnemonic AND [Test Name] and then update the temp table with the value of section from SectionChanges

Thank you all.

Try this:

Code Snippet

UPDATE t

SET Section = sc.Section

FROM temp t

INNER JOIN SectionChanges sc

ON t.Mnemonic = sc.Mnemonic

AND t.[Test Name] = sc.[Test Name]

WHERE (Section = 0) OR

(Section >= 18)

|||Thanks much!|||

I'm sorry, it worked once, but now it isn't working any more. I now get the error:

Column or expression 'Section' cannot be updated.

This is the exact query I was using when it worked before

Code Snippet

UPDATE t
SET Section = sc.Section
FROM temp t
INNER JOIN SectionChanges sc
ON t.Mnemonic = sc.Mnemonic
AND t.[Test Name] = sc.[Test Name]
WHERE (t.Section = 0) OR
(t.Section >= 17)

|||

Have you changed anything (ie, table definition, etc.) since the first time you ran it?

No changes to the update code?

|||

|||alright I got it to work again but I'm baffled. The result changes depending on the query window i enter it. In Management studio, if i right click a table and click open table, then open up the query text and replace it with the update query, it gives that error. If i right click the table and goto script table as update and then paste the query, it works fine. It also doesnt work in a SQL task in SSIS.|||

I had to change the where clause to add the alias to the Section column name (I had just copied it from your code before)

but this works:

Code Snippet

create table [dbo].[temp](

[Section] [smallint] NOT NULL,

[Mnemonic] [nvarchar](50) NOT NULL,

[Test Name] [nvarchar](200) NOT NULL,

[Date] [smalldatetime] NOT NULL,

[Patient Type] [smallint] NOT NULL,

[Nurse Unit] [nvarchar](20) NULL,

[Client#] [int] NOT NULL,

[Item_Qty] [smallint] NOT NULL,

[InputDate] [smalldatetime] NOT NULL,

[Bill_Item_ID] [int] NOT NULL,

[Charge_Item_ID] [int] NOT NULL

)

create table [dbo].[SectionChanges](

[Mnemonic] [nvarchar](50) NOT NULL,

[Test Name] [nvarchar](200) NOT NULL,

[Section] [smallint] NOT NULL

)

insert into dbo.temp values (0, 'code1', 'test1', getdate(), 0, '1', 1, 1, getdate(), 0, 0)

insert into dbo.temp values (0, 'code2', 'test2', getdate(), 0, '1', 1, 1, getdate(), 0, 0)

insert into dbo.temp values (0, 'code3', 'test3', getdate(), 0, '1', 1, 1, getdate(), 0, 0)

insert into dbo.SectionChanges values('code1', 'test1', 100)

insert into dbo.SectionChanges values('code2', 'test2', 200)

insert into dbo.SectionChanges values('code3', 'test3', 300)

UPDATE t

SET Section = sc.Section

FROM temp t

INNER JOIN SectionChanges sc

ON t.Mnemonic = sc.Mnemonic

AND t.[Test Name] = sc.[Test Name]

WHERE (sc.Section = 0) OR

(sc.Section >= 18)

select *

from temp

|||

When I right clicked on [temp] and chose "Open Table" and then replaced the SQL with the update code, it gave me the same error that you received.

I noticed though that SSMS modified the script by inserting "CROSS APPLY t" before the WHERE clause.

Removing this cross apply gets rid of the problem.

(I've never used the SQL window on an OPEN TABLE to do anything like this, I usually use "New Query"...so I have no idea why SSMS decided to change what was pasted in the window.)

As for the SSIS, can you tell me what about it isn't working? Is there an error message? What all is going on in and around the SQL Task causing the problem. Please post anything can about it.

|||

Thank you very much for your help, I found out that SSIS was just a dumb mistake on my part. It's really strange that that query window gives different results and different errors. This was 1000 times more efficent than what I was trying to do before in SSIS.

Friday, March 9, 2012

looking for T-SQL Editor !

Hi,
i looking for good T-SQL editor beside QA.
thanks,Hi,
You cant get a better editor than Query Analyzer. Still for your information
you can use 3rd party tools like
1. DBArtisan
2. BMC Patrol ....
Both the above are very costly, But to connect to SQL server you need to
have SQL server client installed in the PC.
Thanks
Hari
MCDBA
"BY" <by@.yahoo.com> wrote in message
news:ezDI4nH8DHA.2524@.TK2MSFTNGP11.phx.gbl...
> Hi,
> i looking for good T-SQL editor beside QA.
> thanks,
>

looking for T-SQL Editor !

Saturday, February 25, 2012

Looking for method to create stem and leaf graph

All,

I would appreciate any advice anyone might have to offer on how to approach creating a stem-and-leaf graph using t-sql. My table structure is as follows:

CREATE TABLE [dbo].[Responses](
[appraisalId] [char](9) NOT NULL,
[userId] [char](9) NOT NULL,
[res1] [decimal](5, 2) NOT NULL,
[res2] [decimal](5, 2) NOT NULL,
[res3] [decimal](5, 3) NOT NULL,
[res4] [decimal](5, 2) NOT NULL,
[res5] [decimal](5, 2) NOT NULL,
CONSTRAINT [PK_Responses] PRIMARY KEY CLUSTERED
(
[appraisalId] ASC,
[userId] ASC
))

ALTER TABLE [dbo].[Responses] WITH CHECK ADD CONSTRAINT [CK_Responses_res1] CHECK (([res1]>=(1) AND [res1]<=(5)))
ALTER TABLE [dbo].[Responses] WITH CHECK ADD CONSTRAINT [CK_Responses_res2] CHECK (([res2]>=(1) AND [res2]<=(5)))
ALTER TABLE [dbo].[Responses] WITH CHECK ADD CONSTRAINT [CK_Responses_res3] CHECK (([res3]>=(1) AND [res3]<=(5)))
ALTER TABLE [dbo].[Responses] WITH CHECK ADD CONSTRAINT [CK_Responses_res4] CHECK (([res4]>=(1) AND [res4]<=(5)))
ALTER TABLE [dbo].[Responses] WITH CHECK ADD CONSTRAINT [CK_Responses_res5] CHECK (([res5]>=(1) AND [res5]<=(5)))

The problem to be solved is to create a stem and leaf graph that shows for each response value column res1 to res5, with valid values 1.0 - 5.0, create a graph similar to the following:

5|11233445567789
4|12233445566789
3|11334556667789
2|11223444567889
1|12233345556778

It was my hope that I could do this using reporting services, but I'll almost settle for being able to produce a two column result set similar to the above.

Best,
Steven

Please post some sample data (input) & expected sample output data.

It will help us to understand the problem quickly..

|||I believe at this point I've come up with a solution that will work. Perhaps the task of just writing down here what little bit I did helped me to think more clearly about the problem to solve.

I was playing around with a stacked bar chart, and experimented with a custom fill, and wound up with something that actually looks quite like what I wanted.

Thanks for the assist!

Best,
B.

Looking for method to create stem and leaf graph

All,

I would appreciate any advice anyone might have to offer on how to approach creating a stem-and-leaf graph using t-sql. My table structure is as follows:

The problem to be solved is to create a stem and leaf graph that shows for each response value column res1 to res5, with valid values 1.0 - 5.0, create a graph similar to the following:

5|11233445567789
4|12233445566789
3|11334556667789
2|11223444567889
1|12233345556778

It was my hope that I could do this using reporting services, but I'll almost settle for being able to produce a two column result set similar to the above.

Best,
Steven

Please post some sample data (input) & expected sample output data.

It will help us to understand the problem quickly..

|||I believe at this point I've come up with a solution that will work. Perhaps the task of just writing down here what little bit I did helped me to think more clearly about the problem to solve.

I was playing around with a stacked bar chart, and experimented with a custom fill, and wound up with something that actually looks quite like what I wanted.

Thanks for the assist!

Best,
B.

Monday, February 20, 2012

looking for help to parse in t-sql

Hello,
I have a column of varchar that has data of 4-5 I need to manipulate the
data in a query to get it into another table to where the value would be
0040005. How could I accomplish this by parsing out the -? Other data could
be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would be
appreciated. Thanks.
JakeHope this helps
CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
REPLACE(col1,'-', '00'),7)
WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
REPLACE(col1,'-', '0'),7)
END
FROM #test
Thanks
GYK
"Jake" wrote:
> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate the
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data could
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would be
> appreciated. Thanks.
> Jake
>
>|||GYK,
This is great thank you. If possible could you walk me through this so I
could learn how it worked? I got the charindex but and confused with teh
reverse and then right +replace idea. I truly appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...
> Hope this helps
> CREATE TABLE #test
> (col1 VARCHAR(5))
> INSERT INTO #test VALUES('4-5')
> INSERT INTO #test VALUES('12-99')
> INSERT INTO #test VALUES('1-234')
>
> SELECT
> CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '00'),7)
> WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
> REPLACE(col1,'-', '0'),7)
> END
> FROM #test
> Thanks
> GYK
> "Jake" wrote:
>> Hello,
>> I have a column of varchar that has data of 4-5 I need to manipulate
>> the
>> data in a query to get it into another table to where the value would be
>> 0040005. How could I accomplish this by parsing out the -? Other data
>> could
>> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would
>> be
>> appreciated. Thanks.
>> Jake
>>|||Let us consider one statement as an example:
WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
+REPLACE(col1,'-', '000'),7)
--> First goal is to find out at what position '-' is available. So I used
charindex
--> But the position of '-' is not determined from left of the given
characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
did the reverse of those characters. So that charindex is calculated from the
right of the char.
--> Replace function just replaces '-' with the specified characters . In
this case its '000'
--> Next step is to pad zeros to the left of the char. And you want to
retain the existing characters. So after the length of 40005, zero's will be
padded till the length becomes 7. See function RIGHT in BOL
Then I used case function to determine the position of '-' and the number of
zeros it should be replaced with.
If you generally look at these kind of statements, it looks complex but if
you breakdown into indivdual sections (from the inner params), it is pretty
easy.
HTH
GYK
"Jake" wrote:
> GYK,
> This is great thank you. If possible could you walk me through this so I
> could learn how it worked? I got the charindex but and confused with teh
> reverse and then right +replace idea. I truly appreciate it.
> Jake
>
> "GYK" <GYK@.discussions.microsoft.com> wrote in message
> news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...
> > Hope this helps
> >
> > CREATE TABLE #test
> > (col1 VARCHAR(5))
> >
> > INSERT INTO #test VALUES('4-5')
> > INSERT INTO #test VALUES('12-99')
> > INSERT INTO #test VALUES('1-234')
> >
> >
> > SELECT
> > CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> > +REPLACE(col1,'-', '000'),7)
> > WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
> > REPLACE(col1,'-', '00'),7)
> > WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
> > REPLACE(col1,'-', '0'),7)
> > END
> > FROM #test
> >
> > Thanks
> > GYK
> >
> > "Jake" wrote:
> >
> >> Hello,
> >>
> >> I have a column of varchar that has data of 4-5 I need to manipulate
> >> the
> >> data in a query to get it into another table to where the value would be
> >> 0040005. How could I accomplish this by parsing out the -? Other data
> >> could
> >> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would
> >> be
> >> appreciated. Thanks.
> >>
> >> Jake
> >>
> >>
> >>
>
>|||GYK
Thanks for the break down I appreciate it.
Jake
"GYK" <GYK@.discussions.microsoft.com> wrote in message
news:F960FC9A-7F2A-44D1-911D-4AB5E016CC9C@.microsoft.com...
> Let us consider one statement as an example:
> WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
> +REPLACE(col1,'-', '000'),7)
> --> First goal is to find out at what position '-' is available. So I used
> charindex
> --> But the position of '-' is not determined from left of the given
> characters. For ex: charindex position is 2 for both 1st and 3rd row. So I
> did the reverse of those characters. So that charindex is calculated from
> the
> right of the char.
> --> Replace function just replaces '-' with the specified characters . In
> this case its '000'
> --> Next step is to pad zeros to the left of the char. And you want to
> retain the existing characters. So after the length of 40005, zero's will
> be
> padded till the length becomes 7. See function RIGHT in BOL
> Then I used case function to determine the position of '-' and the number
> of
> zeros it should be replaced with.
> If you generally look at these kind of statements, it looks complex but if
> you breakdown into indivdual sections (from the inner params), it is
> pretty
> easy.
> HTH
> GYK
>
> "Jake" wrote:
>> GYK,
>> This is great thank you. If possible could you walk me through this
>> so I
>> could learn how it worked? I got the charindex but and confused with teh
>> reverse and then right +replace idea. I truly appreciate it.
>> Jake
>>
>> "GYK" <GYK@.discussions.microsoft.com> wrote in message
>> news:6DDBD65E-2D05-4560-9E27-9E2CE9488774@.microsoft.com...
>> > Hope this helps
>> >
>> > CREATE TABLE #test
>> > (col1 VARCHAR(5))
>> >
>> > INSERT INTO #test VALUES('4-5')
>> > INSERT INTO #test VALUES('12-99')
>> > INSERT INTO #test VALUES('1-234')
>> >
>> >
>> > SELECT
>> > CASE WHEN CHARINDEX('-', REVERSE(col1)) = 2 THEN RIGHT('0000000'
>> > +REPLACE(col1,'-', '000'),7)
>> > WHEN CHARINDEX('-', REVERSE(col1)) = 3 THEN RIGHT('0000000' +
>> > REPLACE(col1,'-', '00'),7)
>> > WHEN CHARINDEX('-', REVERSE(col1)) = 4 THEN RIGHT('0000000' +
>> > REPLACE(col1,'-', '0'),7)
>> > END
>> > FROM #test
>> >
>> > Thanks
>> > GYK
>> >
>> > "Jake" wrote:
>> >
>> >> Hello,
>> >>
>> >> I have a column of varchar that has data of 4-5 I need to
>> >> manipulate
>> >> the
>> >> data in a query to get it into another table to where the value would
>> >> be
>> >> 0040005. How could I accomplish this by parsing out the -? Other data
>> >> could
>> >> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help
>> >> would
>> >> be
>> >> appreciated. Thanks.
>> >>
>> >> Jake
>> >>
>> >>
>> >>
>>|||CREATE TABLE #test
(col1 VARCHAR(5))
INSERT INTO #test VALUES('4-5')
INSERT INTO #test VALUES('12-99')
INSERT INTO #test VALUES('1-234')
SELECT
RIGHT('000'+SUBSTRING(col1,1,CHARINDEX('-',col1)-1),4)+RIGHT('0000'+SUBSTRIN
G(col1,CHARINDEX('-',col1)+1,10),5)
FROM #test
Gary
"Jake" <rondican@.hotmail.com> wrote in message
news:%23wbOrnlzEHA.2716@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a column of varchar that has data of 4-5 I need to manipulate
the
> data in a query to get it into another table to where the value would be
> 0040005. How could I accomplish this by parsing out the -? Other data
could
> be 12-99, 1-234 which in turn should be 0120099, 0010234. Any help would
be
> appreciated. Thanks.
> Jake
>

looking for help to parse in t-sql

Looking For Examples Of Stored Procedures in C# or VB.Net.

Hi,

Does anyone have any links to examples of stored procedures written in C# or VB.Net instead of T-Sql. I'd like to see how it looks and works. Thanks! Ken.Hi Ken,

Here are some links for writing stored procedures in .Net compatible language.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlclrguidance.asp
http://www.sqlteam.com/item.asp?ItemID=21927

If you have any concerns please revert back, i will send you the sample code.

Best Regards,
Swamy|||The SQL Server Hands On Labs have examples:

http://msdn.microsoft.com/sql/2005/2005labs/default.aspx

See the SQL CLR lab.|||There are several examples available if you installed the samples and documentation. They are located in the C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR by default.

Wednesday, March 28, 2012

lop deleted row

Wednesday, March 21, 2012

loop through a tables columns

Monday, March 19, 2012

lookup values in another table

Friday, March 9, 2012

looking for T-SQL Editor !

looking for T-SQL Editor !

Saturday, February 25, 2012

Looking for method to create stem and leaf graph

Looking for method to create stem and leaf graph

Monday, February 20, 2012

looking for help to parse in t-sql

looking for help to parse in t-sql

looking for help to parse in t-sql

Looking For Examples Of Stored Procedures in C# or VB.Net.

query equivalent

Blog Archive

About Me