.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Tsql

Posted By:      Posted Date: October 11, 2010    Points: 0   Category :Sql Server
 

i m having two databases 'lstock' and 'lstockhh' both the database contain tables with same names. the following query is for only one table present in the both databases'T_buffalow'. now there are more tables in both the database and when i m try to include those tables it is showing an error"such as i m having tables like T_camel,T_cow etc"in both the database with same coulmn names with each table how can i perform the following operation with the other tables.

please help me sort out this problem.

with

 

Lstock

as

(

select

 

state_code


View Complete Post


More Related Resource Links

TSQL: Passing array/list/set to stored procedure (MS SQL Server)

  
Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

Approach using Regex in TSQL text manipulation

  
Hi there I have this following data: 0297144600-4799               0297485500-5599 The 0297485500-5599 based on observation always on position 31 char from the left which this is an easy approach. But I would like to do is to anticipate just in case the data is like this below which means the position is no longer valid: 0297144600-4799      0297485500-5599      0297485600-5699 As you can see, I guess the first approach will the split by 1 blank space (" ") but due to number of space is unknown (varies) how do I take this approach then? Is there any method to find the space in between and shrink into 1 blank space (" "). I am exploring Regex not sure how to do this and what I can think of is that if the blank is space can somehow reduce from xxx to 1 and I can handle this the rest. Thanks dewacorp.alliances

Looking for TSQL statement that returns customers with greatest number of order in descending order

  
What would be an efficient TSQL SELECT statement that joins customer and order tables, counts the orders for each customer (group by) and returns a result for all customers with at least one order, and also in descending order from customers with the most orders to the least?

XQeury in TSQL

  
Why can i use it like this: SET @xml.modify(' delete if(/employees/employee/name[sql:variable("@index")][contains(.,@type)]) then (/employees/employee/name[sql:variable("@index")]/@type) else() ') but can not use it like this: SET @xml.modify(' insert attribute type {string("Normal")} as first into (/employees/employee/name)[sql:variable("@index")] ')

Tsql

  
this particular query takes the tototal execution time of 20 minutes and 52 seconds is there any posibilities to make this particular query faster? select   state_name,dist_name,tahsil_name,isnull(sum(CASE WHEN T_rabbit.Village_code like '0%' and T_rabbit.sb_code between 1401 and 1405 then rm end ),0)as rm,isnull(sum(CASE WHEN T_rabbit.Village_code like '0%' and T_rabbit.sb_code between 1401 and 1405 then rf end ),0)as rf,isnull(sum(CASE WHEN T_rabbit.Village_code like '0%' and T_rabbit.sb_code between 1401 and 1405 then rm+rf end ),0)as TotalRabbit,isnull(sum(CASE WHEN T_Dog.Village_code like '0%' and T_Dog.sb_code ='1300' then dm end ),0)as dm,isnull(sum(CASE WHEN T_Dog.Village_code like '0%' and T_Dog.sb_code ='1300' then df end ),0)as df,isnull(sum(CASE WHEN T_Dog.Village_code like '0%' and T_Dog.sb_code ='1300' then dm+df end ),0)as TotalDog,isnull(sum(CASE WHEN T_Elephant.Village_code like '0%' and T_Elephant.sb_code ='1500' then em end ),0)as em,isnull(sum(CASE WHEN T_Elephant.Village_code like '0%' and T_Elephant.sb_code ='1500' then ef end ),0)as ef,isnull(sum(CASE WHEN T_Elephant.Village_code like '0%' and T_Elephant.sb_code ='1500' then em+ef end ),0)as TotalElephant from state_master inner   join dist_Master ON Dist_Master.State_code=state_master.State_code INNER   JOIN Tahsil_Master ON Tahs

XQuery in TSQL

  
Why can i use it like this:   SET @xml.modify(' delete if(/employees/employee/name[sql:variable("@index")][contains(.,@type)]) then (/employees/employee/name[sql:variable("@index")]/@type) else() ')   but can not use it like this:   SET @xml.modify(' insert attribute type {string("Normal")} as first into (/employees/employee/name)[sql:variable("@index")] ')  

A tsql report with multiple counts and pivioted description attributes

  
Hello,   I have an assignment in which I am to prepare a report displayed in excel to return counts of various attributes in the database. However, the count is a bit challenging because it requires counting certain attributes in a pivot format illustrated below. The counts for each category by recruiter, rolled up by Division, within the specified time frame.   The tables that I would have to deal with are as follows:   User: Has the number of Accounts…user accounts with no Recruiter are unassigned Recruiter: Has the recruiter information LoginHistory: Has a record of all logins EForm: Has the various forms, for example: Release and Authorization, Provider Service Agreement EFormUserXref: You can filter it to return the Physican AppForms                                                                       Surgery                                      &n

Report Level Security in SSRS using Tsql

  
So i am Brand new in this area i have developed reports before and deployed them but never got an oppurtunity to work on security and who gets to see what.. Purpose of the view is thats what im thinking..   We have a reports database has 100 reports . What we are trying to do is have a faster way to manage report subscriptions. The tables i have is Analysts, SalesRep,SalesRepSubs and dbo.users.Dbo users are our web portal users where they can view reports. I want to create a table in which every report is viewed per security level. We have 6 layers of security layer 1 is lowers to layer 6 being highest. This is the relationship between tables Salesrep(SRID PK) Salesrep_sub (SRID_SUBID PK) Analysts : (AnalystIDPK) Users: The Table has all Users who are Salesrep,Sales Repsub and Analysts)(PKID PK) and we have cols SRID, SRID_SUBID & AnalystID) in this table. use Pricing go Alter View dbo.User_Access   AS select S.SR_name,u.SRID,sr.SRsub_name,u.SRID_SubID,U.Userid,u.username,u.Email, u.SecurityLevel,a.AnalystID from dbo.Users u join tbl_Analyst a on a.AnalystID=u.UserID join tbl_SalesRep s on s.SRID=u.SRID Join tbl_SalesRep_Sub sr on sr.SRID_SubID=u.SRID_SubID My question is what do i do next? Add this view to Report server? or how can i make it functionl?????????FM

Report Level Security in SSRS using Tsql

  
So i am Brand new in this area i have developed reports before and deployed them but never got an oppurtunity to work on security and who gets to see what.. Purpose of the view is thats what im thinking..   We have a reports database has 100 reports . What we are trying to do is have a faster way to manage report subscriptions. The tables i have is Analysts, SalesRep,SalesRepSubs and dbo.users.Dbo users are our web portal users where they can view reports. I want to create a table in which every report is viewed per security level. We have 6 layers of security layer 1 is lowers to layer 6 being highest. This is the relationship between tables Salesrep(SRID PK) Salesrep_sub (SRID_SUBID PK) Analysts : (AnalystIDPK) Users: The Table has all Users who are Salesrep,Sales Repsub and Analysts)(PKID PK) and we have cols SRID, SRID_SUBID & AnalystID) in this table. use Pricing go Alter View dbo.User_Access   AS select S.SR_name,u.SRID,sr.SRsub_name,u.SRID_SubID,U.Userid,u.username,u.Email, u.SecurityLevel,a.AnalystID from dbo.Users u join tbl_Analyst a on a.AnalystID=u.UserID join tbl_SalesRep s on s.SRID=u.SRID Join tbl_SalesRep_Sub sr on sr.SRID_SubID=u.SRID_SubID My question is what do i do next? Add this view to Report server? or how can i make it functionl?????????FM

TSQL Stored Procedure

  
Hi, I have a rather odd Stored Procedure I need to write, I was wondering if anyone could help me?   I have two tables which I need to basically match up and get information from.   Table: FormField - FormFieldID (PK) - Name   Table: FormFieldValues - FormFieldValue - FormFieldID - Value (Value of the FormField) - FormKey (This is to keep the form values together, for example Bob's DOB is 01/01/1975)   Example View   FormField.Name          FormFieldValue                   FormKey First_Name                 Bob                                    1 First_Name                 Jim                                     2 DOB                 &nbs

tsql

  
how would i convert a given number into an alphabet. suppose if user gives a number '1224' then the output should be 'one thousand two hundred and twenty four'. if anyone please reply.

how to concatenate special characters in TSQL ?

  
hi how can i insert special characters like ' to my select output ?? for example, i tried to get the following output but it just doesnt work.. i am using SQLS2K btw, SELECT ' Hi. this is called the 'modern age' ' note that i need those to ' s with the words modern age :)   thanks in advance :)

Restore using TSQL, how to determine the files to move/overwrite

  

There might be an easier way to perform it, if so, please advise.  I am looking for a scriptable solution (preferably TSQL).

I want to code a tsql procedure to restore a database from a backup file (.bak).  The procedure must allow to restore a backup from a database to a different database.

When performing it through the SSMS interface, I see all the files in the .bak file.  From there, I can associate the files in the backup to the appropriate files in my destination database.  when generating TSQL, this translates to MOVE statements for the files.

Can I translate the whole process in TSQL?  In order to know how to map my files, I must first gather the information on which files are present in my bak file.  Can one read the definition of the files contained in a backup file before performing the restore.  Preferably using TSQL?


tsql

  
i have got an excel sheet containing some records and i want the intersection between those records and the records in my database.

current logged in user and password using a TSQL

  

Dear frnds,

 

 

Is there is any way to get current logged in user and password using a TSQL?  (SQL MODE)

 

 

 

Regards,

Sajid P K


TSQL to get Week of Day

  

Hi all,

I need to write a automatic query. Please see below for example.

Date: 4/4/2010 (Monday), 4/5/2010 (Tuesday), 4/6/2010 (Wednesday)

So from every Monday - Sunday (End of the week) the result = Week of October 4th 2010

Date       Day          Wanted Result

4/4/2010 Mon          Week of Oct 4 2010

4/5/2010 Tue          Week of Oct 4 2010

4/6/2010 Wed         Week of Oct 4 2010

4/7/2010 Thur         Week of Oct 4 2010

4/8/2010 Fri            Week of Oct 4 2010

4/9/2010 Sat           Week of Oct 4 2010

4/10/2010 Sun         Week of Oct 4 2010

4/11/2010 Mon         Week of Oct 11 2010

 

Thanks loadzzz in advancee


How to replace first occurrence of word in TSQL?

  

Dear Frnds,

Could please tell me best way to replace first occurrence of word in Tsql ?

Eg: 

SET @SQL = 'select 1 as X, 1 as  Y,  3  as  z  from

  ( 

      Select * from Table1

  ) 

I need to replace first   “select” to another word.


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend