T-SQL (SS2K8) :: Table Changes Lost

Nov 19, 2014

In SSMS, I used the ALTER TABLE statement to add a field to a table. No errors are generated and I can verify that the field has been added. Since there are only about 20 records in the table, I use the Edit All Records option to populate that new field. Later, when a job runs using that new field, it fails because the field suddenly does not exist. Sure enough, I check the table and the field is gone. To be more specific, I can add and populate the new field at 2pm. At 6pm, when the job runs, the new field is apparently gone.

I have searched for answers but only have found responses related to the "Prevent saving changes that require table re-creation" in the Tools|Options settings in SSMS. I did uncheck that option, but the problem persists. Based on my searches, the only other alternative is to recreate the table with the new field and move the data from the "old" table to the "new". I got the impression that unchecking that option would mean I would not need to do all this.

I know I have done this type of change before with no issues. What may have changed?I am using SQL Server 2008R2 SP2 on Windows Server 2008R2 SP1.

View 9 Replies


SQL 2012 :: Permissions Lost After Renaming Table?

May 28, 2015

We have a process that uses the following method to move data quickly:

TableA = 600million records
TableB = 0 records

To "move" data from TableA to TableB

Rename TableA --> TableA_HOLD
Rename TableB --> TableA
Rename TableA_HOLD --> TableB

The problem with this is that after every rename, permission to the TableB is lost so we have to execute a statement to grant permission back to TableB after the process is complete.

My question is why is this necessary? Is the permission tied to the table in such a way that it can't use the name only?

View 9 Replies View Related

Global Variable Value Lost During Insertion In A Table

Oct 6, 2006


This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.

This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.

My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?

How can i insert the value stored in a variable in a table from previous SQL Task.

Can anyone suggest some solution,links to try a workaround?

Thanks in advance.



View 4 Replies View Related

T-SQL (SS2K8) :: Procedure That Create Views With Table Name And A Table Field Parameter?

Aug 4, 2015

I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).

However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.

Below code.

ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)


View 9 Replies View Related

T-SQL (SS2K8) :: Pulling Records From A Table Between Date Ranges In Another Table

Mar 17, 2014

This seems simple enough but for some reason, my brain isn't working.

I have a lookup table:

Table A: basically dates every 30 days


I have Table b that has records and dates created assocated with each record

I want all records that fall between the 1st 30 days to have an additional column that indicates 30


records with additional column indicating 60 days that fall between the 30 and 60 day


records with additional column indicating 90days that fall between the 60 and 90 day mark.

Is there an easy way to do this?

View 6 Replies View Related

T-SQL (SS2K8) :: Insert Causing Delay From Table To Table

Oct 28, 2014

I am trying to move data from one table to the another (staging to real time) in a stored procedure.

There are no indexes or primary keys on the target table and it is still taking ages to execute it (30 minutes approx.). There are no defaults, no constraints as well. There is one identity int column though.

There are some 500000 odd rows in the target table.

I am using the

Insert into..
Select from..


View 2 Replies View Related

T-SQL (SS2K8) :: Insert Subset Of Self-referencing Table Into That Table

Mar 19, 2015



[Code] ....


nRow_Id nParent_Id cGroup cValue
----------- ----------- ------- ------
1 1 One A
2 1 One B
3 2 One C

I want to insert a copy of this data, but w/ group = 'TWO', so the table will contain the additional rows

4 4 Two A
5 4 Two B
6 5 Tow C

View 5 Replies View Related

T-SQL (SS2K8) :: Update One Column For One Table From Another Table?

Jul 8, 2014

I have to tables say 'employee1' and 'employee2'

employee1 has lastname
employee2 has firstname, lastname

i have to update firstname in employee1 from firstname in employee2 table and the common field for both tables is 'lastname'

View 8 Replies View Related

T-SQL (SS2K8) :: Date Comparison In Two Table By Returning Nearest Date Of Table A In Table B

Jun 9, 2014

I am having a problem in creating query for this exciting scenario.

Table A

ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000

Table B

A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000

I would like to return the nearest date of Table B in my table like for

ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000

View 3 Replies View Related

T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B

May 6, 2014

I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid

View 9 Replies View Related

T-SQL (SS2K8) :: To Get Top 10 From A Table

May 18, 2015

I have a more than 50 departments in my table and trying to get top 10, something I need to show in a report like top9 departments and then rest of them has to show as Others in 10th row. is it possible ?

when I use top 10 from table I am getting top 10 rows but I need the top 9 list and rest as others in 10th row.

I have table like below

Custid, department, location
1 Fin NY
2 Acc NY

View 3 Replies View Related

T-SQL (SS2K8) :: How To Calculate Sum Value In Table

Mar 13, 2014

create table cust_details
id int ,
city varchar(20),
cust_name varchar(20),
sales int

insert into cust_details


My expecting o/p:

city customer sales
tamilnadu 101 500
102 300
total 800
customer sales
101 500
102 300
total 800

Like I wanna display separate location?

View 2 Replies View Related

T-SQL (SS2K8) :: How To Sum Columns In Table

Dec 16, 2014

I've the table structure below. Example table of my original

create table fms
fs_locn char(100),
fs_account_no varchar(200),
fs_cost_center_no varchar(100),
fs_tran_type char(50),
fs_post_amt float,
fs_tran_date datetime

[Code] ....

Expecting Output :

Account 200Prod 201PROD ProdcutionCost
E002-SW100-2100 2500 1000 3500

How to do that?

I tried like

my query

its showing error '200prod'

View 1 Replies View Related

T-SQL (SS2K8) :: One Table Two Rows

Feb 4, 2015

What I want is to divide the row value for 'OB Dial Attempts' by the row value for '# Ready To Work Inventory', both in the same table.The code below will work, however I think there is a flaw in my logic. The actual table only has one row per category (MatrixCat) and will always have only one row per category.

CREATE TABLE #NumVals (MatrixCat VARCHAR(100), MatrixVal VARCHAR(100));
INSERT INTO #NumVals (MatrixCat, MatrixVal) VALUES
('# Ready To Work Inventory','606'),
('OB Dial Attempts','255');


View 8 Replies View Related

T-SQL (SS2K8) :: Filter Records In Table?

Mar 6, 2014

I have a table that has multiple records as illustrated in the simple list below. The real list is much longer.

MachineA 1/1/2008
MachineA 1/3/2008
MachineB 1/7/2008
MachineB 1/8/2009
MachineB 5/5/2010
MachineA 5/7/2011
MachineA 4/2/2013

I need to query to return a result for each unique machine with the latest date. The example result below would be returned because they have the latest date.

MachineA 5/7/2011
MachineB 5/5/2010

Select Distinct would almost do it, but I need each unique machine that has the latest date.

View 9 Replies View Related

T-SQL (SS2K8) :: Group Columns To One Row In A Table

Apr 1, 2014

I have a table with the following columns

74 ,1,2,beck
74 ,1,2,greg
74 ,1,9,mike
74 ,1,9,laggo
74 ,2,2,beck
74 ,2,2,greg
74 ,2,9,mike
74 ,2,9,laggo

I am trying to get the result as follows.

Num Id Final Value

74 1 2,beck,greg;9,mike,laggo
74 2 2,beck,greg;9,mike,laggo

I tried to use Stuff and XMLpath but it is not giving me distinct results.

View 4 Replies View Related

T-SQL (SS2K8) :: How To Get Distinct Records From Table

Apr 14, 2014

Suppose I have 2 table


Main table maintain all the records having columns MAIN_SKU,DEDUCTIBLE_AMT,model_id,catagory,ModifiedDate

IF DEDUCTIBLE_AMT is changes it will place entry in history table ,columns are same with history_id

i want to display distinct main_sku from history table(all columns) with last DEDUCTIBLE_AMT changed from history table

table structure
main table
History table

View 3 Replies View Related

T-SQL (SS2K8) :: How To Get One Row On A Table With Multiple Records

Apr 24, 2014

I have a table called TBLCataloghi

I have multiple records with colunms codpro and codcat equal

They differ only by a date called catalog.datfin

I'd like to select all rows but with the same codpro,codcat, obtaining ONLY the row with MIN () field datfin

Field datfin is a date..

Ex. codpro = 'PIPPO'
codcat = 'MK'
DATFIN = 01/01/2010

codpro = 'PIPPO'
codcat = 'MK'
DATFIN = 10/07/2014

I'd like to read both records but in SELECT obtain only the record with datfin MIN (01-10-2010)

I did the query but i was not able to do nothing of good. I obtain all times both records...

SELECT catalog.codpro AS CodProdotto,
catalog.codcat AS CodiceCatalogo,

FROM pub.catalog

WHERE catalog.codcat = 'MK'

GROUP BY catalog.codpro,catalog.codcat ,catalog.datfin

View 2 Replies View Related

T-SQL (SS2K8) :: Time Table For Simple ETL

Jun 17, 2014

I am trying to build a simple time table and pre-populate the rows for lets say 1 year. Basically, I will be extracting the data froma source based on the boundaries of this table. eg of this table as I can visualize:

Date StartTime Endtime IsProcessed
2014-06-17 2014-06-17 00:00:00 2014-06-17 01:00:00 0
2014-06-17 2014-06-17 01:00:00 2014-06-17 02:00:00 0
2014-06-17 2014-06-17 02:00:00 2014-06-17 03:00:00 0

Lets call this TimeTable and we have our souce and destination table..The ETL process will pick the start time and endtime, will pass these as parameters to the source and extract the data as follows:

Insert into Destination
select * from source where entrydate>=TimeTable.StartTime and entrydate<TimeTable.EndTime

Once this data gets loaded, the IsProcessed field is set to 1 so that next time it won't be considered.I am trying an incremental load process here.

View 5 Replies View Related

T-SQL (SS2K8) :: Show Changes From And To In History Table

Jun 28, 2014

I have a table history of Employee data.

id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser

I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.

Result must be:

EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to

View 4 Replies View Related

T-SQL (SS2K8) :: Compare Rows In The Same Table?

Aug 7, 2014

We have a table setup to track changes that are made to another table, for auditing purposes. How do we compare the most recent record in the change table with the previous record in the change table? Particularly, we have a column named DUE_DATE in the change table and want to identify when the most recent change has a different DUE_DATE than the previous change made.

View 8 Replies View Related

T-SQL (SS2K8) :: Get Missing Dates In Table

Aug 18, 2014

I've a request: I've a table with a date column and an if numeric data type.

I've to check from now on 7 days ago if any value is missing in my date field.

So if I've f.i. in my table
1 2014-08-11
2 2014-08-12
3 2014-08-13
4 2014-08-14
5 2014-08-17

then my output should be:

How to do this?

View 1 Replies View Related

T-SQL (SS2K8) :: Create Table Dynamically?

Sep 5, 2014

I am having SP which gives, two result sets. The columns which are coming from result sets are also dynamic.
i.e. some time 5 columns and some time 10 columns.

Now I want to load this output into 2 different tables on daily basis. This would be truncate/delete table and load again.

Now my problem is that as I am not sure about columns, Is it possible to create table(Physical Table) depends on output of SP, and after load data into it.

During each load we can drop table, No issue and we can handle this through SSIS Package.

View 2 Replies View Related

T-SQL (SS2K8) :: Compare Next Row With Previous Row Of Same Table?

Sep 16, 2014

I need to compare the next row with the previous row of same table and produce nonidentical column.for eg... say my table has

Row 1 => 1001 Abhas 120 150 180
Row 2 => 1001 Abhas 150 150 180

then my output would be as below:

StudId Name fee1 fee2 fee3
1001 120
1001 Abhas 150 150 150

i.e in first row of resultset, i want to show only those values which are changed alongwith studID and next row should display all values.

View 9 Replies View Related

T-SQL (SS2K8) :: Generating One Table From Two Tables

Jan 14, 2015

There are two tables as below:

Table 1

Table 2

what is best way to generate one table as below:

New table

View 4 Replies View Related

T-SQL (SS2K8) :: Divide Using Formula From Different Table

Jan 23, 2015

I need to evaluate a formula in sql server 2008

Table 1 contains

Entity Value
A 2424053.500000
B 1151425.412500
C 484810.700000
Table 2 contains

Entity Formula
A (2100*(1-0.0668)*24*mday*10)
B (1000*(1-0.0575)*24*mday*10)
C (1260*(1-0.09)*24*mday*10)

Where mday is number of days taken from user

I need to calculate the output of value/formula for each entity can you provide me the query for the same

The datatype for formula column is varchar

I do not have the liberty to use cursors or loops.mday will be a input fromt the user say 'mday = 31' ..i need to divide the value in the first table with the computed value of the formula after replacement

View 1 Replies View Related

T-SQL (SS2K8) :: How To Insert Queries In A Table

Feb 9, 2015

I am trying to insert the queries in a table such as the following, not able to insert it.

create table test (txt Varchar(200))
insert into test values('select *from master.dbo.sysprocesses WHERE status NOT IN('sleeping','background')')

View 4 Replies View Related

T-SQL (SS2K8) :: Get Data Changes From History Table?

Mar 25, 2015

how to get products on which SalesPerson changed. Here is table with data.

View 9 Replies View Related

T-SQL (SS2K8) :: Script That Returns All SPs To Given Table

Apr 9, 2015

I am looking for a script, that returns all SPs, which related to a given table, incl. the access type.For example: the SPs SP_test1, SP_test2, SP_test3 are dependencies of MyTable

This should be the result:
--> SP_test1: select
--> SP test1: update
--> SP_test1: drop
--> SP test2: delete
--> SP_test3: insert

There are a lot of scripts, who returns the related SPs.

View 9 Replies View Related

T-SQL (SS2K8) :: Get Rows And Sum In Joined Table?

May 2, 2015

I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.

What I tried, but what returns NULL for purchasedamount, where I want purchasedamount to be the sum of the `amount` for THAT item, based on giftregistrypurchases.itemid=giftregistryitems.id:

SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,*
FROM giftregistryitems gi
LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id
WHERE gi.registryid=2

How can I achieve what I need?

Here's my table definition and data:

USE [tt]
/****** Object: Table [dbo].[giftregistry] Script Date: 09-05-15 11:15:18 ******/
CREATE TABLE [dbo].[giftregistry](
[id] [int] IDENTITY(1,1) NOT NULL,


Desired results:



View 9 Replies View Related

T-SQL (SS2K8) :: Cursor Table Update

May 6, 2015

SELECT [ServersList] AS 'ID'
FROM dbo.Servers


How do loop a table server(serverlist,flag) table with these 2 columns.And ping each of the servers in the table and update the flag column to '1' if ping has been successfull or flag to '0' if ping has been unsuccessfull.

View 1 Replies View Related

T-SQL (SS2K8) :: XML Export From A Denormalized Table

Aug 12, 2015

I have a de-normalized table that I need to export to XML using For XML, but put all of the related rows under the same node.The table is alot more complicated than the example below, but for proof of concept purposes, i'll keep it really simple:

Campaign, Price
C1, 4.00
C1, 6.00
C1, 10.00
C2, 1.00
C2, 13.00
C3, 20.00

If I have a table of campaigns and prices, I would like to output it as XML like the following:

<Campaign name="C1">
<Price value="4.00"></Price>
<Price value="6.00"></Price>
<Price value="10.00"></Price>
<Campaign name="C2">
<Price value="1.00"></Price>

The default behaviour gives me this:

<Campaign name="C1" price="4.00"/>
<Campaign name="C1" price="6.00"/>
<Campaign name="C1" price="10.00"/>

Is there an existing option that deals with this automatically, or do I essentially need to do a group by to output the campaign element, and then union an ungrouped select to output the price element?

View 4 Replies View Related

T-SQL (SS2K8) :: Return All Values From Table

Oct 1, 2015

I have a small problem with a join clause, because i need to return all values from my table BL:

my code is:

SELECT cast(0 as bit) as 'Escolha',data, contado , ollocal ,origem, ousrdata,ousrhora
SELECT noconta,banco, u_area


In fact, i need to return 2 accounts (16,35) - x.NOCONTA IN (16,35), but I know that the problem is on the WHERE clause.How can do that, because i need all the condition on WHERE clause regarding my table OL, but also, i need to return my two accounts (16,35).

View 2 Replies View Related

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