I have imported a txtfile to access and have some problem making the data appear in my wanted way. I want to shift the cells in the first row one step to the left, i.e to overwrite the zeros with the left value. Then I want to assign the first row values as field names.
Current data
Field1 Field2 Field3 Field4 Field5
0 Name Major Sector Hi
karl
per
anna
Wanted outcome
Name Major Sector Hi
karl
per
anna
Normally it is recommended to leave an empty partition on both the front and back ends of a table to avoid data movement when merging/splitting. But I have some questions based on my scenario, which is a table partitioned by a load date, so all records in a partition contain the same date, not a range of dates.
If I use range left, once I switch out the first partition it would become empty, so would there be data movement when I merge it into the next partition? The real issue though is that we will not just be removing the first partition, but "random" partitions throughout the table. Will this work?
If I use range right, when I split the last partition to create a new one it doesn't seem there would be any data movement there either. Am I missing something?
Basically I'm wondering if I should use range left or right. Most recommend using right, but then the boundary value is not the value in the partition. This could potentially result in someone deleting the wrong data if they are not careful. So is there any reason not to use left in my scenario?
I did a test of removing a partition in the middle and it worked just fine; this was using range right. I have about 6 million rows per partition. I also tested splitting at the end and it worked fine. I'll rebuild it with range left and test.
SELECT LEFT(InstanceName,CHARINDEX(' ',InstanceName)-1)
FROM ServerDB
Returns the following error.
Invalid length parameter passed to the SUBSTRING function.
SAMPLE
keyboard , keyboard keyboard
piano , keyboard keyboard
RESULT
keyboard, keyboard keyboard
piano, keyboard keyboard
I wrote the below script to move the SP from master to another db, but does not seem like working.
declare @sql3 nvarchar(max), @name3 sysname = 'NEW_DB'
SET @sql3 = (Select definition from sys.sql_modules where object_id = object_id('usp_Build_DB'));
set @sql3 = N'execute ' + QUOTENAME(@name3) + N'.dbo.sp_executesql N''' + @sql3 + ''''
We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.
So rather then the following exemplary fragment
select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.idthis now looks like
select <many items>
from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id
Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.
I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.
I am trying to erase some erroneous bad data in my table. The description column has a lot of </div>oqwiroiweuru</a> weird data attached to it, i want to keep the data to the left of where the </div> erroneous data begins
update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid = 1
that totally works.
update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid >= 2
gives me a Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated error.
Is there a way to leave the graphical 'Include Execution Plan' on by default in SSMS? I don't know how many times I run a long-running query, say to myself, "wow, that took a while; I wonder what the execution plan looks like?" only to realize that I left it turned off. Now I have to turn it on, and wait for the query to run again. I'm guessing there's a setting in the options somewhere to always leave it on, but I'm not sure where
View 2 Replies View RelatedI just notice that my MASTER database has some user tables and user SP ..and I am thinking to move them to 1 new user database but I am worried it will break something ..
What should I do ?
Moreover I wonder why Transaction log of MASTER can be full ( The recovery model is simple ) It should be fine , isn’t it?
I want to to move all database log files from drive E to F .
There are more than 10 databases so I don’t wanna move them 1 by 1 .
At the moment I use detach – attach method .
-Detach db
-Move log file
-Attach db
How do I do this massively in one go ?
I am trying to create a job using power-shell script to move the backup files from one folder to another. I am using Ola Hallengren script for backups. Ola hallengren created a common backup folder with sub-folders for databases and even more sub folders for Full and Log backups. My goal is to move full backups, which are older than a month and save them in a different drive along with the same folder structure. I was able to move the first set of backups without any problem, but I can't move anymore files and keep getting this error even when I try to overwrite the previous file with the force statement:
Move-Item : Cannot create a file when that file already exists.
At line:5 char:9
+ Move-Item $i.FullName C:Test -force
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (C:BackupVALIDATIONgcommon:DirectoryInfo) [Move-Item], I
+ FullyQualifiedErrorId : MoveDirectoryItemIOError,Microsoft.PowerShell.Commands.MoveItemCommand
Here's the script that I used to move the first set of files:
foreach ($i in Get-ChildItem C:BackupVALIDATION)
{
if ($i.CreationTime -lt ($(Get-Date).AddMonths(-1)))
{
Move-Item $i.FullName C:Test
}
}
I have two tables.
Emp:
id | Deptno | sdate | edate | locid
1 | 10 |2014-09-19 |2009-09-25 | 100
1 | 10 |2014-11-06 |2009-11-06 | 100
1 | 10 |2014-12-11 |2014-12-14 | 100
1 | 10 |2009-07-01 |2009-07-20 | 100
2 | 20 |2007-07-11 |2007-07-17 | 104
Empl1:
Id | Deptno | edate | refid | refid1
1 | 10 | 2014-11-06 | 201 | 204
1 | 10 | 2014-12-13 | 204 | 202
1 | 10 | 2014-12-12 | 207 | 124
1 | 10 | 2003-05-04 | 105 | 103
2 | 20 | 2007-07-14 | 102 | 106
I am trying to combine these two tables into the output below:
Id | Deptno | locid | current_refid | current_refid1 | last_refid | last_refid1
1 | 10 | 101 | 201 | 204 | 204 |202
2 | 20 | 104 | 102 | 106 | 102 |106
The tables share the common columns id and deptno. To get the above result, the emp1.edate must be between emp.sdate and emp.edate.
If that condition is met, we need to retrieve the refid and refid1 values corresponding to the lowest edate as last_refid and last_refid1. Then we need the refid and refid1 values corresponding to the emp1.edate between emp.stdate and emp.edate to be retrieved as the current_refid and current_ refid1.
select a.id,a.deptno,a.locid,b.refid,b.refid1
from
emp b
[code]...
I am new in SQL and i need do a query where I need sum values from 2 tables, when i do it the Sum values are not correct. this is my query
SELECT D.Line AS Line, D.ProductionLine AS ProductionLine, D.Shift AS Shift, SUM(CAST(D.DownTime AS INT)) AS DownTime,
R.Category, SUM(Cast(R.Downtime AS INT)) AS AssignedDowntime,
CONVERT(VARCHAR(10), D.DatePacked,101) AS DatePacked
FROM Production.DownTimeReason R
left JOIN Production.DownTimeHistory D
[Code] .....
I have a table Tbl1 which has 7 columns.This table will be my base table.By using our current application version ,i'll be creating record for Client1. Col1 will have value that application will generate(id).Then i'll be creating Tbl2 with same columns.Then i'll be creating same record for Client1 again ,using our new application version .Col1 will have different (id)value.I would like to compare the rest of the columns if there is any discrepancy caused by new version(columns Col2 -Col7).If there are same ,don't show me anything.
View 9 Replies View RelatedI have a table of raw data with supplier names, and i need to join it to our supplier database and pull the supplier numbers.
The issue is that the raw data does not match our database entries for these suppliers; sometimes there are extra periods, commas, or abbreviations (i.e. FedEx, FederalExpress, FedEx, inc.) etc. I'm trying to create a query that will search for entries that are similar.
I tried setting a variable to be equal to the raw data field, and then using a LIKE '%@Variable%' to try and return anything that would contain it, but it didnt return any rows.
Why can i get the numeric values from this string?
{_cpn}=1743; {_cpnll}=4511
Result: 1743, 4511
Position and len of the value can be different...
I want to concatenate the column_names with the respective values.
Create table #test1 (Id int,Name varchar(10),Country varchar(10))
insert into #test1 values ( 1,'JOHN','USA'),
(2,'SAM','CANADA'),
(3,'HO','CHINA'),
(4,'RAM','INDIA')
select * from #test1
I have temp table with columns (Id,Name, Country). I want to concatenate column_name with their respective values; means i want column_header with every value for the column and then i want to concatenate multiple columns. I am looking for something like below:
ID values
1,NAME-john-COUNTRY-USA
2,NAME-SAM-COUNTRY-CANADA
3,NAME-HO-COUNTRY-CHINA
4,NAME-RAM-COUNTRY-INDIA
Note: This is just a sample .i am looking something dynamic because i have around 50 tables.i can do with query below but since i don't have static columns and table i am looking for something dynamic
select ID, (a+'-'+B) as Value from (
select ID,'NAME'+'-'+NAME as a,'Country'+'-'+Country as b from #test1
I have to increment sequencial values for the following:
Current Data
Col1 Col2 Col3
12.345.678 0001 32
13.456.789 0002 43
Updated Data
Col1 Col2 Col3
12.345.678 0001 32
12.345.678 0002 32
12.345.678 0003 32
13.456.789 0002 43
13.456.789 0003 43
13.456.789 0004 43
What I need is: Increment up to 3 times the values in Col2. Need to identify the first number in Col2 and increase the number up to 3.
I've a table that has salescode(124!080) and salesamount(125.65!19.25) and I need to split the columns. Salesman(124) has commission(125.65). Here is the DDL:
USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
, InvNoVARCHAR(10) NOT NULL
, SalesCode NCHAR(80) NOT NULL
, Amount NCHAR(80) NOT NULL
[code]....
I need to split the amount equally into 12 months from Jan 2015 through Dec 2015.There is no date column in the table and the total amount has to be splitted equally.Guess I can't use Pivot here because the date column is not there ...How can I achieve this ?
CREATE TABLE #tbl_data (
Region Varchar(25),
Amount FLOAT,
[code]...
This is the database structure I setup:
--Main Table
CREATE TABLE [dbo].[tbl_RF_Items](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[ProgramID] [tinyint] NOT NULL,
[StatusID] [tinyint] NOT NULL,
[Item] [nvarchar](256) NOT NULL,
)
--Mapping Table
CREATE TABLE [dbo].[tbl_RF_Tags_Map_Items](
[TagID] [int] NOT NULL,
[ItemID] [int] NOT NULL,
[DateModified] [smalldatetime] NULL,
CONSTRAINT [PK_tbl_RF_Tags_Item_Map] PRIMARY KEY CLUSTERED
[Code] ....
This a result set of the Items Map table so far:
TagIDItemID
1284838
1291475
1291480
8284838
8291475
8291480
10284838
10291480
62291475
Each item will have 3 tags. I am having trouble on how to filter the data. For example if i chose TagID 1, 8, and 62, the result set should return only one result. If I do an IN clause, it acts like an OR and I need something to act like an AND.It seems like the only option is to do a dynamic where clause, but there are thousands of items and that might hinder performance of the database. Is there any other option?
Which values are best prepared value for given below memory objects
Memory
Parameter
Total memory=
SQL Cache Memory=
Lock Memory=
Optimizer Memory=
Connection Memory=
Granted WorkSpace Memory=
Memory Grants Pending=
Memory Grants Success=
Cache Details:
Cache Hit Ratio=
Cache Used/Min=
Cache Count=
Cache Pages=
Scheduled Jobs:
Job Status=
Run date & time=
Job Time=
Retries Attempted=
Need to know the above performance counters prepared values.
I have results that are XML data and I am trying to figure out how to parse the data to select certain values from the xml.
example
<InformationRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" teamid="TEAM003341507" playerid="PL341508" gameid="G000000852" playertype="Starter" FolderName="Test" CurrentYear="2015" Ultimateid="P00000688505" xmlns="http://schemas.sports.com/Messages/Stats" />
I would like to write a statement that just pulls the game id G000000852. So just the id right of gameid=.
Not really sure where to start. Table is GAME, and column is XMLDATA.
We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....
We will be having date column values as 201401,201402,.....
How can we implement in sql sever performance should be good.
I am stuck on a query where I need to display all the month year values even if the corresponding count_booking values are NULL. The requirement is to display the 13 month year period from current date.
For e.g. if the date exists in the current month then starting from May 15 go all the way back to Apr 14.
My current query works in terms of displaying all the 13 months if the count_booking values exist for all the months. However, if some months are missing the values then those months don't show up.how to display all the months even if the values are NULL ? Query below:
SELECT COUNT(m.BOOKING_ID) AS count_booking, LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2)
AS month_name
FROM MG_BOOKING AS m
WHERE (m.CREATE_DT >= DATEADD(m, - 13, GETDATE()))
[code]...
For finding size values of temp database:
select * from sys.master_files -
size column value here is 1024 for .mdf,size here for .ldf is 64
select * from tempdb.sys.database_files -
size column value here is 3576 for .mdf,size here for .ldf is 224
Why is there a difference and not the same. size columns in the above 2 tables for temp db's do they represent different values ?
Everytime I try to open up SSMS, I get this error:
"Values cannot be null. Parameter Name: ViewInfo"
When I press ok to login, It doesn't show me the database and system dbs aren't expandable.
I have created the below function and apply it on a column in a table to replace the below identified values with Blank. It works fine but i have so many different varieties of values i need to add to ths list. Is there any way i insert these values in a table and call the values from that table instead of writing separate SET Statements.
CREATE FUNCTION [dbo].[sv_ReplaceChar] (@badString varchar(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
[code]...
I have basic knowledge of T-SQL and I am using Cursors to get the first value, the last value and the peak value and some other values from other tables. I found some examples on google but the code I am using is mixed up. I am using multiple Cursors. I need to join three tables to get the result set into the Cursor. The first example uses 2 tables.
@FirstName NVARCHAR,
@LastName NVARCHAR,
@FirstValue decimal,
@HighestValue decimal,
@LastValue decimal
-- First Cursor
DECLARE TESTCURSOR CURSOR
DYNAMIC
[Code] ....
The above code seems totally inefficient but it gives the correct result. Now I want to pull some more value and join a third table (TABLE z) in the above CURSORS and not sure how to make it working using CURSORS.I would like to use the following in the CURSORS above.
SELECT x.publishdate, y.firstname, y.lastname, y.age, z.initialValue AS FirstValue, z.HighestValue AS Highest, z.LastValue AS Last
FROM TABLE x
LEFT OUTER JOIN TABLE y
ON x.id = y.id
INNER JOIN TABLE z
ON x.id = z.id
I have a table with constantly changing data - stock.
I want to start monitoring the value of stock at the end of each month. I can do this with a simple query, export the results to Excel and store on the network.
My question is:
Are there better ways to do this with SQL Server?
I thought of a monthly "job" that does the query and outputs to a file. (Need to be able to look at each month separately though for trend monitoring.)
Then wondered, if I should have an extra table to store the data and write queries on that in the future?
I tried to find a forum for "tools" with SQL Server 2008, but couldn't find anything, so hoping this is the right forum for my question.
I have recently installed RedGate SQL Search. Although I can use this tool for a lot of stuff, I mis-understood the details and thought it not only searched database objects, but would search inside individual rows inside tables for a value, but it does not do this.
I have been trying to search for a comparable product. I have found a bunch of stored procedures to do this, but I do not want to create a bunch of stored procedures for all the various databases we have, nor do I have the permission to do this.
Are there any tools like SQL Search that integrate (not a requirement, but definitely a nice bonus) with SSMS and will search the tables for a value in any given row, listing the table and column (it would be nice to list the entire row, but not required as with table and column, I can do a search for the row) for the given value?
I have found this product: [URL] ...., but I do not have any information on the company to know if they are reputable.
Hi all,
I have run into a problem that I am stuck on. I have 3 tables lets call them a,b, and c. What I want to do is left outer join a with b on a common value, then replace any null values in this result with a default value, and then left outer join table c on the previously joined table (on the columns that I just replaced certain values on).
please help!
Thanks
i was trying to use the XML read functionality using t-SQL for XML attached.The column is coming with the token names and token-values in XML format and we are using the XML nodes() functionality to read the token names and token value.I am able to read only the parent token names and its values(using the sql attached) and could not be able to get the child token names and its values.how can i acheive the tokenNames with its values with the SQL query.i am attaching both SQL script which i am using and the XML entity.
View 9 Replies View Related