In SQL server, I am trying to insert values from one table to another by using the below query : delete from tblTable1 insert into tblTable1 select * from tblTable1_Link I am getting the following error : Column name or number of supplied values does not match table definition. I am sure that both table having same structure,colum...
In SQL server, I am trying to insert values from one table to another by using the below query : insert into tblTable1 ( [Week], 20001, 20002, 20003, 20004, 20006, 20005, W/c ) select * from tblTable1_link ( [Week], 20001, 20002, 20003, 20004, 20006, 20005, W/c ) I am getting the following error : Serv...
I have a table mapping postal code ranges to contacts. Postal codes are saved in multiple formats for different countries. Table postcode-range: FROM-CODE TO-CODE CONTACT-ID COUNTRY-CODE 12000 12999 18 fr BT000000 BT9ZZZZZ 34 uk ... To find a contact in a specific range , e.g. range star...
I want to present a schema to my BA team. Select TABLE_NAME, col.DATA_TYPE from INFORMATION_SCHEMA.COLUMNS col order by col.TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA, col.ORDINAL_POSITION I want to present 3 rows of sample data as well but I wanted to pivot it out so they get an output that has the following 3 columns: Table_Name Dat...
Hi, I need to fo the following: Check if Public or guest is granted any permission on an object (database role and server role) Check if any user is granted permissions on an object rather than roles. Check if a user has "with grant" previliges on an object Check who has access to extended strored procedures (which I get from select n...
Hi, I need to get 5 random records from a table plus a further record based on data from the users preferences as stored in a second table. Here are the two statements i have created so far: Gets favourite charity based on key set in TBL_MEMBERS: SELECT DISTINCT TBL_CHARITIES.* FROM TBL_CHARITIES JOIN TBL_MEMBERS ON TBL_CHARITIES.cha...
Hi, Thanks to help from a lot of you guys ive been given the following code which works great. However ive realised ive missed an important bit of info out of the question and so have reposted here (with updated code) to clarify. The following code gets 5 random records from a table plus a further single record based on the users favou...
I'm having a general discussion with one of my developers about philosophy of structuring TSQL stored procedures. Let's make a couple assumptions (to avoid responses along the line of "don't put business logic in SPs", etc): Business Logic can be in SPs There is no standard scripted layer of CRUD SPs Complex procedures are executed ...
I'm trying to solve the below problem. I feel like it is possible, but I can't seem to get it. Here's the scenario: Table 1 (Assets) 1 Asset-A 2 Asset-B 3 Asset-C 4 Asset-D Table 2 (Attributes) 1 Asset-A Red 2 Asset-A Hard 3 Asset-B Red 4 Asset-B Hard 5 Asset-B Heavy 6 Asset-C Blue 7 Asset-C Hard If I am looking for something havin...
Terribly worded question above (trying to be short). Lets try that again: In Microsoft SQL Server Management Studio, when you are editing an SP and execute the sp in the same window, errors show up at the bottom of the screen you can double click to highlight the code causing the error. However, this never lines up, usually being anywh...
Can anyone out there remember in which version of SQL server UDFs were introduced? EDIT: Thanks guys, looks like it was SQL 2000, I had thought it earlier. Thinking about it now I do remember those days of doing nasty workarounds and just waiting for the next version... http://msdn.microsoft.com/en-us/library/aa214363(SQL.80).aspx ...
I would like to know how to get no. of days using getdate() & existing dates in other table, where that table contains multiple records, ie i would like to know the jobs posted 5 days back or 10 days back or 15 days back. I have written the query like declare @q1 datetime; select cdate from jobposting where cdate like (select cdate) se...
I get lists in Outlook, Word or Excel of hundreds of strings like so: 7864750A 7888801BZ 5189748C 48982572E 6936001F 7096235FG 3218833H etc. Currently I run a stored procedure on each of these, in one-by-one fashion. How could I programmatically add an apostrophe before and after a string and add a comma inbetween, like so: ('7864750A...
I know that sys schema has execute access on all extended stored procedures. If my sys admin logins are having schema - dbo, what is "sys" schema used for? Or can I deduce that sys admins have execute permissions on all extended stored procedures? How can I prove it with tsql? ...
Is there a magic TSQL command which can show me the enabled or disabled SQL server protocols? I thought it is select * from sys.endpoints but the state_desc field indicates "started" even for the protocols (which i know from "configuration manager") which is disabled. any comments? version: 2005 ...
Hi, I want to Copy a Row in SQL which has child records in 2 other tables. i want to duplicate them all if the master record duplicates. for example if i say i want to copy master record with id 15 in to a new record. i want all its child records to be duplicated with the new master record id as well. can do it through TSQL using loops...
I am managing a data warehouse. I have several dimension tables most of which are type-2 and some are type-1. I was able to figure out how to use MERGE to maintain my type-1 dimension tables. I am stumped on how to do the type-2 dimension tables. How do I do both, the update to the EndDate and an insert using the MERGE Command? ...
Is it possible to express 1 or 0 as a bit when used as a field value in a select statement? e.g. In this case statement (which is part of a select statement) ICourseBased is of type int. case when FC.CourseId is not null then 1 else 0 end as IsCoursedBased To get it to be a bit type I have to cast both values. case when FC.Course...
I have a trigger that supposed to update log out time[generate random log out time within an hour of log in time, seconds have to differ too], how do I achieve that functionality, please help here is my trigger so far: USE TestDB GO CREATE TRIGGER AfterInsertTG ON dbo.usage_reports_Dummy2 AFTER INSERT AS DECLARE @pk_id as int, @mem...
Does anyone know of a real good SSMS Add-In that beautifies T/SQL, isn't too expensive and also does things around best practices for T/SQL formatting? I'm well aware of Red Gate's tool, but ~$300 is quite a killer amount. http://www.red-gate.com/products/SQL_Refactor/index.htm I've seen this Add-In, but it's purely about indentation....