[T-SQL] Adding a Table Lock on Purpose |
- Adding a Table Lock on Purpose
- How to make a Pivote table
- Deletes taking long time
- Custom order in SELECT
- USPS Addresses Load
- Exclude similar columns in a JOIN command
- Converting SQL DBMail XML to XLS
- XML issue converting table to XML
| Adding a Table Lock on Purpose Posted: 01 Mar 2013 06:14 AM PST Hello EveryoneI have a rather odd situation. I want to add a lock to a table on purpose. I need to update and then select from that table before anything else can happen. This is in the middle of a very large stored procedure, and I cannot log that table during the entire transaction of the entire stored procedure. I can Lock the table for the very minimum amount of time.@NumberOfListToInsert is set by @@ROWCOUNT after an Insert into a table. I then re-key that table starting with the number from the Keys table[code="sql"]UPDATE dbo.KeysSET MaxCount = (MaxCount + @NumberOfListToInsert) + 5WHERE TableName = 'Diagnosis';SET @MaxKeyNumber = (SELECT MaxCount AS MaxKeyCount FROM dbo.Keys WHERE TableName = 'Diagnosis')[/code] I need for both of these to start and complete before any other query can access the "Keys" table.Can I simply wrap this in a Being Tran and Commit Tran? Or should I use TABLOCKX ?I am not sure how to use TABLOCKX, so if you can give an example that would be very helpful.Thanks in advance for your help, suggestions and code samples.Andrew SQL DBA |
| Posted: 01 Mar 2013 12:55 PM PST Hi,I have a table containing medical test results (as integer with 3 values: 0,1,2) like this :[code="plain"][PATIENT_ID],[Name],...,[T1],[T2],[T3]...... (the T? are test names (in abbreviations) dynamically added to table) ------------------------------------------------01, John,.....,1,2,0,.....02, Jake,.....,0,2,0,.....03, Joe,.....,2,2,2,.....04, Jane,.....,1,2,1,...............[/code]Another table includes information about each test :[code="plain"].....,[TEST_DESC],[ABBR],......-----------------------------------.....,Test name 1,T1,...............,Test name 2,T2,...............,Test name 3,T3,....................................[/code]Each time one test introduces to system, one row is added to second table and one column to first table with a default value.Now, I need to make a group report for tests. To do so, first I need to change it to this format :[code="plain"][PATIENT_ID],[Name],[TEST_DESC],[RESULT]-----------------------------------------------------01,John,Test name 1,101,John,Test name 2,201,John,Test name 3,0..........................02, Jake,Test name 1,002, Jake,Test name 2,202, Jake,Test name 3,0...........................[/code]Any suggestion will be appreciated. |
| Posted: 01 Mar 2013 04:06 AM PST I have a situation where I created I ran a query that generated about 32,000 individual deletes that I am sending to my client to run on their database.Delete x from table where id = 1234Delete x from table where id = 1235Delete x from table where id = 1237On this table, it takes about 3 hours to run.It has the requesite clustered PK and 2 foreign keys.I have another table same type 2 foreign keys and it takes 40 seconds to delete 90,000 records.Why would the table that I am deleting a 3rd as many records take a ton of time longer?I thought about disabling the FKs but not sure what happens when I re-enable them. Does it recreate the FKs pointers?Thanks,Tom |
| Posted: 01 Mar 2013 06:26 AM PST Hi,How could we change the order in SELECT command based on a custom semicolon delimited phrase.We have :[Letters]----------abcdeAnd based on this phrase 'd;c;b;a;e' we want to have :[Letters]----------dcbaeThanks in advance for helps |
| Posted: 01 Mar 2013 04:14 AM PST hi guys,Does anybody has loaded the USPS city/state addresses in a text file format to a table , my manager gave me a CD to load a flat file with no consistent delimiters and no headers in a table,does anybody have loaded USPS addresses in the past , if so can you guide me to do thisany help is much appreciated. |
| Exclude similar columns in a JOIN command Posted: 01 Mar 2013 03:49 AM PST Hi,I have three tables and the structures are :Table1 : [PATIENT_ID],[Name],[Age],[City],[TEST1],[TEST2]...... (the AB? are test names dynamically added to table)Table2 : [PATIENT_ID],[Name],[Age],[DR1],[DR2],...... (the DR? are drug names dynamically added to table)Table3 : [Hospital],[City],[DATE],[ROOM],[PATIENT_ID] I need to 'SELECT JOIN' them to create this table :[Hospital],[City],[DATE],[ROOM],[PATIENT_ID],[Name],[Age],[TEST1],[TEST2]......, [DR1],[DR2],......When I simply join them, I will have repeated columns such as [Name], [City]. Here, I simplified the structure but in reality we have many more such informative columns. In the other hand, part of tables are dynamically changing. So, it would be really difficult to define column names individually.I appreciate for any guide to tell me if there is a way to exclude those similar columns and they show up only once.Thanks in advance |
| Converting SQL DBMail XML to XLS Posted: 01 Mar 2013 05:47 AM PST Hi, Does anyone know of a good way to convert the XML table I'm generating to XLS prior to send. I'm doing something like this...http://www.mssqltips.com/sqlservertip/2347/send-email-in-a-tabular-format-using-sql-server-database-mail/Regards, Greg. |
| XML issue converting table to XML Posted: 01 Mar 2013 02:46 AM PST Hi,Thanks for your help in advance. I am trying to get the following data [code="sql"]CREATE TABLE [dbo].[TABLE_XML1]( [ID] [int] IDENTITY(1,1) NOT NULL, [TOP_LEVEL_ID] [int] NULL, [TOP_LEVEL_ITEM] [nvarchar](50) NULL, [SECOND_LEVEL_ID] [int] NULL, [SECOND_LEVEL_ITEM] [nvarchar](50) NULL, [THIRD_LEVEL_ID] [int] NULL, [THIRD_LEVEL_ITEM] [nvarchar](50) NULL, [FOURTH_LEVEL_ID] [int] NULL, [FOURTH_LEVEL_ITEM] [nvarchar](50) NULL, CONSTRAINT [PK_TABLE_XML1] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[TABLE_XML1] ON GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (1, 294, N'TOP LEVEL TYPE A', 1244, N'SECOND LEVEL ITEM A', 1245, N'THIRD LEVEL ITEM A', 2222, N'FOURTH LEVEL ITEM A')GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (2, 294, N'TOP LEVEL TYPE A', 1252, N'SECOND LEVEL ITEM B', 1245, N'THIRD LEVEL ITEM A', 2223, N'FOURTH LEVEL ITEM B')GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (3, 294, N'TOP LEVEL TYPE A', 1254, N'SECOND LEVEL ITEM C', 1255, N'THIRD LEVEL ITEM B', 2231, N'FOURTH LEVEL ITEM C')GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (4, 294, N'TOP LEVEL TYPE A', 1298, N'SECOND LEVEL ITEM D', 1255, N'THIRD LEVEL ITEM B', 2313, N'FOURTH LEVEL ITEM D')GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (5, 294, N'TOP LEVEL TYPE A', 1302, N'SECOND LEVEL ITEM E', 1303, N'THIRD LEVEL ITEM C', 2318, N'FOURTH LEVEL ITEM E')GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (6, 294, N'TOP LEVEL TYPE A', 1305, N'SECOND LEVEL ITEM F', 1303, N'THIRD LEVEL ITEM C', 2192, N'FOURTH LEVEL ITEM F')GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (7, 656, N'TOP LEVEL TYPE B', 657, N'SECOND LEVEL ITEM G', 27, N'THIRD LEVEL ITEM A', NULL, NULL)GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (8, 656, N'TOP LEVEL TYPE B', 657, N'SECOND LEVEL ITEM G', 638, N'THIRD LEVEL ITEM B', NULL, NULL)GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (9, 656, N'TOP LEVEL TYPE B', 657, N'SECOND LEVEL ITEM G', 939, N'THIRD LEVEL ITEM C', NULL, NULL)GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (10, 656, N'TOP LEVEL TYPE B', 657, N'SECOND LEVEL ITEM G', 939, N'THIRD LEVEL ITEM C', NULL, NULL)GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (11, 656, N'TOP LEVEL TYPE B', 657, N'SECOND LEVEL ITEM G', 939, N'THIRD LEVEL ITEM C', NULL, NULL)GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (12, 656, N'TOP LEVEL TYPE B', 1165, N'SECOND LEVEL ITEM H', 940, N'THIRD LEVEL ITEM D', NULL, NULL)GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (13, 656, N'TOP LEVEL TYPE B', 1165, N'SECOND LEVEL ITEM H', 940, N'THIRD LEVEL ITEM D', NULL, NULL)GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (14, 1234, N'TOP LEVEL TYPE C', 2206, N'SECOND LEVEL ITEM I', NULL, NULL, NULL, NULL)GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (15, 1234, N'TOP LEVEL TYPE C', 2207, N'SECOND LEVEL ITEM J', NULL, NULL, NULL, NULL)GOINSERT [dbo].[TABLE_XML1] ([ID], [TOP_LEVEL_ID], [TOP_LEVEL_ITEM], [SECOND_LEVEL_ID], [SECOND_LEVEL_ITEM], [THIRD_LEVEL_ID], [THIRD_LEVEL_ITEM], [FOURTH_LEVEL_ID], [FOURTH_LEVEL_ITEM]) VALUES (16, 1234, N'TOP LEVEL TYPE C', 2208, N'SECOND LEVEL ITEM K', NULL, NULL, NULL, NULL)GOSET IDENTITY_INSERT [dbo].[TABLE_XML1] OFFGO[/code]Into XMLSuch that it is structured like[code="xml"]<TOP_LEVEL_ITEM= "TOP LEVEL TYPE A" TOP_LEVEL_ID="294"> <SECOND_LEVEL_ITEM= "SECOND LEVEL ITEM A" SECOND_LEVEL_ID="1244"> <THIRD_LEVEL_ITEM="THIRD LEVEL ITEM A" THIRD_LEVEL_ID="1245"> <FOURTH_LEVEL_ITEM="FOURTH LEVEL ITEM A" FOURTH_LEVEL_ID="2222"> <FOURTH_LEVEL_ITEM="FOURTH LEVEL ITEM b" FOURTH_LEVEL_ID="2223"> </THIRD_LEVEL_ITEM> <THIRD_LEVEL_ITEM="THIRD LEVEL ITEM B" THIRD_LEVEL_ID="1255"> <FOURTH_LEVEL_ITEM="FOURTH LEVEL ITEM C" FOURTH_LEVEL_ID="2231"> <FOURTH_LEVEL_ITEM="FOURTH LEVEL ITEM D" FOURTH_LEVEL_ID="2313"> </THIRD_LEVEL_ITEM> </SECOND_LEVEL_ITEM></TOP_LEVEL_ITEM>[/code]Any pointers as to how I can achieve this are gratefully appreciated.Cheers,Oliver |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment