Transact SQL :: Table Trigger To Delete All Rows Before Inserting Rows

Jul 24, 2015

I have a SQL script to insert data into a table as below:

INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents

I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.

USE [BB]
GO
/****** Object:  Trigger    Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

View 3 Replies


ADVERTISEMENT

How To Create A Trigger Such That It Can Delete The Rows Whenever Any Other Application Such As Biztalk Had Read The Rows ?

Mar 12, 2007

I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

View 3 Replies View Related

Exporting Data From SQL Table To Excel File - How To Delete Rows Before Inserting New

Feb 5, 2007

Hi,

Question pls. I have an MS SQL local package where it exports data from SQL table to Excel file. My question is, how can erase all the records in my excel file before i export the new data from SQL table?

What i want is to delete the rows in the destination file before inserting new records.

Thanks a lot.

View 7 Replies View Related

Trigger On Table LeaveRegister And Inserting Rows In Audit Table

Oct 22, 2012

I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit table, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.

View 6 Replies View Related

Transact SQL :: Delete Duplicate Rows From Table?

Jul 21, 2015

I have a table with more than 1000 records. The columns look like this

Import id   |   Reference   |  Date  |  Username  | Filename

Some of the rows are duplicates except for the fact that they have different values for the first column alone (import id)

11111      test   21-07-2015   xxxxxx   yyyyyy
22222      test   21-07-2015   xxxxxx   yyyyyy

Is it possible to delete these kind of duplicate rows by ignoring the import id ?

View 9 Replies View Related

Transact SQL :: How To Delete Newly Inserted Rows Automatically From Table After 30 Minutes

Sep 29, 2015

I would like to perform autodeletion operation from my table using stored procedure....

Here is my table structure:

Emp.id | emp_name | dept | desig | time_out | date | emp_sign |

if the user inserts new employee record that record should delete automatically from original after

30 minutes..I don't want to keep that newly inserted record after
30 minutes...I don't know how to achieve this...

View 12 Replies View Related

I Have Created A Table Table With Name As Varchar And Id As Int. Now I Have Started Inserting The Rows Like, Insert Into Table Values ('arun',20).

Jan 31, 2008

I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50.                 insert into Table values('arun's',20)  My sqlserver is giving me an error instead of inserting the row. How will you solve this problem? 
 

View 3 Replies View Related

Inserting Rows Into Existing SQL Table

May 21, 2008

Hi all,

I am trying to insert some new rows into an existing SQL table. The table name is Agt_table, and I want to add some data for some new agents into existing columns:
Agent name, agent code, phone number, fax number

Example -
I want to add the following record to my existing table Agt_table
Agent name: ABC Company
Agent code: 012345
Phone #: 555-555-5555
Fax#: 555-555-5555

How would I write the sql statement to do that?

Thanks so much!!!

View 12 Replies View Related

INSERTING MULTIPLE ROWS IN A TABLE

Jun 20, 2007

Hi,

Is it possible to insert multiple rows in a table using one INSERT statment. If yes, how can I do that ? I tried doing this using the substitution method.

Using Substitution Method - This is how,I proceeded.

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
SELECT ‘&EMPN’, ‘&ENAM’, ‘&JO’, ‘&MG’, ‘&HI’, ‘&SA’, ‘&COMM’, ‘&DEPTN’ UNION ALL
SELECT ‘&EMPN’, ‘&ENAM’, ‘&JO’, ‘&MG’, ‘&HI’, ‘&SA’, ‘&COMM’, ‘&DEPTN’

Let me know if this is correct.

Thanks,
Vandana

View 2 Replies View Related

Inserting The Diff Rows Into A Table

Sep 24, 2007



Hi,
I have two tables as follows
Table TempTab
{

CatId varchar(20),
lastupdate Datetime
}

Table MainTab
{

CatId varchar(20),
lastupdate Datetime
}
and the data in those tables are as follows


Table TempTab
{

CatId LastUpdate
------------------------------
Cat1 D1
Cat2 D2
Cat3 D3
Cat4 D4
}



Table MainTab
{

CatId LastUpdate
------------------------------
Cat1 D1
Cat3 D3
Cat5 D5
}


I need a query to insert the differences into the MinTab, i mean to say the fincal MainTab should look like as follows
Table MainTab
{

CatId LastUpdate
------------------------------
Cat1 D1
Cat2 D2
Cat3 D3
Cat4 D4
Cat5 D5

}



can any one please let me know the query

Thanks alot
~Mohan

View 3 Replies View Related

Inserting Millions Of Rows Into A Table

Aug 10, 2006

Hi,

I have a DataTable in memory and I want to write a C# code to dump the data into a SQL database. Is there a faster way of dumping millions of rows into a SQL table besides running INSERT INTO row by row?

Thank you,

Jina

View 3 Replies View Related

Delete Rows From One Table Using Rows From Another Table

Jul 23, 2005

Using the the NumId from TitleData, I would like to delete thecorresponding row in Bookdata using pure SQL. I want it to delete allrows in bookdata where the Titledata.NumID is a match to bookdata.idThe two tables are linked in that the NumId of table Titledata isidentical to the Id of table bookdata. I can, using ADO, loop thrudeleting one by one but I would like to do this in a pure SQLstatement. Is this possible? Any help is appreciated.I was thinking something like this way :"Delete from Bookdata where Titledata.NumID = Bookdata.id"But of course it will error.My current code is:(frmlogon.tablename is really Titledata)Dim rstry As New ADODB.RecordsetDim values As VariantSQLQuery = "Select Numid from " & frmLogon.TablenameSet rstry = frmLogon.cnConnection.Execute(SQLQuery)values = rstry.GetRowsSet rstry = Nothing'now loop thruDim xx As Integerxx = 0Do Until xx > UBound(values, 2)SQLQuery = "Delete from Bookdata where bookdata.Id = '" & values(0,xx) & "'"frmLogon.cnConnection.Execute (SQLQuery)xx = xx + 1Loop'create statements for 2 tables involved areconn.Execute "CREATE TABLE TitleData" & _"(Id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY," & _"NumId INT DEFAULT 0 )"conn.Execute "CREATE TABLE BookData" & _"(Id INT IDENTITY (1, 1) NOT NULL," & _"Titles TEXT DEFAULT ''," & _"GeneralNote TEXT DEFAULT ''," & _"Author VARCHAR(100) DEFAULT ''," & _"Imprint VARCHAR(100) DEFAULT ''," & _"ISBN VARCHAR(100) DEFAULT ''," & _"Description VARCHAR(100) DEFAULT ''," & _"CallNumberPre VARCHAR(5) DEFAULT ''," & _"CallNumber VARCHAR(25) DEFAULT '',LOCNumber VARCHAR(30) DEFAULT '',"& _"Accession VARCHAR(25) DEFAULT ''," & _"Bibliography VARCHAR(100) DEFAULT ''," & _"Series VARCHAR(100) DEFAULT ''," & _"MyStatus VARCHAR(70) DEFAULT ''," & _"Barcode VARCHAR(50) DEFAULT ''," & _"LocalData VARCHAR(100) DEFAULT ''," & _"CheckoutPeriod VARCHAR(10) DEFAULT ''," & _"CatalogCard TEXT DEFAULT ''," & _"Summary TEXT DEFAULT ''," & _"MyCount VARCHAR(10) DEFAULT ''," & _"ItemDate DATETIME DEFAULT ''," & _"MyUser VARCHAR(50) DEFAULT ''," & _"MarcData TEXT DEFAULT ''," & _"SdlsRecord TEXT DEFAULT '', LOSC VARCHAR(5) DEFAULT '', LOSNDecimal(14,6) DEFAULT 0," & _"Edits Char(1) DEFAULT '', TitleDuplicate VARCHAR(50) DEFAULT '')"

View 14 Replies View Related

Transact SQL :: Login Name Of User Inserting Into Table With Execute As DML Trigger?

Jun 24, 2015

Suppose you have a simple DML "execute as..." trigger that looks like this:

CREATE TRIGGER dbo.myTrigger
 ON dbo.myTable
 WITH Execute As Owner
 AFTER INSERT
AS

[code]...

which runs as "owner" so that the user inserting into "myTable" won't have the privs to edit the audit table.How does one capture the identity of the user whose insert statement caused the trigger to fire?  I've tried "current_user" and "system_user" without success when "execute as ..." is used: they each return a value not related to the identity of the user running the original insert.without an "execute as" clause, what prevents a user from adding to or editing the audit table directly?

View 6 Replies View Related

Problem Inserting Rows After Table Creation

Dec 29, 2004

I am using the below SQL to insert a table. The problem is after I run this, I run another script to populate the table (see below). The population script will work if I run it as INSERT INTO ... SELECT TOP 99.9999999 PERCENT ..., but if I put 100 PERCENT, or just use no percent limiter I get the following error: Msg 8624, Internal SQL Server error.

It is weird b/c once something is inserted in the table, i can run the populate script without any problems. Any idea as to why this is happening?

Thanks,

Dave

TABLE GENERATION SCRIPT

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbCelebroAds]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbCelebroAds]
GO

CREATE TABLE [dbo].[tbCelebroAds] (
[AdID] [int] IDENTITY (1, 1) NOT NULL ,
[BranchCode] [int] NOT NULL ,
[PropertyID] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AdScheduleCode] [int] NOT NULL ,
[RecAtCentral] [int] NULL ,
[AdRan] [int] NOT NULL ,
[AdStatusID] [int] NOT NULL ,
[PatID] [int] NULL ,
[RunDate] [datetime] NOT NULL ,
[CreationCost] [float] NOT NULL ,
[BillCost] [float] NOT NULL ,
[AddedDate] [smalldatetime] NOT NULL
) ON [PRIMARY]


TABLE POPULATION SCRIPT

INSERT INTO tbCelebroAds (BranchCode, PropertyID, AdScheduleCode, PatID, AdStatusID, AdRan, RunDate, CreationCost, BillCost, AddedDate)
SELECT [TOP 100 PERCENT or no percent limiter doesn't work, TOP 99.9999999 PERCENT does] BranchCode, PropertyID, AdScheduleCode, cp.PatID, 6, 0, CAST(PubDate AS DATETIME), CAST(ISNULL(pat.Cost,0) AS DECIMAL(10,2)), 0, GetDate()
FROM tbCelebroView cv
LEFT JOIN tbCelebroPubs cp ON cv.PublicationName = cp.PublicationName AND cv.AdSectionName = cp.AdSectionName
LEFT JOIN tbPubToAdType pat ON cp.PatID = pat.PatID
WHERE CAST(BranchCode AS nvarchar(20)) + CAST(PropertyID AS varchar(20)) + CAST(AdScheduleCode AS nvarchar(20)) NOT IN
(SELECT CAST(BranchCode AS nvarchar(20)) + CAST(PropertyID AS varchar(20)) + CAST(AdScheduleCode AS nvarchar(20)) FROM tbCelebroAds)

View 4 Replies View Related

Delete Rows From A Table

Jun 20, 2008

Hi,

This is an easy one.

How do delete rows from a table?

I want to delete all the rows from the last month of a table so i have to be able to pass the month parameter to the delete statement.

Help.. plz.. i think this is an easy one

View 17 Replies View Related

Delete Rows From Table

May 16, 2012

In my table I have following current data. I want to delete duplicate records from table.I am trying following SQL statement, So I am getting desired output which is orange color in current data. I want to keep the desired output records and other all should be deleted. But complection is that in current data values in C5 and C6 are different.

select C1,C2,C3,C4,COUNT(*) from table_Delete
group by C1,C2,C3,C4
having COUNT(*) >= 1
ORDER BY COUNT(*) DESC

Current Data:

C1 C2 C3 C4 C5 C6
201 203224 325E 00000999
201 203224 325E 00009999
201 203224 325E 10001999
201 203224 325E 30003999

[code]....

View 3 Replies View Related

Delete Particular Rows In A Table

Jul 25, 2006

hi alli hava eetable that is ,eename sal_______ ________suresh 100000ramsesh 100000raja 100000susjssj 100000dkddkd 100000jfdjfdjfd 100000so i want to delete from second to Fourth row by using Rownumber..Rownumber is not a column of eetable give some example

View 8 Replies View Related

Delete Rows From Table

Jul 20, 2005

I need a script that will delete the first 100000 rows of a table. Is this possible?

View 3 Replies View Related

Inserting Multiple Rows Into One Table (with Calculated Fields)

Nov 29, 2006

Hello all,my first post here...hope it goes well. I'm currently working onstored procedure where I translated some reporting language into T-SQLThe logic:I have a group of tables containing important values for calculation.I run various sum calculations on various fields in order to retrievecost calculations ...etc.1) There is a select statement which gathers all the "records" whichneed calculations.ex: select distinct Office from Offices where OfficeDesignation ='WE' or OfficeDesignation = 'BE...etc.As a result I get a list of lets say 5 offices which need to becalculated!2) A calculation select statement is then run on a loop for each ofthe returned 5 offices (@OfficeName cursor used here!) found above.Anexample can be like this(* note that @WriteOff is a variable storing the result):"select @WriteOff = sum(linecost * (-1))From Invtrans , InventoryWhere ( transtype in ('blah', 'blah' , 'blah' ) )and ( storeloc = @OfficeName )and ( Invtrans.linecost <= 0 )and ( Inventory.location = Invtrans.storeloc )and ( Inventory.itemnum = Invtrans.itemnum )"...etcThis sample statement returns a value and is passed to the variable@WriteOff (for each of the 5 offices mentioned in step 1). This is donearound 9 times for each loop! (9 calculations)3) At the end of each loop (or each office), we do an insert statementto a table in the database.


Quote:

View 9 Replies View Related

SQL 2000: Inserting Multiple Rows Into A Single Table

Jul 20, 2005

To anyone that is able to help....What I am trying to do is this. I have two tables (Orders, andOrderDetails), and my question is on the order details. I would liketo set up a stored procedure that essentially inserts in the orderstable the mail order, and then insert multiple orderdetails within thesame transaction. I also need to do this via SQL 2000. Right now ihave "x" amount of variables for all columns in my orders tables, andall Columns in my Order Details table. I.e. @OColumn1, @OColumn2,@OColumn3, @ODColumn1, @ODColumn2, etc... I would like to create astored procedure to insert into Orders, and have that call anotherstored procedure to insert all the Order details associated with thatorder. The only way I can think of doing it is for the program to passme a string of data per column for order details, and parse the stringvia T-SQL. I would like to get away from the String format, and gowith something else. If possible I would like the application tosubmit a single value per variable multiple times. If I do it this waythough it will be running the entire SP again, and again. Anysuggestions on the best way to solve this would be greatlyappreciated. If anyone can come up with a better way feel free. Myonly requirement is that it be done in SQL.Thank you

View 3 Replies View Related

Delete Rows From One Table That Exist In Another

Aug 26, 2005

Hello,

I need to delete rows from one table that appear in another table.
For example:
newnames has fname, lname, age
oldnames has fname, lname, age

So, I want to be able to remove the rows from newname where there are rows in oldnames. This should be simple enough.

This is what I'm trying to use but it is deleting everything from oldnames.

delete from oldname
select * from newnames

View 9 Replies View Related

How Can I Delete All Rows In A Table At Same Time

Oct 6, 2006

hi,
here i am with a table containing 5columns and 100 rows.i want to delete all rows at the same time. pls suggest me a way on this

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

View 5 Replies View Related

Need To Update Table To Delete Rows

Oct 2, 2007

what is wrong with this statement?

Update services
Delete
where ticketnum = '0'

error message: incorrect syntax near keyword delete

View 6 Replies View Related

How To: Delete All Rows Without Relation To 2nd Table?

Feb 13, 2008

I'm a bit new to SQL, so this might be a very simple question, but I was not able to find an answer by search:

I got two tables, with a 1:n relation between them, seller and article, and I want to delete all sellers that do not offer any articles anymore.

An extract of my SQL to show the tables and the relation:

@"CREATE TABLE dbo.[Seller]
(
[ID] uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT [PK_Seller] PRIMARY KEY,
) ON [PRIMARY]",
@"CREATE TABLE dbo.[Article]
(
[ID] uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT [PK_Article] PRIMARY KEY,
[SellerID] uniqueidentifier NOT NULL,
) ON [PRIMARY]",

@"ALTER TABLE dbo.[Article] ADD CONSTRAINT [FK_ArticleSeller] FOREIGN KEY
(
[SellerID]
) REFERENCES dbo.[Seller]
(
[ID]
)",


Now say I got two sellers (1 & 2), and only one article (1, offered by seller 1). The delete query should delete seller 2 because he is not offering articles anymore.

This probably is dead simple in SQL, something like
DELETE FROM seller WHERE COUNT(article.SellerID=seller.ID)==0
but honestly, my SQL books leave me guessing here, and I found no online ref for cases like this.

Maybe someone can help me here?

Thanks in advance,
Sam

View 3 Replies View Related

Calculating Value From Two Separate Rows In The Same Table And Inserting As New Row In The Same Table

Jan 19, 2008

Code Block


Hi,

I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem:

Table Name: metrics_ladder

id security_id metric_id value
1 3 80 125.45
2 3 81 548.45
3 3 82 145.14
4 3 83 123.32
6 4 80 453.75
7 4 81 234.23
8 4 82 675.42
.
.
.

Table Name: metric_details

id metric_id metric_type_id metric_name
1 80 2 Fiscal Enterprise Value Historic Year 1
2 81 2 Fiscal Enterprise Value Current Fiscal Year
3 82 2 Fiscal Enterprise value Forward Fiscal year 1
4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2
5 101 3 Calendar Enterprise value Historic Year 1
6 102 3 Calendar Enterprise Value Current Fiscal Year
5 103 3 Calendar Enterprise value Forward Year 1
6 104 3 Calendar Enterprise Value Forward Year 2

Table Name: metric_type_details

id metric_type_id metric_type_name
1 1 Raw
2 2 Fiscal
3 3 Calendar
4 4 Calculated

The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder:

id security_id metric_id value
1 3 101 <calculated value>
2 3 102 <calculated value>
3 3 103 <calculated value>
4 3 104 <calculated value>

Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81.

Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82,

metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally

metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use).

As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes:

security_id 80 81 82 83 101 102
----------- -- -- -- -- -- --
3 125.45 548.45 145.14 123.32 <calculated value> <calculated value>
4 ...
.
.
.

which is then unpivoted.

The SQL that achieves this is more or less as follows:

*********
START SQL
*********

declare @calendar_averages table (security_id int, [101] decimal(38,19), [102] decimal(38,19), [103] decimal(38,19), [104] decimal(38,19),etc...)


-- Dummy year variable to make it easier to use MONTH() function
-- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc...
DECLARE @DUMMY_YEAR VARCHAR(4)
SET @DUMMY_YEAR = 1900;

with temp(security_id, metric_id, value)
as
(
select ml.security_id, ml.metric_id, ml.value
from metrics_ladder ml
where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...)
-- only consider securities with fiscal year end not equal to december
and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec')
)
insert into @calendar_averages
select temppivot.security_id
-- Net Income
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103]
,NULL as [104]
-- Share Holders Equity
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107]
,NULL as [108]
-- Capex
-- Sales
-- Accounts payable
etc...
..
..
from temp
pivot
(
sum(value)
for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...)
) as temppivot
inner join company_details cd on temppivot.security_id = cd.security_id

*********
END SQL
*********

The result then needs to be unpivoted and stored in metrics_ladder.

And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET.

Many thanks (if you've read this far!)

M.

View 6 Replies View Related

Inserting Rows Into A Temp Table Created By An Execute Statement

Dec 16, 2007

Below is a simplified table & dataset to illustrate a problem I'm experiencing with a more complex one.





Code Block

create table #test(
recno smallint PRIMARY KEY,
value decimal (18,2))

insert into #test values (1, 3.57)
insert into #test values (2, 5.32)
insert into #test values (3,6.29)
insert into #test values (4, 9.25)
insert into #test values (5, 0.84)

Method 1: I tried inserting rows from #test into a temp table (#table) as follows




Code Block

declare @n as nvarchar(3)
set @n = 1
while @n <= (select count(recno) from #test)
begin
exec ('
insert into ##table
select *,
originalrecno = (select recno from #test where recno = '+@n+')
from #test'
)
set @n = @n + 1
end
However, this yields an error message:




Code Block

Server: Msg 208, Level 16, State 1, Line 2
Invalid object name '##table'.
Note - you can comment out the insert into ##table line above to view the results that I'm trying to put into ##table.


Method 2: next I tried explicitly creating ##table & rerunning the loop containing the insert




Code Block

create table ##table (
recno smallint,
value decimal (18,2),
originalrecno smallint)

declare @n as nvarchar(3)
set @n = 1
while @n <= (select count(recno) from #test)
begin
exec ('
insert into ##table
select *,
originalrecno = (select recno from #test where recno = '+@n+')
from #test'
)
set @n = @n + 1
end
This worked - it inserted the data from the select statements in the loop into ##table.

Question - why won't method 1 work?

View 6 Replies View Related

Stored Procedure - Delete Some Rows (table)

Dec 5, 2006

How to I return the row count as result of deleting some rows from a table? This this script correct below?? Here's my stored procedure script..


Code:

CREATE PROCEDURE dbo.sp_CleanUp_Table_tblSoldRaw
/***************************************************************
** CREATED: 12/04/2006
****************************************************************
** DESCRIPTION: Clean the tblSoldRaw by deleting any data that
** is over 90 days old. We need keep the database
** as small as possible so the performance won't
** suffer on the server's and the client's machine.
**
****************************************************************
** NOTES: Hooked up to BookItOut Data Importer Windows Service
****************************************************************
** MODIFICATIONS:
**
** DATE WHO MODIFICATION DESCRIPTION
** -------------------------------------------------------------
**
***************************************************************/
/***************************************************************
** Uses: tblSoldRaw
***************************************************************/
AS
DELETE FROM tblSoldRaw WHERE TIMESTAMP > (GETDATE() - 90)

RETURN ROWCOUNT

ERROR_HANDLER:

RETURN -1

SET NOCOUNT OFF



Thanks...

View 1 Replies View Related

How To Delete Rows In A Table When No Primary Key Is Defined

Jul 23, 2005

Hello,I want to delete duplicate rows in a table when no primary key isdefined.For eg: If we have table1 with data as below,Suma 23 100Suma 23 100I want to delete a row from this table and retain only one row.I tried deleting self joins and exists operator. But it is deletingboth the rows. I want to retain one row.Can anybody help me out.Thanks in advance,Suma--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict221110.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=760520

View 11 Replies View Related

Delete Table Or Rows Before Adding New Ones?!?! Help Needed...

Apr 28, 2008

Hi there, I have a simple data flow from OLE DB to OLE DB that writes data from a view into a table. Is it possible to delete the table or the rows within the table before wirting the new data???

Thanks in advance!

View 10 Replies View Related

Delete Rows While Table Is Shown On DataGrid

Oct 2, 2007

Hi All,
I have a program which shows some manipulate data from 2 tables on a datagrid.
I'm using another program to delete rows from both tables. (The second program activate from the PC using the RAPI dll).
I have sometimes a problem deleting records.
Actually the Datagrid is not suppose to be shown when the user use the second program, but since I'm not able to determine whether my first program is still running or not, is there an option to force the delete even if other program is using it?
If not, is there a way to stop the first program?

Thanks in advance

View 5 Replies View Related

Delete Child Table Rows Based On Predicates In A Parent Table

Jul 20, 2005

I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim

View 1 Replies View Related

How Do I Delete Selected Rows Without Deleting The Entire Table?

May 26, 2000

Hi Guys!

This SQL statement, though carefully written to delete only selected rows, deletes the entire A_Shift_Times table:

DELETE FROM A_Shift_Times
WHERE EXISTS (
SELECT 1
FROM
Users
WHERE
(A_Shift_Times.time_in >= CONVERT(DATETIME, '2000-05-29 00:00:00', 102)) AND
((Users.User_Password LIKE N'mrr%') OR (Users.User_Password LIKE N'work%')))

What gives? What am I doing wrong here?

View 1 Replies View Related

SQL Server 2008 :: Delete Duplicate Rows From ANY Table

Jul 31, 2015

Is there is 'fairly' simple query to delete duplicate rows from ANY table ?

A script which removes duplicates from a table, when a table_name is supplied.

View 9 Replies View Related







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