Transact SQL :: Append A Column To The Huge Transaction Table

Oct 18, 2015

I want to append the column to the transaction table(60 million records in it.) ..

Our transaction table is being used in production.. but i have very less amount of time ..

Instead of alter table.. (IF we use the alter to take backup of table and do the processing it will take more time). Is there any way to append the column to the transaction table ..

View 8 Replies


ADVERTISEMENT

Transact SQL :: Table Locking Happening When Removing Huge Data

May 14, 2015

declare @error int, @rowcount int
select @rowcount = COUNT(1) FROM  STG_BCDR;
while @rowcount > 0
begin
 BEGIN TRAN Deletion

[code]....

Above code i try to delete records batch by batch to avoid table locking at BCDR table.total records in this BCDR  table is 40,000 records.  However I run the code at execution plan, the BCDR table still clustered index scan which means that the locking still happend.

If i change the delete top (5000)...... to delete top (5).... then thre is clustered index seek, which is good..The problem here is  each time  only delete top 5 records which is means it will realy take very long time to remove those  data.

how to cater the situation inorder for me to delete those huge data without table locking happend. If table locking happend , then other user will not be able to access this table at the same time.

View 6 Replies View Related

Huge Deletes In A Huge Table

Apr 3, 2000

SQL 7 SP1 NT4 SP5

I have a TRANSACTION table with 150 million rows.

I have a USER table.

Each user has about 600 records in the TRANSACTION table.

The TRANSACTION cluster index is on USERID + RECID . The second index is on USERID + Fieldx + Fieldy.

The TRANSACTION table gets about 1.4 million inserts in a normal day and about 40,000 updates.

I want to go through the USER table and delete all users who have not visited me in a while.

I want to do this without substantially hindering performance in a production environment. I can perform this over a week period or two if needed.

The best way I thought of doing this was to grab x amount of users in a cursor and loop through deleting their corresponding TRANSACTION records.

Does anyone have any ideas on a better way. What is going to happen to my indices during this time ?

Thanks !!!

View 3 Replies View Related

Huge Transaction Log - Do I Need This?

Nov 19, 2004

I just peeked at my DNN setup and I found that I have a transaction log about 98 gigs large, compared to a DNN database that is only about 250 megs. Crazy, huh?

Do you happen to know what I need that transaction log for? Can I just delete it or will it break my SQL db? Is there a way that I can keep only maybe a week of transactions in it so it doesn't grow so dang large?

Thanks in advance for your response!!

Tim x 4
(always learning!)

View 2 Replies View Related

Huge Transaction Log

Aug 10, 2000

One of my production databases is currently 51 mb. The transaction log is well over 5 gig. I have tried truncating and then shrinking the log through the use of SQL utilities. This does not work! How can I quickly resolve this problem without tampering with the production environment?

Thanks in advance.
Ray Reinders

View 1 Replies View Related

Huge Transaction Log

Sep 30, 2002

I am not a DBA and I run a personal web site that has gotten pretty large. I have never done anything to maintain my sql server, and now my transaction log is 10 Gigs and my data is only like 300 Megs. I am starting to get a memory leak with the sql service. What should I do? Is it bad to have a huge transaction log. I am not familiar with any of this stuff, so someone please point me in the right direction.

View 3 Replies View Related

Transact SQL :: Querying Data From Master Table Into Transaction Table?

Oct 13, 2015

I am stuck in the following scenario.

Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01

[code]....

View 5 Replies View Related

Transaction Logs Are Huge

Mar 19, 2008

Hi all our It Admin is having issues with the backups. He was doing full backups every 4 hours with backupExec, which means thats way too much. But now hes trying to do a simple recovery now, because obviously the transaction logs have not been truncated. Its a big mess, I'm not involved in this part, they handle the backing up and permissions. Transaction logs are huge??

Any suggestions??

View 12 Replies View Related

Maintenance Job Generates A Huge Transaction Log

Oct 12, 2001

Recently I did an upgrade form SQL 7 to SQL 2000. Everything went smoothly
except there is a little problem with an Optimization job (reorganize data and index pages/remove unused space from database files). The job itself runs
fine. But when I do a transaction log backup, it generates a huge log backup file. I don't remember that there was such a huge-sized transaction log backup file when I ran it on SQL 70. Is SQL 2000 different from SQL 7 on this aspect?

View 1 Replies View Related

'transaction Log Backup' File Is Huge

May 19, 2007

Hello all,

I have inherited a SQL 2000 server, and am therefore an absolute beginner of SQL2000.

I know this has been covered before, but I don't know how to use the KB as I don't know how to run the commands/script.
http://support.microsoft.com/kb/272318/

I can no longer backup the SQL database because the 'transaction log backup' file is about 17GB. The SQL database is only about 2GB! The partition that it is backed up onto fills up every day.

Can anyone help me?

Thanks
Puskas

View 4 Replies View Related

Transact SQL :: Table To Store All Transaction Happened On A Single Day

Jun 11, 2015

I have a transaction table to store all the transaction happened on a single day. as per my requirement I wrote the query like this select Currency Code,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where TransactionCode in ('BNT' ,'BCN','BTC','STC','SCN','SNT') group by TransactionCode,CurrencyCode,TransactionAmount order by CurrencyCode..I got the result like this

My I want to show this result like this
                            
ARS
  BNT          0            0
  BCN        0            0
  SCN        1            12
  BTC        0             0
  STC        0             0
  SNT        0             0
      
[code]...

and so on for all the the currency lists.how can I achieve like this .

View 6 Replies View Related

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

Oct 14, 2015

I have the following table

Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
    1             |John                       |Baker
    2             |Carl                        |Lennon
    3             |Marion                    |Herbert

Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
    1        |1/1/14      |12/30/14
    2        |1/1/15      |12/30/15

[code]...

I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID  AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID  AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID  AS SickBalance

and the table should be shown as below after executing the query

EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance

View 4 Replies View Related

Transact SQL :: Get Table And Column Name In Separate Column Using PIVOT

Jul 16, 2015

Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:

Text                                                     QueryPlan             Plan_handle  
         Name         Value

select id,name,Address from person     <showPlznXML...   010101                 Table            Person
select id,name,Address from person     <showPlznXML...   010101                 column         id
select id,name,Address from person     <showPlznXML...   010101                 Table            Person

[code]....

View 26 Replies View Related

Transaction Log Huge Loading SQL Profiler Trace Files In Simple Mode

Dec 8, 2007



Hi there - can anyone advise on the following issue. We have recently performed some server side tracing on a particular SQL instance over 24hr period. We are now attempting to load these into a database for analysis. Here lies the problem.

When we are loading the profiler trace files (one at a time) into the database the transaction log is growing at an excessive rate. Even though the database is in SIMPLE mode.

We are loading the traces using the command:

INSERT INTO sqlTableToLoad
SELECT * FROM ::fn_trace_gettable('MytraceFileName', DEFAULT)

Can anyone advise how we could possibly get round this issue as we're running out of space due to the transaction log.

Thanks

View 5 Replies View Related

Transact SQL :: Huge Performance Difference For Same Select Between Environments

Jun 22, 2015

I have encountered a problem with a specific set of tables. The same select yields slightly differing execution plans in two different environments (instances). But the slight variation seems to contain a huge differences in stats. I don't know the significance of these stats. The two tables have the exact same indices.

This is the selcet statement:

SELECT 'xx' FROM DUKS.dbo.Profiler
WHERE DNA_Løbenummer IN
(SELECT DNA_Løbenummer FROM DUKS.dbo.Effektregister
WHERE Sagsnummer = '2015-00002')

View 17 Replies View Related

Transact SQL :: How To Alter Existing Table Column As Identity Without Dropping Table

Nov 20, 2013

I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.

create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)

View 7 Replies View Related

Transact SQL :: Delete Records From Table (Table1) Which Has A Foreign Key Column In Related Table (Table2)?

Jun 29, 2015

I need to delete records from a table (Table1) which has a foreign key column in a related table (Table2).

Table1 columns are: table1Id; Name.  Table2 columns include Table2.table1Id which is the foreign key to Table1.

What is the syntax to delete records from Table1 using Table1.Name='some name' and remove any records in Table2 that have Table2.table1Id equal to Table1.table1Id?

View 11 Replies View Related

Want To Append Column Value In Textbox To Each Other

Jul 9, 2007

I have one cloumn with 10 rows and I want to read all those value to textbox, or another way I want to append all value to each other, I think I have to loop through it, so how can I do this. I have this column in SQL table. some plese tell me how to do this
thank you
maxmax

View 4 Replies View Related

Transact SQL :: Order By A Column From Another Table

Sep 2, 2015

I have two tables, one is called (questions), the second one (answers).

questions columns are (ID,questionTitle)

answers columns are (ID,questionID,answer, answerDate)

I use this query to load data: "SELECT q.questionTitle,COUNT (a.ID),a.answerDate FROM questions q LEFT JOIN answers a ON q.ID=a.questionID" the query is easy, but my problem which I can't solve is how can I fetch the data ordered by the column answerDate, I mean I want the first record to be the one which has the most recent answer and so on.

View 12 Replies View Related

Transact SQL :: Get Table And Column Names From XML

Jul 6, 2015

I am trying to find the Table and column names from the below.

Is there a way i can get table name and column name from query_plan column?

SELECT TOP 100  text, query_plan,cp.plan_handle
FROM sys.dm_exec_cached_plans cp 
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
       CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
WHERE objtype='Adhoc'

View 5 Replies View Related

Transact SQL :: Getting Table And Column Names

Jul 21, 2015

 SELECT TOP 100  text, query_plan, cp.plan_handle, qs.last_execution_time
FROM sys.dm_exec_cached_plans cp 
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
       CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
  JOIN sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle
WHERE objtype='Adhoc'

I have below output:

Text      Query_plan     Plan_handle     Lst_execution_time
Select id,name from person                  
<Showplan...   dshkkgdaHqrqe13232423    2015-07-21 10:50:22.713
Update customer set Cid=3 where name='abc'      
<Showplan...    poasfvrqe13232423    2015-07-21 10:16:22.500
delete from orders where  ORid=8    
<Showplan...     2ase2423     2015-07-21 10:10:22.710
Select 1,2,3,4,5 from num  
<showplan            afqfqfq   2015-07-21 10:10:22.710
 
I am looking for 

Text           Query_plan                   Plan_handle      Last_execution_time        TabName colname
Select id,name from person  <Showplan... dshkkgdaHqrqe13232423   2015-07-21 10:50:22.713  
Person Id, name Update customer set Cid=3 where name='abc'  
<Showplan   poasfvrqe13232423   2015-07-21 10:16:22.500  customer  Cid,  name
delete from orders where  ORid=8   <Showplan...   2ase2423     2015-07-21 06:10:22.710    Orders    ORid
Select 1,2,3,4,5 from num <showplan            afqfqfq   2015-07-21 10:10:22.710        nUM      1,2,3,4,5

View 2 Replies View Related

Transact SQL :: Create Table With Timestamp Column

Jul 2, 2015

Im trying to insert the values from this query into a table, so I can later check the history of memory usage:

SELECT
[total_physical_memory_kb] / 1024 AS [Total_Physical_Memory_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Physical_Memory_In_MB]
,[total_page_file_kb] / 1024 AS [Total_Page_File_In_MB]
,[available_page_file_kb] / 1024 AS [Available_Page_File_MB]
,[kernel_paged_pool_kb] / 1024 AS [Kernel_Paged_Pool_MB]
,[kernel_nonpaged_pool_kb] / 1024 AS [Kernel_Nonpaged_Pool_MB]
,[system_memory_state_desc] AS [System_Memory_State_Desc]
FROM [master].[sys].[dm_os_sys_memory]

What I'm missing is a way to insert the current timestamp every time I insert to the table.My plan is to use the insert into command.

View 3 Replies View Related

Transact SQL :: Column Update With Comparison With Another Table

Nov 10, 2015

I upload data from a Txt File(Txt_Temp) where I have VinNumber with 6 digits. Another table name Resrve_Temp1 where I have Vinumber with 17 digit. Now I need to update the vinnumber 6 digit to 17 digit or to new column in Txt_temp.

Txt_Temp - Table

I tried this code with no succes and only one row is updating

update Txt_Temp Set Txt_Temp.Vinnumber=dbo.R_ResrvStock.Vin
from dbo.R_ResrvStock inner join Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=Txt_Temp.VinNumber

OR Add this code in view 

Select dbo.R_ResrvStock.Vin,R_Txt_Temp.Vinnumber,R_Txt_Te mp.Model_Code 
from dbo.R_ResrvStock inner join R_Txt_Temp on Right (dbo.R_ResrvStock.Vin,6)=R_Txt_Temp.VinNumber

Vin
123456
123123
123789

Resrve_Temp1 - Table
asddfghjklk123654
asddfghjklk123456
asddfghjklk321564
asddfghjklk123123
asddfghjklk123789
asddfghjklk654655
asddfghjklk456465

My Result can be in Txt_Temp table or new table or with one or two columns

asddfghjklk123456 123456
asddfghjklk123123 123123
asddfghjklk123789 123789

View 10 Replies View Related

Transact SQL :: Display Different Table Having Same Column With Different Data

Sep 25, 2015

I am having two table i.e( tbl_oldEmployee , tbl_NewEmployee ),which is having Column name Employee Name and City same in both table but inside column data is different in different table.but i want to view the Employee name and City from tbl_NewEmployee to tbl_oldEmployee which is having EmployeeId common with tbl_oldEmployee extra record also required (i.e  tbl_NewEmployee having 6 record  and tbl_oldEmployee having 10 record,so i need to display data from  tbl_oldEmployee but first 6 record which id match with tbl_NewEmployee id should be replaced and extra data from tbl_oldEmployee also display).

View 3 Replies View Related

Transact SQL :: Table With SPARSE Column Need More Space?

Jul 24, 2015

As I understood, if SPARSE is used on a column, which have many NULL marks, then the storage could be efficently used (we need less spaces to save NULL marks, hence a table which has many NULL marks with SPARSE property needs less storage than the same table, but without SPARSE. I created two table as follow:

/******* Table with Sparse ******/CREATE TABLE Sprstb(
unsprsid INT IDENTITY(1,1) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
adress nvarchar(60) SPARSE NULL)/***** Table without Sparse*******/CREATE TABLE Unsprstb(unsprsid INT IDENTITY(1,1) NOT NULL,Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
address nvarchar(60) NULL)

I have populated the Sprstb with 5 Milion records. It needs 509,961 MB storage. Then I have copied this table into Unsprstb

SET IDENTITY_INSERT [dbo].[Unsprstb] ON
Insert [dbo].[Unsprstb](unsprsid,Firstname,Lastname,Tel,adress)
SELECT unsprsid,Firstname,Lastname,Tel, adress FROM [dbo].[Sprstb]
SET IDENTITY_INSERT [dbo].[Unsprstb] OFF
The Unssprstb need only  466,031MB !

That means the Table with SPARSE column need more storage, Why? 

By the way, in table Sprstb column address has  1666198  Null mark (from 5000000)

View 5 Replies View Related

Transact SQL :: XML Data Column - Insert Into Another Table

May 29, 2015

I have table with about 10000 rows,  there is a column named  Raw_XMLData is defined as varchar but data is xml format.  

I try to insert into another table, if Raw_XMLData column has is valid xml data?

Is it possible to do in T sql?

View 2 Replies View Related

Append Query From Access Table To Linked SQL Server Table Failing

Jun 18, 2004

Strange one here - I am posting this in both SQL Server and Access forums

Access is telling me it can't append any of the records due to a key violation.

The query:

INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;

Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.

There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.

I can manually append the records using cut and paste with no problems.

I have tried re-linking the tables.

Any ideas?
Thanks,
Brad

View 4 Replies View Related

Transact SQL :: Alter Table To Rename Column Name With No Space

Oct 27, 2015

I want to alter my all tables to change the name of all columns.
 
I need this because all column names were created with space and I want to remove the space for future work .

For example – In Table Customer there is a column name [Cust_Id ] and I want to change it with [Cust_Id]

View 11 Replies View Related

Transact SQL :: Failure Update Table Column Script

Jul 17, 2015

I am dynamically creating a job using sql script and it does work fine(It creates the job and when it's done it gets deleted as it's created dynamically. so I won't be having any job history at all in the system.). I want to update an existing table if the jobs fails and I am not sure how I can do that using t-sql script. Is that possible?I have an idea but not sure whether it works. In the job create script, can I include the code for if the job fails then go to step 2 and update the table column with the error??? If so, how can I retrieve the error???

View 3 Replies View Related

Transact SQL :: Populate Column On Insert Based On Value From Another Table

Jul 26, 2015

I have the following 2 tables:

Table: classes  Columns: classID, hp
Table: char_active  Columns: name, classID, hp

The classes table is already populated.

What I want to do is insert a new row into char_active using the name and classID column, and have the HP column auto populate based on the corresponding value in the classes table. This is the trigger I wrote but I'm getting the error

Incorrect syntax near 'inserted'.

I'm new to sql, this is actually the first trigger I've tried writing. 

create trigger new_hp on curr_chars.char_active
instead of insert
as
declare @hp tinyint
select @hp=lists.classes.hp from lists.classes where lists.classes.classID=inserted.classID
insert into curr_chars.char_active (name, classID, hp) inserted.name, inserted.classID, @hp
go

View 4 Replies View Related

Transact SQL :: Get 3 Comma Separated Values Into 3 Column Of A Table

Aug 21, 2015

I have 3 variables that gets comma separated values. My requirement is to get them into a temporary table with 3 columns and each column should have single value. E.g. if 

Declare @SID varchar(max),@CID VARCHAR(MAX),@KID VARCHAR(MAX)
Set @SID='1,2,3,4'
Set @CID='6,7,8,9'
Set @KID='A,BB,CCC,DDDD'

--Now my requirement is to get them in a temp table with 3 column and different rows as per comma separated values in variables.

Now my requirement is to get them in a temp table with 3 columns and different rows (as per number of comma separated values in variables) E.g.

16A
27BB
38CCC
49DDDD

How i can use them for joining with other tables.

View 5 Replies View Related

Transact SQL :: Removing Duplicate Values In ID Column Of Table

Oct 14, 2015

I need write a query for removing duplicates, for Example in my table I have columns

A_ID name id
1 sam 10
2 sam 10
3  sam 10
4 sam 10
5  ccc 15
6 ccc 15
7  ccc 15
8 fff 20
9 fff 20
10 fff 20

So now I have duplicates values in id column so now I need to take only one value of each and delete the remaining. I need to take first id value 10,15,20 so only 3 rows should be there in my table.

View 4 Replies View Related

Append Query To Insert 80000 From One Table To Empty Table

Jul 22, 2012

All, Using access 2003 frontend and sql server 2008 backend. I have an append query to insert 80000 from one table to an empty table. I get an error:

"Microsoft Office Access set 0 field(s) to Null due to a type conversion failure, and didn't add 36000 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations."

I know this error normally comes if there are dups in a field that doesnt allow.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved