Truncate Table Via Stored Procedure

Oct 4, 2013

I have never created a stored procedure before so Not even sure what i am trying to do is even viable

Aim – Create a stored procedure which truncates a table at a certain time of the day

Table name Dan.Stg_Jitter_Opp want to truncate table at 11am

View 7 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt

process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)

* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.

View 2 Replies View Related

SQL 2012 :: Using Stored Procedure To Truncate Tables?

Jun 30, 2014

I was just looking at an SSIS package someone else set up and I went into one of the execute SQL tasks and it is calling a stored procedure to truncate a table. There are a lot of places that tables are truncated within this SSIS package. But each one the 'database', 'owner', and 'table' name are hard coded.

Like this: exec dbo.uspTruncateTable 'dbname', 'dbo', 'tblname'

Of course there are different names, just changed for an example.

Here is the code in the Stored Procedure.

CREATE procedure [dbo].[uspTruncateTable]
@nDatabase varchar(255),
@nOwner varchar(255),
@ntable varchar(255)
as
declare @sqlString nvarchar(max)
set @sqlString = 'Truncate Table ' + @nDatabase + '.' + @nOwner + '.' + @ntable
exec (@sqlString)

This just seems like an unneeded step, just code the 'truncate table dbname.dbo.tblname'.

View 5 Replies View Related

SQL Server 2005 JDBC Driver 1.1 Truncate Output Parameter From A Stored Procedure To 4000 Char.

Oct 26, 2006

I have a procedure that uses varchar(max) as output parameter, when I tried to retrieve

the result of calling the procedure, the result is truncated to 4000 character. Is this a driver bug?

Any workaround?



Here is the pseudo code:

create Procedure foo(@output varchar(max))

{

set @foo = 'string that has more than 4000 characters...';

return;

}



Java code:

CallableStatement cs = connection.prepareCall("{call foo ?}");

cs.registerOutputParameter(1, Types.longvarchar); // also tried Types.CLOB.

cs.execute();

String result = cs.getString(1); // The result is truncated to 4000 char.

-- Also tried

CLOB clob = cs.getClob(1);

long len = clob.length(); // The result is 4000.

Thanks,

Eric Wang





View 3 Replies View Related

What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure

Aug 29, 2007

which is more efficient...which takes less memory...how is the memory allocation done for both the types.

View 1 Replies View Related

Stored Procedure To Copy Table 1 To Table 2 Appending The Data To Table 2.

Jan 26, 2006

Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.

View 2 Replies View Related

Transact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?

Nov 5, 2015

Can I invoke stored procedure stored inside from a user defined table column?

View 5 Replies View Related

Using Table Name Stored In A Scalar Variable In Stored Procedure Problem

Mar 27, 2006

Hello to all!

I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:

For example:

declare @countRows int

set @countRows = (select count(*) from MyTable)

The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:

declare @countRows int

set @countRows = (select count(*) from @myTableName)

I also tried this:

declare @sqlQuery varchar(100)

set @sqlQuery = 'select count(*) from ' + @myTableName

set @countRows = exec(@sqlQuery)

But it looks like function exec() does not return any value...

Any idea how to solve this problem?

Thanx,

Ziga

View 3 Replies View Related

Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?

Jul 19, 2012

I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).

Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32

Simple example:
    declare @tTable(col1 int)
    insert into @tTable(col1) values (1)
    select * from @tTable

Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.

But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.

Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.

View 11 Replies View Related

How Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function

Apr 18, 2007

Here is the scenario,
I have 2 stored procedures, SP1 and SP2

SP1 has the following code:

declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'

EXEC (@tmp)

SP2 has the following code:

SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)

Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:

FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable

-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1

RETURN
END

View 2 Replies View Related

Error Inserting Image Into SQL Server2000 Table From Pocket PC Application Only When Using Stored Procedure In Table Adapter Wiz

Apr 24, 2008

My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.


main.ds.MailsSignature.Clear();

main.ds.MailsSignature.AcceptChanges();


string[] signFiles = Directory.GetFiles(Settings.signDirectory);


foreach (string signFile in signFiles)

{


mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();

mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.

main.ds.MailsSignature.Rows.Add(mailsSignatureRow);

}


mailsSignatureTableAdapter.Update(main.ds.MailsSignature);

But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.


ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )

AS

SET NOCOUNT OFF;

INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);



SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())

For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.

Is there any limitation in CF?

Regards,
Professor Corrie.

View 3 Replies View Related

Stored Procedure That Fetch Each Row Of A Table And Update Rows In Another Table

Jan 31, 2006

I am working with the following two tables:

Category(NewID,OldID)
Link(CategoryID,BusinessID)

All fields are of Integer Type.

I need to write a stored procedure in sql 2000 which works as follows:

Select all the NewID and OldID from the Category Table
(SELECT NewID,OldID FROM Category)

Then for each rows fetched from last query, execute a update query in the Link table.

For Example,

Let @NID be the NewID for each rows and @OID be the OldID for each rows.
Then the query for each row should be..

UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID

Please help me with the code.

Thanks,
anisysnet

View 1 Replies View Related

Update Temp Table With Stored Procedure Joined With Table

Sep 8, 2006

Hello

Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?

insert #MyTempTable

exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3

JOIN dbo.OtherTable...

I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.

I just can't figure it out

Many Thanks!

Worf

View 2 Replies View Related

How To Stored A Stored Procedure's Results Into A Table

Jul 23, 2005

Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Cannot Truncate Table 'Database.dbo.Table' Because It Is Being Referenced By A FOREIGN KEY Constraint..

Aug 23, 2006

Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.

It will run and update with a primary key until I add a foreign key to another database.

Here is my error when running the package when table 2 has a foreign key.

[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

View 3 Replies View Related

Stored Procedure For Insert Data From One Table To Another Table

Apr 25, 2006

Hi,
     I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table.
Overall it is like storing the history of the table updation.
How do i write a stored procedure for this?
Anybody has done this before?
Pls help me.
 

View 1 Replies View Related

Stored Procedure Append All From Table A To Table B

Jan 29, 2013

I've never used a stored procedure before - let alone created one. how to append records from table A to table B.

View 1 Replies View Related

Truncate Table

Jul 4, 2007

i did a Stored Procedure to truncate a table. it contains lots of crap from testing the site. just a simple 1 ALTER PROCEDURE dbo.TruncateUploads

AS
TRUNCATE TABLE Uploads but it says "Cannot truncate table 'Uploads' because it is being referenced by a FOREIGN KEY constraint." then i make NULL all foreign keys and try still cannot. then i did it manually deleting all records then can! but why can't i truncate it? isn't it the same as removing all values plus having the primary keys to start from 0? 

View 5 Replies View Related

How Can I TRUNCATE My Table?

Oct 27, 2007

How can i TRUNCATE my table (removes rows that were produced at testing), so that in the actual running the automated IDNumber for my table start with 1.

View 6 Replies View Related

Truncate Table

Nov 1, 2000

hi, I have two tables members table and orders table.
I have 2 records in members table and zero records in order table.
When I try to truncate members table I get an error message.
cannot truncate table members,because it is being referenced by a FOREIGN KEY constraint.

this what blows my head, There is no records in orders table. so why would the constraint enforced when there is no data in the order table. I created same table in access and I was able to delete a member from the members table. any comments.

thanks
Ahmed

View 4 Replies View Related

Truncate Table

Feb 1, 2005

I have several very large tables and sometimes I need to clean them.
It's known that TRUNCATE TABLE works much faster than DELETE, but impossible to use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint... Is it possible temporary disable (not delete) constraints and after complition of TRUNCATE enable them ?

View 14 Replies View Related

TRUNCATE Table

Feb 10, 2005

Hi,

(From Bol)
"You Cannot Use Truncate Table On A Table Referenced By A Foreign Key
Constraint;"

1) States
Statecode TinyintPrimary Key
Statename Varchar(25)

2) Rivers
RivercodeSmallint Primary Key
RivernameVarchar(40)

3) Riverinstates
StatecodeTinyint,
RivercodeSmallint,
Constraint Pkriverinstates Primary Key(Statecode,Rivercode),
Constraint Fkriverinstates
Foreign Key (Statecode) References States(Statecode),
Foreign Key (Rivercode) References Rivers(Rivercode)

after removing all rows from RiverInStates Table using TRUNCATE and the command completed successfully.

when i tried to remove all rows from Rivers table , i got the following error.

"Server: Msg 4712, Level 16, State 1, Line 76
Cannot truncate table 'rivers' because it is being referenced by a FOREIGN
KEY constraint."

i thought the error may be due to the "Fkriverinstates" FOREIGN KEY CONSTRAINT. so i DROPped the constraint with
ALTER TABLE RiverinStates DROP CONSTRAINT Fkriverinstates

even after DROPping the constraint, im getting the same error
can any one point out where the problem is?

thanks in advance

View 1 Replies View Related

Truncate Table

May 18, 2004

what is the best way to truncate/delete records from a table at a certain time of the day...... i'm sure this is a simple ?, but i'm still learning sql.


thanks,
eddie

View 4 Replies View Related

Truncate All Table's From A Db

Jan 18, 2007

any idea why this is not wroking ?
------------------------------
declare
@TableName varchar

DECLARE
EmptyTables CURSOR FOR

Select Table_Name From Information_Schema.Tables
Where Table_Type = 'Base Table' Order By Table_Type

open EmptyTables

FETCH NEXT FROM
EmptyTables
into
@TableName


WHILE @@FETCH_STATUS = 0
BEGIN
truncate table @TableName

FETCH NEXT FROM
EmptyTables
into
@TableName

END

close EmptyTables
deallocate EmptyTables

View 11 Replies View Related

Truncate Table

Nov 16, 2006



I have a database of 25 GB. I truncated a table which holds around 5 GB of data.

Even after truncation, I see my database size as 25 GB...

Should I run a shrink database after the truncation?

Thanks

Santhosh

View 3 Replies View Related

Getting The Table Name From Stored Procedure

Oct 11, 2007

Hi,
I am having a SP which returns two Rowsets:
   create proc GetSalesDetails as
        select CustName, ProductPurchased from Customers where PurchaseDate > '10/10/2006'
        select ProductName from Products where SalesDate > '10/10/2006'
 
Now in my code when I am filling the Dataset using this SP then it is giving the Table Names as "Table" and "Table1". Is there any way to get the actual table names respectively?
 
Cheers,
Soumen

View 3 Replies View Related

Table Name In A Stored Procedure

Mar 9, 2004

How can be used in a stored procedure the name of a table that was passed as a varchar parameter? The table name must be used in the "from" clause of a "select" statement without calling the "EXECUTE" or "sp_executesql". Is it possible?
Marius G.

View 2 Replies View Related

Truncate Table Permission

Jul 16, 2001

I have somme users who need to truncate some tables owned by DBO. I know that only table owner or DBO can execute TRUNCATE TABLE but I don't want grant DBO permission to those users. Do you have any suggestion ? Thanks a lot.

View 4 Replies View Related

Truncate Table -- Log Backups

Jul 23, 2001

If I have a truncate table statement in a stored proc, will my log backups be compromised due to the nonlogged operations. If so, what are my alternatives in case that I need to restore? Differential backups?

View 1 Replies View Related

I Am Able To Rollback TRUNCATE TABLE ????

Apr 13, 2000

I am doing the following-

begin transaction
truncate table Acounts
select * from Accounts
rollback
select * from Accounts

The first select is returning 0 rows as expected. But after doing the rollback I expected to yet see 0 rows as truncate is not logged so cannot be rolled back. But to my suprise I see that there are rows.

So the rollback is rolling back the truncate table statement. Can someone please explain this????

View 4 Replies View Related

Truncate Table Permission

Mar 17, 2000

I know that only the owner of a table can truncate it.
I want a user to run a procedure that truncates a table. Both the procedure and the table are owned by dbo, however the system says that the user can't truncate the tables because she doesn't have permission. The user has dataread and datawrite global permissions, and I do not want to give her dbo perimssions.
Is there a solution to this?

View 1 Replies View Related

Possible TRUNCATE / BCP Into A Table In One TRANSACTION?

Aug 12, 1998

Hi all,

Is it possible to TRUNCATE a table and BCP data into the same table in one
TRANSACTION?
My problem is that I want to refresh(delete and via BCP append new data) a
table without disturbing running applications. Can I run BCP from a
SQL-script or a stored procedure?

Thank`s
Jonas Dahlqvist
Alfa Laval Automation AB

View 1 Replies View Related

Truncate Table Permissions

Mar 22, 2006

Does anybody know of a way to allow non-administrators to execute the truncate table statement?

I have developers that from time to time need to move data between their databases usinge the DTS wizard. Most of their tables have identity columns and in order retain the identity seed, they need to click on the option to enable identity insert. This option isn't available to non administrators.

View 4 Replies View Related







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