Is there any method to get the T-SQL command/ statement using DML triggers?.
i.e. While we insert a record using INSERT Statement, is there any possible way to get the T-SQL Statement using DML trigger for AFTER INSERT.
I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)
View 7 Replies View RelatedI have this script which generates droptable tablename.
select  top 10000 'drop table '+name, * from sys.tables (nolock) where name like '%4[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' order by create_date
I would like to generate 'GO' after each drop statement.
I have many new tables for which i need to write Insert,Update and delete triggers manually. Is there any way to generate triggers script which takes table name as a input parameter and print/generate trigger's script?
View 1 Replies View RelatedHi i have a page in which stock can be allocated, there are two boxes which have a product serial number start range and a product serial number end range, when these boxes are filled the "allocate" button is then clicked and the product will then be allocated the serial numbers.
What i want to happen is that when the start and end ranges have been entered into the text boxes it will fail if any number within the range has already been allocated previously. E.g
Start Range
End Range
So lets say in the start range text box 15 is entered and in the end range 25 is entered, however 18 has already been allocated previously, this will then bring up a message saying please select another range;
I have done the following so far;private void ValidateRange()
{
//String strSql;SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["strConnectionString"]);
String strSql = "SELECT SqlCommand dbCommand4 = new SqlCommand(strSql, conn);
dbCommand4.Parameters.Add("@Start_RangeID", txt_Start_Range.Text);dbCommand4.Parameters.Add("@End_RangeID", txt_End_Range.Text);
dbCommand4.Connection = conn;
conn.Open();SqlDataReader myDataReader = dbCommand4.ExecuteReader();
myDataReader.Read();if (txt_Start_Range.Text == Convert.ToString(myDataReader["Serial_No"]))
{lblRange.Text = "Please enter a different range a portion of the selected values have already been allocated";
//Response.Redirect("Selectedfilm.aspx");
}
else
{Allocate(true);
}
conn.Close();
}
My problem is constructing the select statement, thanks.
I am trying to generate XML path from a SQL Server Table. Table Structure and sample data as belowÂ
CREATE TABLE #OfferTransaction
( [OfferLoanAmount1] INT
,[offferid1ProgramName] VARCHAR(100)
,[Offer1LenderName] VARCHAR(100)
,[offerid1LenderNMLSID] INT
[code]....
what changes do I need in my query so that the XML looks like the one above ( DESIRED XML). Is it possible via query changes?
I have table A and B. A has column ID,A1,A2,A3,A4,A5 columns. B has ID,B1,B2, A1 columns. A table has a trigger. I defined the below trigger.Â
Solution 1
ALTER TRIGGER [dbo].[Tri_A]
ON [dbo].[A]
for UPDATE
AS
BEGIN
  UPDATE B
 SET B.A1= i.A1
 FROM inserted i
    INNER JOIN BÂ
    ON B.ID = i.ID
END;
GO
If I change the above solution 1 trigger to solution 2.Can I improve the trigger performance dramatically? I mean only A.A1 is changed then update B.A1. So when the other columns is changed, the update will not be required.
Solution 2
ALTER TRIGGER [dbo].[Tri_A]
ON [dbo].[A]
for UPDATE
AS
BEGIN
IF ( UPDATE (A1) )
  UPDATE B
 SET B.A1= i.A1
 FROM inserted i
    INNER JOIN BÂ
    ON B.ID = i.ID
END;
GO
I would like to use sequences and triggers to update table identity field with int value from sequence via before insert trigger. I'm searching on google for a few days and there are no same or identical article about this subject.
Is there any sample how to create table with column Id, Name, Comment and sequence (for generate int numbers for Int field in table) and trigger which will fired before insert and check is inserted Id is NULL and update this field from sequence or nothing if id is set up.
I am trying to generate a script to drop 15 tables which have dependencies across the database. Is there a script that could generate the drop stataments based on the child table first , parent table last strategy?
View 3 Replies View RelatedI am using SQL 2012 SE. I have 2 databases say A and B with same structure and relationships. There are 65 tables in each database. A is already replicating data to database C for 35 tables. Now I need to move data from A to B which is greater than getdate()-1 everyday  for all the tables and once the move is done I need to delete this data from A. And samething the next day. Since this is for 65 tables its challenging to identify the insert order. Once the insert order is identified the delete order will be the reverse of it.
Is there a tool or any SP that could generate the insert order script? The generate scripts data only is generating the entire data and these databases are almost 400GB. So I thought of generating a schema only script and then create an empty database with it and then generate data only to identify the order of insert. But it wont generate anything since there is no data.
I am wanting to fire-off an email with the failed jobs anytime they are deposited into a table. Â My syntax fires off an email even when the table does not contain data, it just sends a blank email. Â this will only generate an email if teh table contains data? Â
if exists (Select from FailedJobs)
exec msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseMail'
@recipients = 'asdfasdfsdf@aafas.com'
@from_address = 'asdfasdfacasca@cc.com'
@query = 'Select * from failedjobs'
@subject = 'List Of Failed Jobs'
@attach_query_result_as_file = 1;
Hi guys
I am wondering if there is any way of setting the trigger to execute once per individual row that is updated as appose to once per statement. Thus a statement like this would trigger twice not just once:
Update Order
Set Name = 'test'
Where OrderId In (1,2)
If this is not possible do the Deleted and Inserted tables have the records for all the items that were updated in the statement?
The reason being that i need to store the before snapshot and after snapshot of the updated data in an audit database along with an event that ties the two audit records together. This event is a business logic event, which is stored in the main database and records which user made the change, what other records the change is related to and impacts, etc, etc. Thus after the above update is complete, I would have 4 audit records, 2 being pre shots and 2 being post shots, along with 2 event records. Where each event record ties one of the pairs of pre and post audit records together.
Now if the tables hold all the records, my first thought was to simply do a mass insert. But as the event ties the pairs of pre and post records together I can€™t just do a mass inset into the database. Thus when I first thought about doing this row by row triggers is great for me as it keeps the logic fairly simple. But if i only get one bit of the apple (as it were) does anyone have any suggestions as to about I could go about. Is my only option to run through a cursor on the inner join of the pre and the post data and on each loop create my event and then insert the pre and post shots??
Any help is appreciated.
Anthony
I am trying to achieve the below problem statement, however I am getting stuck at the looping part.
EX: We have three columns
First Name |Â Last Name | Mothers Name
I want to generate the username field using above columns, so lets consider, I have following data
First Name |Â Last Name | Mothers Name
a                   b                 cdef
a                   b                 cdfg
a                   b                 cdfj
Expected Usernames:
1: a.b
2: a.b.c
3. a.b.cd
Basically, it has to be FirstName.LastName.(incremental letters from MothersName until the name becomes unique)
I need to generate the week ranges like this format :
Here from date and to date would be picked up from the table but just to make you understand i have hard coded it but this is the real date which is falling inside the table.Â
Note : Week should be generated from Monday to Sunday within desired date range
I have 5 different tables with same structure. Each table has an account column and another column count. I am trying to generate a report based of the columns in each table. Account is same in all tables with same account numbers but count will be different.Â
Eg:
Table 1 has Account Count
Table 2 has Account Count
Table 3 has Account Count
Table 4 has Account Count
Table 5 has Account Count
I want output as:Â
Account Count Account Count Account Count Account Count Account CountÂ
I have to insert YEARÂ Â WEEKNUMBERÂ Â STARTDATEÂ Â ENDDATE values to a datatable (sayweekrange), if I pass 2011 as year.
Week range starts at 2011-03-28 to 2011-04-03 (because in my database 2010 last week range ends with 2011-03-27) like this I have to generate for 52 weeks.
I want to write a stored procedure, that takes only year as parameter. with this year I have to generate week ranges and insert into my table as shown above.
How can I do this ?
I want generating Valid date ranges from any list of dates.
The List of Dates could be generated from the below TSQL -Â
SELECT '2015-06-02' [Date] UNION ALL
SELECT '2015-06-13' UNION ALL
SELECT '2015-06-14' UNION ALL
SELECT '2015-06-15' UNION ALL
SELECT '2015-06-16' UNION ALL
SELECT '2015-06-22' UNION ALL
SELECT '2015-06-23' UNION ALL
SELECT '2015-06-24'
And the expected output should look like -Â
SELECT '2015-06-02' FromDate, '2015-06-02' ToDate UNION ALL
SELECT '2015-06-13' FromDate, '2015-06-16' ToDate UNION ALL
SELECT '2015-06-22' FromDate, '2015-06-24' ToDate
I have a dataset which is like:
Month, Day, Location, TotalSales
Jan    1     A 100Â
Jan    1     B 200Â
Jan     14    A 120Â
Feb     2     A 130Â
Mar     5     B 150Â
I want to transform the dataset using sql query into the following format:
Month, Day, LocationATotalSales, LocationBTotalSales, TotalSales
Jan    1       100            200              300
Jan    14      120             0               120
Feb    2       130             0               130
Mar    5         0           150              150
How can I write it in SQL? I have a table that column  A   has two values of  Product  and Cost...Where value of A is Product, I have to add 6 more rows, and add column b with values from 1 to 7 for each rows ( counter)Where value of A is Cost, I have to add 4 more rows, and add column b with values from 1 to 5 for each rows ( counter).
View 3 Replies View RelatedWe have triggers for Insert, Update and Delete, so that we can check which record is going to inserted, updated or Deleted.
Is there any way so that we could check "From which values the data has been selected ?"
Thnks
Here is the example
state
-----
NY
NJ
CA
NY
NC
NJ
CA
IL
CA
Can we generate a result like this with select statement
state No
----- ---
CA 1
CA 2
CA 3
NJ 1
NJ 2
NY 1
NY 2
IL 1
NC 1
I have an application which uses ODBC to insert, update, delete records in an MSSQL DB. The application uses prepared statements and keeps the odbc statement handles to execute the same statement multiple times.
This application works fine until triggers are introduced. The triggers are pretty simple and take the inserted, updated record and insert this information into another table.
If we change the application to not re-use the prepared statement handle, by closing the statement handle after each execute, it also works fine. (However, this results in bad performance.)
The error message is: "[ODBC SQL Server Driver]Connection is busy with results for another hstmt". If anyone has any information on this, I would greatly appreciate the help!!!
Thank you.
Christine
My problem is one of my query is returing party_codes . now i also want a column which returns serial numbers along with it . the serial numbers are not stored anywhere. they should be autogenerated. My query is combining two different databases and its using union in it so i can not use count in it . Is there any other way i can acheive . for eg Now my query output is party_code ---------- R06048 R06600 R06791 (3 row(s) affected) I want it like party_code serial number ---------- ------------- R06048 1 R06600 2 R06791 3 (3 row(s) affected)
The serial number column should be auto generated in the select statement it self Is there any system rowid i can use . Please suggest
Good day,
I have seen in Enterprise manager there is a toll that can script the all tables in a database, but nothing that can generate the insert statements for all the rows in each table in a secified database.
Does any one know of a application, plug in, script that can generate the insert statments for all the tables in a database?
Please someone help, this is driving me insane.
Thanks
I'm trying to generate an update statement based off select statement results. A very basic example,
SELECT ListID FROM DListing WHERE Status = 2Results return 3 rows.
1234
2345
3456
How can I take those results and turn them into WHERE criteria for UPDATE statement?
Generated UPDATE statement should look like this.
UPDATE DListing SET Status = 1 WHERE ListID IN (1234,2345,3456)
I have started by creating a temp table to hold my SELECT results, but I don't know how to get those results into format for IN condition. Right now I get 3 UPDATE statements with 1 ListID in each IN condition.
CREATE TABLE #TempStatusUpdate(ListID INT)
INSERT INTO #TempStatusUpdate
SELECT ListID FROM DListing WHERE Status = 2
SELECT 'UPDATE DListing SET Status = 1 WHERE ListID IN (' + CONVERT(VARCHAR(30),ListID) + ') AND Status = 2'
DROP TABLE #TempStatusUpdate
I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?
View 7 Replies View RelatedI am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
 UPDATE TABLE1
  SET TABLE1.FIELD2 = 1
 ELSE
  UPDATE TABLE2
  SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
Hi all,I have a problem trying to generate the u/m list of customer.I am trying to generate a list of customer whoes last commence date isjan 04 to current.It is part of a billing system which the customer come in and pay fortheir season parking in carpark. They can pay for various periodshortest being 1 week.so i will have customer paying for 1 week, 1 month, 2 months or even 1year. Everytime a customer come in to pay, a new line at the incvoicewill be generated.My DB structure is as followedCustomer Table-Cust_Acc_No ----> (Primary Key)-Customer Name-Customer AddressInvoiceCust_acc_no-- Link to customer tableInvoice_no -- primary keyinvoice detailsinvoice_no -- link to invoice tablecommence_dateexpiry_dateamount_paidif i do aselect * from customer a, invoice b, invoice_details cwhere a.cust_acc_no=b.cust_acc_no andb.invoice_no=c.invoice_no andc.commence_date>1/1/04it doesnt works as it will showjohn, 1/1/04 - 31/1/04john, 1/2/04 - 29/1/04i do not want repetitive customer number just the latest commencedate.can anyone help?thanks
View 1 Replies View RelatedI would like to wrap the following code in a function and reuse it. Â I use this code in many triggers.
DECLARE @Action as char(1);
SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
THEN 'U' Â -- Set Action to Updated.
WHEN EXISTS(SELECT * FROM INSERTED)
THEN 'I' Â -- Set Action to Insert.
WHEN EXISTS(SELECT * FROM DELETED)
THEN 'D' Â -- Set Action to Deleted.
ELSE NULL -- Skip. It may have been a "failed delete". Â Â
END)
Is it possible to write a function and pass the INSERTED and DELETED logical tables to it?
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies View RelatedHi,
I just have a Dataset with my tables and thats it
I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
Please help if anyone has a solution
Thanks
Hi,
I have a table with data.I want to generate a SQL script with many insert statements.the data can import to same table when I run the script .
I remember I can do the work with a stored procedure,but I do not have a idea how to write the stored procedure.
I use SQL Server 2005 now.
Please give me a idea about this.Thanks
Mark
Mark
I need an SQL statement that will allow me to strip out duplicate records based upon a data condition.
Table
PPN_I CPN_I Qty_Required ECN_02
1. ItemA ItemB 1 4506
2. ItemA ItemB 1 3209
3. ItemA ItemB 1 901
4. ItemA ItemD 1 null
5. ItemA ItemD 0 null
6. ItemA ItemF 3 5609
7. ItemA ItemG 1 null
8. ItemB ItemA 1 1725
Okay - I have two conditions I need to enforce
1. A duplicate record is defined as a record where PPN_I and CPN_I have identical ID's. My new DB doesn't allow duplicate records. In order to 'strip' out the unwanted records - I need to enforce a couple of data conditions:
I delete both records for this case only - if qty required =1 and a duplicate record has qty required = 0 then both get deleted. In the above example - records 4 & 5 both get deleted. In all other duplicate record scenarios - I want to keep the record with the highest ECN_02 number. So, records 2 & 3 get deleted because they are duplicate (same PPN_I and CPN_I id's) and their ECN_02 is less than the highest value (4506). If no duplicate record exists - no action taken (records 6,7,8).
So, records 4 & 5 - both get deleted. Records 1,2 & 3 become one record (highest ECN_02) records 1 stays - records 2 & 3 deleted.
8 records become 4.
Any thoughts? How do I 'loop thru the table and identify duplicate records and then enforce some kind of rule?
Any assistance would be appreciated,
Thanks in advance.