Tuesday, October 8, 2013

[T-SQL] Filter Countries, States and Cities which in same row in one table.

[T-SQL] Filter Countries, States and Cities which in same row in one table.

Filter Countries, States and Cities which in same row in one table.

Posted: 07 Oct 2013 08:04 PM PDT

I have table which contains the data of the Users with different Country, State and city, in which State and city may be null or may not be null. And all these data are in one table.Table is likeUserId Not Null,Username Not Null,Fullname Not Null,CountryId Not Null,StateId Null,CityId NullI need data based on 1. CountryId2. CountryId and StateId,3. CountryId, StateId and CityIdPlease suggest me the possible queries.

LOCK/Wait on Begin Tran and Commit

Posted: 08 Oct 2013 12:54 AM PDT

Hi,We have a script with insert statements in between BEGIN TRANSACTION and COMMIT.The data from those insert statements should populate few tables. When two of us run the script at the same time. Some of my data and some of other persons data are getting populated. I would like to know the best option to get rid of this issue. Should we use NOLOCK or Wait or any other approach that is the best.Can somebody suggest?Thanks

Out of memory error

Posted: 20 Sep 2013 01:01 AM PDT

I have a procedure, in which returns many results in the grid(over 1 million) and when I run it I get the out of memory error. I tried running the script with the "discard results after execution" box checked and not only was I able to process many more records, but the execution time was cut down from about an hour to 5 minutes. The checkbox I was referring to is located under Tools > Options > Query Results > SQL Server > Results to Grid > Discard results after execution. Is there an equivalent T-SQL command that can perform the same configuration? Having this box checked still processes the script, but doesn't produce any results in the grid. The script now runs so much faster and memory isn't getting used up by the massive amounts of rows that were previously being returned. Since this is will be run in production, i would like to include this option in the procedure/ script instead of selecting it from GUI.

SELECT for UPDATE....

Posted: 08 Oct 2013 12:23 AM PDT

Hi,We have several tables, sales, orders, ..., that when they're updated insert record in a pool so that the customer balance is updated through a scheduled procedure...The scheduled procedure currently has a cursor that goes through all the records in the pool, makes a big update query and then deletes the record from the pool (no risk of doing the same record twice since SQL Agent doesn't start a new schedule until the old one has finished...).Also a scheduler log is inserted in a table with the number of records processed..[code="sql"]SET @cnt = 0DECLARE cur CURSOR FOR SELECT Id, Customer FROM UpdatePoolOPEN curFETCH NEXT FROM cur INTO @id, @customerWHILE @@FETCH_STATUS = 0BEGIN UPDATE ...... WHERE customerid = @customer SET @cnt = @cnt + 1 DELETE FROM UpdatePool WHERE id = @id FETCH NEXT FROM cur INTO @id, @customerENDCLOSE CURDEALLOCATE curINSERT INTO PoolLog (Date, NumProcs) VALUES (GETDATE(), @cnt)[/code]Is there any way of doing this with an UPDATE .. SET ... WHERE customerid IN (SELECT customerId FROM UpdatePool) and then delete the processed records?! I could try to put the INSERTED.customerId from the UPDATE in a table variable and then delete from UpdatePool where customerid IN ... but I can delete more recent process requests (while I'm processing a 2nd customerid a request for the 1st can be inserted again...).Thanks,Pedro

tsql script check existing data on creation

Posted: 07 Oct 2013 08:55 AM PDT

I am missing something here. I need to create a script to do this. To disable I do the following command[code="sql"]EXECUTE sp_msforeachtable @command1="Print 'Test'", @Command2 ="ALTER TABLE Foo.tblFoo NOCHECK CONSTRAINT all"[/code]To enable the FK I do this[code="sql"]EXECUTE sp_msforeachtable @command1="Print 'Test'", @Command2 ="ALTER TABLE Foo.tblFoo CHECK CONSTRAINT all"[/code]The check existing data on creation in the table is still set to "No". I can't seem to google a solution. Any of you potentially know if this can be done without using the ui?

import string with array into table

Posted: 07 Oct 2013 02:31 AM PDT

Hello again,I am running into a problem importing a string containing an array into a table.My string looks like '[{mark,peters,mr}{jane,fonda,mrs}{john,doo,mr}{james,bond,mr}]'I want to import this into a temp table that looks like:ID, Fname, Lname, Sex1, mark, peters, mr2, jane, fonda, mrs3, john,doo,mr4, james,bond,mrCan someone help me with this? I really have no clue how to solve this.Thanks a lot!Mike

lookup table for minutes of a day

Posted: 07 Oct 2013 09:01 AM PDT

Does anyone know if a website or have a query that generates a table with a row for each minute of the day starting 2 years ago and going 10 years into future?Need it to have values in one minute increments (1440 rows/minutes for each day):2013-10-07 04:322013-10-07 04:332013-10-07 04:342013-10-07 04:352013-10-07 04:362013-10-07 04:37Thanks!

Unpivot SQL Query

Posted: 07 Oct 2013 08:23 AM PDT

I have a table structure (tblmapping) like the following:TableName ColumnName ColumnValueProduct ProductID 1Product ProductID 2Product ProductName KeyboardProduct ProductName MouseI want to convert from column based data to row based data. I tried the following query, but syntax errors:[code="sql"]SELECT col,valueFROM(SELECT DISTINCT ColumnNameFROM tblMappingWHERE TableName = 'Product') pUNPIVOT(ColumnValue FOR ColumnName IN (SELECT DISTINCT ColumnName FROM tblMapping WHERE TableName = 'Product')) AS unpvt; [/code]Msg 156, Level 15, State 1, Line 7Incorrect syntax near the keyword 'SELECT'.Msg 102, Level 15, State 1, Line 9Incorrect syntax near ')'.Not sure what I'm doing wrong. Thanks for the help.

Order with unpivot

Posted: 07 Oct 2013 06:12 AM PDT

I have an unpivoted table that seems ordered the way I want but I am not sure if it is guarenteed to be that way.If I have a table:[code]Year TotalNew TotalLost NetGain2013 29544.48 -10832.00 18712.482012 35549.67 -20252.65 15297.022011 136816.89 -22860.54 113956.352010 45795.48 -54933.17 -9137.692009 61113.35 -19419.3 41694.05[/code]And I want to unpivot it:[code]SELECT ActivityYear,Activities, ActivityFROM( SELECT BoundOrLostYear AS ActivityYear , Totalnew , TotalLost , NetGain FROM @FinalTable2) pUNPIVOT(Activity FOR Activities IN (Totalnew, TotalLost, NetGain)) b[/code]This comes out perfect.[code]Year Activities Activity2013 Totalnew 29544.482013 TotalLost -108322013 NetGain 18712.482012 Totalnew 35549.672012 TotalLost -20252.652012 NetGain 15297.022011 Totalnew 136816.892011 TotalLost -22860.542011 NetGain 113956.352010 Totalnew 45795.482010 TotalLost -54933.172010 NetGain -9137.692009 Totalnew 61113.352009 TotalLost -19419.32009 NetGain 41694.05[/code]I need to make sure that years is ordered as it is and the 2nd column is also ordered as it is. If it isn't, I could use an order by on the year in descending order. But I couldn't do the same for the second column.How would I change the unpivot statement to fix this if it only just happens to go this way?Thanks,Tom

How to rename a column in all tables and views

Posted: 07 Oct 2013 05:35 AM PDT

HiI want to change the name of a column in all tables and views (rename Feild "Phone" To "TelePhone")Sp-ReName Change only one table can be done Plz Help Me

Need Help

Posted: 07 Oct 2013 02:12 AM PDT

Hi ,I am trying to delete files in a folder except the last created file in the folder.can you please help me with the logic.The files in the folder are sql server audit files with the extension .sqlauditI cannot choose the files based on date because if the auditing data is not present on todays date . the old files can be deleted.I am looking for a logic which should look at the files in the folder and delete all the files in that folder except the last created file in that folder.I am using SQL Server 2008 R2 Enterprise edition RTM 64 bit on windows server 2008 R2 Enterprise edition sp1 64 bit Thank You,
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog