SQL Server 2012 :: Compress Table Timelines
Jul 8, 2014
Here is data I am working with:
CREATE TABLE HISTORY(CUSTOMER VARCHAR(10), PLANNBR VARCHAR(10), SUBPLAN VARCHAR(3),
STARTDATE DATETIME, ENDDATE DATETIME, HISTORYMONTH VARCHAR(6))
INSERT INTO HISTORY(CUSTOMER, PLANNBR, SUBPLAN, STARTDATE, ENDDATE) VALUES('9111111', 'H1111LAC', '006', '2014-01-01', '2014-05-31', '201401')
INSERT INTO HISTORY(CUSTOMER, PLANNBR, SUBPLAN, STARTDATE, ENDDATE) VALUES('9111111','H1111LAC', '006', '2014-01-01', '2014-05-31', '201402')
[code]...
I need to compress these segments into records which will look like this. The HistoryMonth column is not used to make the final determination of records to keep.
CUSTOMERPLANNBRSUBPLANSTARTDATEENDDATE
9111111H1111LAC0062014-01-012014-05-31
9111111H1111OC0102014-06-012999-12-31
9111112H1111LAC0062014-01-012014-05-31
9111112H1111LAC0182014-06-012999-12-31
9111113H1111LAC0062014-01-012999-12-31
9111114H1111LAC0062014-01-012014-02-28
9111114H1111LAC0062014-04-012999-12-31
Customer 9111111 shows a change in both PlanNbr and SubPlan 2014-06-01 Need to show both records
Customer 9111112 shows a change in SubPlan 2014-06-01 - Need to show both records
Customer 9111113 shows no change in PlanNbr or SubPlan - Need to show 1 record
Customer 9111114 show a break between and enddate and next start > 1 day - need to show both records
View 5 Replies
ADVERTISEMENT
Oct 12, 2014
I would like to do a group by when the timestamp is different from an usual pattern,
create table #tbl ( V1 int, V2 datetime, V3 float)
insert into #tbl values (1,'2012-12-12 10:15', 12.5)
insert into #tbl values (1,'2012-12-12 10:35', 2.5)
insert into #tbl values (1,'2012-12-12 10:45', 1.5)
insert into #tbl values (1,'2012-12-12 11:15', 10.5)
insert into #tbl values (1,'2012-12-12 11:30', 11.5)
select sum(v3) [value], v1 from #tbl group by v1
drop table #tbl
i would like to have result set as, aggregating results of every 15 minutes and when it exceeds 15 minutes then add the same in next group by. This 15 minute interval can be specified in the query itself.
value v1
16.5 1
221
View 9 Replies
View Related
Jan 22, 2015
I have a SQL 2012 enterprise server, and I'm using Commvault as my backups. So commvault can restore a .bak file to my server, but it cannot use sql compression on the file apparently. So what would be a 150GB .bak backup file is now 600GB. I have to manually upload these files to an auditing firm on an sftp server and the transfer times are now huge.
Is there a way to use something in sql to compress this already existing .bak file down?
View 5 Replies
View Related
Apr 13, 2015
I got this situation where my network admin observerd that there is a high network utilization between 2 nodes in our AG (the primary node & the DR site, 2 separate locations of course); then he advised to compress the data transfer between those 2 nodes as the previous DBA already did that before!
Ok, I have no clue about this, so decided to google it, got nothing. My backup is already compressed through some third party app (just in case if that matters to the subject).
View 3 Replies
View Related
Aug 23, 2007
i have a table that is often deleted from and added to.
i noticed that it gets realy slow -
is there a way to compress a table? (there must be even in access you can compact and repair)
is there sql code that I can call from my code after doing a lot of deleting to speak this up.
Please advise.
View 5 Replies
View Related
Jul 15, 2015
easy way to compress backup file. I use SQL Server 2008 R2
View 4 Replies
View Related
Feb 12, 2006
I work with MSDE, now the dimensions are of 2 GB....I have deleted many pages, is possible to compress the database?
Thanks in advanced
View 1 Replies
View Related
Aug 11, 2014
I need to use Bulk insert statement for copying a table with 200 million rows to another table on the same server...the table has no primary key or identity column.... script for BULK INSERT ...
View 9 Replies
View Related
Feb 15, 2005
I was planning on running a service where thousands of text messages are stored. Obviously I'd want to make the most of my DB space, and was wondering if there's some way for SQL to compress text down to the smallest space possible. If not, is there some kind of ASP component I could download to do this? Failing that, I could always write a simple one, which takes the most common letter combinations, and shortens them down to a single character.
Any advice?
View 4 Replies
View Related
Jul 15, 2015
Any easy way to compress backup file. I use SQL Server 2008 R2
View 3 Replies
View Related
Aug 12, 2014
I want Compare two Table data and insert changed field to the third table ...
View 9 Replies
View Related
Feb 11, 2015
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END
only on calling the proc does this give an execution error
View 3 Replies
View Related
Jan 14, 2014
What is the best way to transfer data from the staging table into the main table.
Example:
Staging Table Name: TableA_satge (# of rows - millions)
Main Table Name: TableA_main (# of rows - billions)
Note: Staging table may have some data same as the main table.
Currently I am doing:
- Load data into staging table (TableA_stage)
- Remove any duplication of rows from the staging table (TableA_stage)
- Disable all indexes on main table (TableA_main)
- Insert into main table (TableA_main) from staging table (TableA_stage)
- Remove any duplication of rows from the main table using CTE (TableA_main)
- Rebuild indexes on main_table (TableA_main)
The problem with the above method is that, it takes a lot of time and log file size grows very big.
View 9 Replies
View Related
Apr 29, 2014
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
View 7 Replies
View Related
Nov 19, 2014
I'm using SS 2012.
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest]
(
-- Add the parameters for the function here
@Source_Tbl sysname
)
RETURNS TABLE
AS
RETURN
(
select @Source_Tbl.yr from @Source_Tbl
)
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12
Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4
Incorrect syntax near the keyword 'table'.
Msg 137, Level 15, State 2, Procedure fnTest, Line 12
Must declare the scalar variable "@Source_Tbl".
The input table can have several thousand rows.
View 9 Replies
View Related
Aug 17, 2015
Is it possible to compress the data at restore time?.
Backup is coming from a not compressed database.
The backup file is compressed.
View 4 Replies
View Related
Jan 29, 2008
hi
If wanted to set up Logshipping between two sqlserver 2005 enterprise edition servers.The transaction log backup is being taken every 2 hours and with approx size of 1GB - 1.5 GB.
Now before the copy job runs,i want to compress the tlog file on primary server,copy it to secondary server and uncompress it on secondary and then apply transaction logs to secondary server.I need the procedure how to do it.Please help out.
Thx in advance
Regards
Arvind
View 3 Replies
View Related
Dec 23, 2013
I just created a new table with over 100 Columns and I need to populated just the first 2 columns.
The first columns to populate is an identify column that is the primary key. The second column is a foreign_key to an other column and I am trying to populate this columns with all the values from the foreign_key value. This is what I am trying to do.
column1 = ID
column2= P_CLIENT_D
SET IDENTITY_INSERT PIM1 ON
INSERT INTO PIM1 (P_CLIENT_ID)
SELECT
Client.ID
FROMP_Client
So I am trying to insert both an identity values and a value from an other table while leaving the other columns blank. How do I go about doing this.
View 1 Replies
View Related
Mar 7, 2014
Why the Indexes on table slow down the DML operation on table, what is the exact reason?
View 5 Replies
View Related
Oct 6, 2014
I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc
SET stc.[Talks] = fmt.found_talks_type
FROM @Find_Missing_Talks fmt
WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks)
AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
View 2 Replies
View Related
Sep 14, 2015
I have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
[code]....
View 9 Replies
View Related
Sep 18, 2014
I have a 2010 SSIS package where I am reading csv files with different fields and formatted data, I have created separate packages for each file and I am formatting the data to fit the final destination tables data elements, I've been instructed to create 7 separate packages to read each file and the format the data from the 7 csv files, and insert into their table tbl1, tbl2, tbl3...etc then, I'm taking a execute sql task and wanting to insert the tbl1, tbl2, tbl3...etc into destination table that will be the final table for all reports and other uses.
1- should I create a ID?
2- these files will be read once a month
3- I want to append the data, not drop and recreate each run,
4- It's 2012 SQL and 2010 SSIS
Each csv file is in a different format, some have 15 columns, other have 8 I have to parse the data, in SP to align with the fields in the destination table.
5- Can I force RowID to be the next auto gen number from tbl1,for the start of insert for tbl2, then last row of tbl2 for insert of tbl3???
View 0 Replies
View Related
Dec 25, 2014
I need to capture changes in my table and store it in another table. Here is how my table would look like.
CREATE TABLE [dbo].[ABC](
[Hno] [nchar](10) NULL,
[Status] [smallint] NULL,
[Date] [datetime] NULL
) ON [PRIMARY]
[Code] ....
In return I need to check column Status for whenever the value has changed and need to store that in my table. If there are two records for which value in column Status is same, I need to pick only one of the records and that being the earliest of them and therefore the Date field is mentioned in my table . My output should be something like below.
100 02014-12-20 00:55:44.667
100 12014-12-22 00:55:44.723
100 02014-12-24 00:55:44.723
100 12014-12-26 00:55:44.723
View 4 Replies
View Related
Feb 19, 2015
I need to create a table from these 2 tables and want all the data. Table 1 has 15000 records and table has around 1000 records.
I have 2 tables.
SELECT [UniqueID]
,[Company]
,[CustID]
,[CustomerName]
,[FiscYr]
[Code] ....
View 5 Replies
View Related
Mar 6, 2015
I am using SQL SERVER 2012..I have the following table:
tbtab1
| A | B | C |
| 1 | Pluto | NULL |
| 2 | Pippo | NULL |
| 3 | Rossi | NULL |
I want to creare a new empy table with the columns name contained into the column B of the first table; the following should my results:
tbtab2
| Pluto | Pippo | Rossi |
View 9 Replies
View Related
Jul 29, 2015
To avoid locking/blocking, or in transaction scope, we are trying make a common practice of writing coide for update commands in our all SPs based on primary key columns in where clause. I have a following scenario...
UPDATE [dbo].[TL_CST_Locker_Issuance] SET
[isActive] = 0
WHERE
LockerIssuanceId IN (SELECT LockerIssuanceId
[Code] ...
What is the better approach and should be followed to avoid locks and gain performance or best approach.
View 7 Replies
View Related
Apr 18, 2008
Is there any improvments in SQL 2008 backup methods such as spliting backup files in manageable size(s), compress the backups and/or improving the speed of backup?
Though there are "commercial" tools available, it would be nicer if Microsoft SQL team can incorporate some core needed features in these areas for Small/Medium size businesses.
This is not a question but a suggesstion.
Thanks
View 2 Replies
View Related
Dec 4, 2006
Hey everyone,
I have an issue where i am sending out files with 30,000+ lines and they are reaching the 11mb, 12mb in size.
This is becoming and issue for us, as we are only allowed to email up to 10mb in size.
I have tried reducing all spaces in the data, removing any graphics etc from the report , but still the excel file is over 11mb. One thing i did find was that, if i export it to excel, then open the file and save as a different file name the file size drops 50% !!
I was wondering if anyone has been able to zip/compress the exported file before it gets emailed?
It would be a great feature for MS to include in the next service pack.. Take advantage of the built in Zip support in Windows..
Look forward to hearing any suggestions that the community may have,
Thanks
Scotty
View 5 Replies
View Related
Oct 26, 2015
1. SQL 2008 R2
2. Maintenance Plan -
a. Daily, Weekly, Monthly schedule
b. Full Back Up - All Database, To Disk, for every database and Disk:DirectorySubDirectory*.bak Compress backup
3. SQL Agent Job
a. Runs the Sub_Plan's at scheduled time I've allocated for each Maintenance Plan.
4. These creates Multiple .bak for all database's that list in the directory.
How can I zip them into a compressed Directory.
View 13 Replies
View Related
Nov 19, 2013
I wonder if it possible to move data from tables on a linked server to a "normal database"?
Name linked server: Covas
Name table on linked server: tblCountries
Name field: cntCountryName
Name "normal" database: CovasCopy
Name "normal" table: Countries (or dbo.Countries)
Name "normal" field: Country
This is just a test setup. I figure that if I get this working the rest will be easier.
My current query:
select * from openquery(COVAS,'
INSERT INTO CovasCopy.dbo.Countries(Country)
SELECT cntCountryName FROM db_covas.tblCountries;')
View 8 Replies
View Related
Oct 26, 2015
Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?
USE MyTestDB;
GO
SET NOCOUNT ON;
IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL
DROP TABLE dbo.FactTemp;
[Code] ....
I'm using very small data at the moment, and the query plan and statistics don't really say which way.
View 2 Replies
View Related
Oct 11, 2014
I wrote query to update id int(1,1) to some value but its not giving me the result. Is it not possible to update id on the table.
View 7 Replies
View Related
May 31, 2015
I have a scenario to bold the total row coming from view.
SalesDateBrandNameLeadTypeLeadsSalesCR%
01-Jun-2015XYZLive811012.35 %
01-Jun-2015DEFOutbound41261.46 %
01-Jun-2015XYZDEFTotal493163.25 %
View 7 Replies
View Related