SQL 2012 :: Delete Old Data From Multiple Tables Before Inserting New

Jun 9, 2014

My requirement is before inserting new data, we need to delete the old data based on the input in 4 tables.

For this one I need to write 4 individual delete statements.

Is it possible to delete rows from multiple tables in single statement in SQL Server 2012 by using joins?

[URL] .....

I am looking similar, I tried by keeping 4 table aliases in delete statement but it is throwing synatx error

View 2 Replies


Inserting Data In Multiple Tables

Jun 22, 2006

i want to insert data in database(sql server2000). there are some attributes in database which are present in two/three tables and these tables are related. e.g. when i create new user; it's userId and name should be inserted in 2 tables. how can i do it?
i think; it should be implemented through transaction statements but not much aware about these

View 1 Replies View Related

Inserting Data Into Multiple Tables In 1 Transaction

Feb 7, 2007

Hello everyone, My web application uses SQL Server database and I am connecting via standard SqlConnection object and running stored procedures using SqlCommand object. In one of my page, I have data coming from 2 different tables. Now , data from 1 table comes as only single record. But from other table it comes as multiple records. Meaning, data that I read as 1 record, goes to different textbox and dropdown controls on page. Data that comes in multiple rows, I am binding that data with DataGrid. Now, in aspx page data from both table can be updated and on aspx page I only need to provide a single save button. Now, I am not sure how to save/insert/update a single row in 1 table and multiple rows in another table in 1 transaction. I thought of stored procedure. But I don't think its straightforward with stored procedures since table with multiple records, I am not sure how to pass all the records in stored procedure's arguments.Is there any way that I can control whole transaction in ASP .NET? Thanks,Ujjaval       

View 5 Replies View Related

Inserting Data Into Multiple Related Tables

Jun 18, 2007

Hey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other.

What I want to know is:
1)How do you go abouts inserting data into multiple related tables that have constraints on them?

2)Would you use a stored procedure in an instance like this?

3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc

Any help would be greatly appreciated!

Say for instance I have three related tables.




table1 has a one-to-many relationship with table2
table3 has a one-to-one relationship with table2

View 3 Replies View Related

SQL 2012 :: Store Procedure - Inserting Same Data Into Two Tables

Nov 13, 2014

In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB

insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp

And then got an error for code below.

"Multi-part identifier "dbo.orderB.OrderCity" could not be bound

IF dbo.OrderB.OrderCity=''
update dbo.OrderB
set dbo.orderB.OrderCity='London'

View 5 Replies View Related

SQL Server 2012 :: Extracting Data From Row And Inserting Into Separate Columns In Different Tables?

Mar 19, 2015

From my query I am getting results like below in one of the column:

'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'

this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.

I need something that shows Date on one side representing particular amount on the other

For Immediate Due it will be current date and the amount besides it.

how can I achieve this.

View 8 Replies View Related

How To Delete Unwanted Data From Multiple Different Tables With One Single SQL Query?

Mar 18, 2008

This a microsoft SQL 2000 server.
I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID.
Here is my problem:
Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.

I know how to get the user table names from the DB as follows:

FROM dbo.sysobjects
WHERE xtype = 'U' and name like 'Elect_Sub%Daily'

What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".

delete from tableName where Date_Stamp = '2008-3-18'

View 7 Replies View Related

SQL Server 2012 :: Select Large Data From Multiple Tables

May 10, 2014

In a Library Management database we have these tables

1) Document ( DocNo , Doc_type , permalink,inDate)
2)Title(id, DocNo,Main_Title, Other_Title)
3)Author(id , Author_Name , Author_Family,Type--Like:main author , translator ,....)
4)Publisher(id,DocNo , Name,Publisedate,address)
6)Description(id,DocNo,ISBN,description)--one document may have some ISBN,etc

In document table I have 500,000 records.

I want to search a word in these tables ,for example i want to search 'Computer' ,this word may be in subject or title or description and etc. How can I do this with best performance?

View 3 Replies View Related

SQL Server 2012 :: XML Import Into Multiple Tables With Data Translation

Jul 17, 2014

I need to consume a live data feed from a golf tournament. And by consume, I really mean insert (merge) into our own SQL Server database on a regular intervals as a tournament progresses.This site didn't let me upload an XML file, but you can see a sample of the data feed here: URL....

I need to insert this data into 2 tables, Player_Holes and Player_Shots. But while doing the insert, I need to lookup several things such as our player ID match to theirs on an external_id against the players table. The shot types translation, and some other logic about the process overall.

The columns in my player_holes tables are: id, player_id, hole_id, round, shots (this is a total # of strokes) and date_created/date_modified.Shots table is similar: id, player_id, hole_id, round, shot_number, shot_type_id, club, distance, date_created/date_modified.

The only way I know how to do it, is inefficient. I would parse the XML in ColdFusion (please no comments on ColdFusion, that's what we use for webdev), and then loop over it and do inserts for each player, each hole for each round, and the shots would probably be separate for each hole.

It would be so much better and more efficient if I could do it in SQL directly. I've done some research and SQL Server Data Tools looks promising. I've never used it, so would have to learn, but also I'm not sure if that'd work in this application when we want to run is as a scheduled task every few minutes.

View 5 Replies View Related

Inserting In Multiple Tables

Feb 22, 2004

i have 3 tables i want to insert data in, customer,gaurdian,customergaurdian
where the primary key in customer is Cust_id ,in gaurdian GD_id, in customer gaurdfian Cust_id & GD_id which are forgien keys from the opther 2 tables how can i preform the insertion in the three tables taking into consideration that all insertions must be commited and non will be ignored i.e. all the 3 will take place !!!

customer ----link------customergaurdian-----link-----gaurdian

is there some kind of statement or something that gaurantee that insertion will take place in this form?

View 3 Replies View Related

SQL 2012 :: Inserting A Row With Multiple Counts

Sep 17, 2015

I am trying to insert into a row in SQL 2012 a default value for one column and then the results of some select count(*) queries into the other columns.Here is my example:

insert into x64_hourly_activity (Hour,Ingests,Restores,Proxies, Tape_Onsite, Tape_Offsite)
select (select count(*) from x64_Ingest_metrics_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59),
(select count(*) from x64_restore_metrics_all_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59),
(select count(*) from x64_proxy_metrics_raw_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59),
(select count(*) from x64_tape_metrics_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59 and Storage_Loc = 'XYZ'),
(select count(*) from x64_tape_metrics_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59 and Storage_loc = 'JKL')

What is the statement I use to insert 12AM as a value for Hour?

View 1 Replies View Related

Delete Multiple Records From Multiple Tables

Jan 20, 2006

What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.)

delete dbo.tblcase

where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseclient

where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseinformation

where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaselawyer

where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

delete dbo.tblcaseprosecutor

where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')

View 1 Replies View Related

Inserting Values Into Multiple Tables

Jan 25, 2008

Hi All,     am new to sql server in my application I am having one Asp.net web page, the user has to enter values for 5 fields(Empno,Empname,salary,deptno,deptname) in that web page and for that i created two tables in sql serverEmp Table- empno(primary key),empname,salaryDept Table- Deptno(primary key),empno(Foreign key ),Deptname(with some check constraint.) After the user enter all the values in the Asp.net web page now i want to store data into database for that i wrote the following stored procedure...create procedure usp_EmpDept  @empno integer,  @empname varchar(15),  @salary money,  @deptno integer,  @deptname varchar(10)  As    Insert into emp(empno,empname,salary)values(@empno,@empname,@salary)  Insert into dept(deptno,Empno,deptname)values(@deptno,@empno,@deptname)   but the problem is whenever some constaint violation for eg. if some check constraint violation in Dept table its inserting the values in the Emp table only, but my requirement is,  It must enter into both tables if there is no constaraint violation otherwise it has to ignore both the tables.  And also please suggest is there any better way to insert values into two tables other than using the stored procedure Any help will be greatly appreciated..Thanks,Vision..   

View 6 Replies View Related

Inserting Rows For Multiple Tables?

Sep 3, 2013

Say for instance I got 2 tables

Subject Table and a Student Table

The Subject Table consist of the following attributes:

Subject_ID [PK], Subject_Name, Course_ID and Course_Name

The Student Table consists of the following attributes:

Subject ID [FK], Students_Name, Students_bday, Students_age, Students_height and Students_weight

How can I use the INSERT function when I would like to add a row with the following details:

Course_Name : Biotechnology
Students_Name : Fred
Students_bday : 01/JAN/1990
Stundets_age : 54

how to use the INSERT function for multiple tables.

View 4 Replies View Related

Inserting Multiple Records In Different Tables

Apr 10, 2007

i wants to insert fields of one form in more than one table using stored procedure with insert query,but i gets error regarding foreign key

View 3 Replies View Related

Inserting From Datas In One Table From Multiple Tables

Dec 5, 2007

Hi guys,I have a problem with my query. What i want to happen is to populate my table EV_NOTIFICATIONDETAILS (Docownerid, CurrentSentDate, LastSentDate, detailsID, GeneralRemarks) using the datas from the two different tables EV_NOTIFICATIONHEADER and EV_DOCDETAILS.I tried to create some a query but im having a error. The problem is once i insert the data from datas to the columns Docownerid, CurrentSentDate, LastSentDate the datas are stored in the database and when i tried to insert the remaining columns TO EV_NOTIFICATIONDETAILS detailsID, GeneralRemarks getting the datas from EV_DOCDETAIL it creates a new set of records in the database. Meaning it doesn't update the records in the table EV_NOTIFICATIONDETAILS but it creates a new set of records.here's my code:INSERT INTO EV_NOTIFICATIONDETAILS (Docownerid, CurrentNoticeSentDate, LastNoticeSentDate) SELECTDocownerid, CurrentSentDate, LastSentDateFROMEV_NOTIFICATIONHEADERINSERT INTO EV_NOTIFICATIONDETAILS (detailsID,GeneralRemarks) SELECTdetailsID,GeneralRemarksFROMEV_DOCDETAIL   Any ideas and suggestions will be greatly appreciated. 

View 2 Replies View Related


Jun 2, 2005

I have a problem there must be an answer to, but I cannot find it anywhere on Google.I have a SQL INSERT statement in an ASP.Net page that  inserts into 3 tables in one transaction.The problem is that 2 of the 3 tables are children of the main table, and I need to get the Parent table's Primary Key [which of course has not been inserted yet] to insert into the Child tables.How do I do this?THanks.Doug

View 1 Replies View Related

DELETE From Multiple Tables

Jan 15, 2007

I am converting all the inline sql in my ASP.NET app to stored procs and I am experiencing some difficultly with my shortest and least complex stored proc. I am new to both ASP.NET and SQL especially using stored procedures so this may have some extremely obvious flaw in it. I am trying to delete a entry that has parts in two tables. I created a stored procedure along the lines of: CREATE PROCEDURE DeleteEntry( @EntryIDINT)ASDELETE FROM firstTable WHERE entry_id = @EntryIDDELETE FROM secondTable WHERE entry_id = @EntryIDI have a remove button on a asp page that executes the stored procedure but it only removes the entry from the second table the first time I click it and then when I click it a second time it removes the entry from the first table.

View 1 Replies View Related

Delete From Multiple Tables

Jun 9, 2008

OK, this is a big problem, with multiple tables, but here goes. Here's my schema:

ID | E_Title

ID | EOG_EventID | EOG_OptionGroupID

ID | OG_Title

ID | O_OptionGroupID

ID | ER_EventID

ID | RO_EventRegistrationID | RO_OptionGroupID

OK, what I'm trying to do is, when I delete an event, I need to delete all the data associated with that event. So here's the thought process.

Delete Event based on ID
Delete all EventRegistration where ER_EventID = Event.ID
Delete all RegistrantOptions where RO_EventRegistrationID = EventRegistration.ID
Delete all EventOptionGroups where EOG_EventID = Event.ID
Delete all OptionGroups where OptionGroups.ID = EOG_OptionGroupID
Delete all Options where O_OptionGroupID = OptionGroups.ID

Sorry that it's so complicated, by I need help. There are foreign key constraints on the tables as well, so you have to work from the bottom back up.

View 2 Replies View Related

How To Delete Multiple Tables At Once

May 28, 2012

In my DB there some tables that I would like to delete. Is there a way to do it NOT one by one?

View 4 Replies View Related

Delete Multiple Tables Like %

Mar 28, 2008

I have 30 or 40 tables in a database called Temp with names like PV_ and i am trying to figure out how to loop through and delete them. I've searched all over the web and forums looking for an answer but can't seem to find it. Does anyone know how to delete tables with the keyword like?

View 4 Replies View Related

Inserting Values From Multiple Tables To Only One Column Of A Seperate Table

Dec 11, 2004

Hi all,

I want to produce some output for Mainframe application. For that I want to insert values from multiple table as source to a single column (huge in size)of a different table (Destination table). There may be same related records in all of the source tables with the primary key. When I export values from the source tables , each related records should be insterted to the destination table's field (multiple entries for each table). Please advise.


View 5 Replies View Related

Inserting To Multiple Tables In SQL Server 2005 That Use Identity Specification

Feb 20, 2007

Hi, I am having a bit of hassle with trying to enter details to multiple tables in SQL Server 2005.
I have four tables, an
Attendance Table (AttendanceID(PK Identity specific), MembershipNo, Date)
Resistance Table (ResistId(PK Identity specific), Weight , Reps, Sets)
Aerobics Tables(AerobicsID(PK Identity specific), MachineID, Intensity, Time)
and a linking table for all of them.... ExerciseMaster(AttendanceID,ResistanceID,AerobicsI D)

My problem is that I can insert data to each specific table by itself using seperate insert statements.....eg....

//insert an attendance record to the attendance table
string userID;

userID = Session["User"].ToString();

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Attendance] ([MembershipNo], [Date]) VALUES (@MembershipNo, @Date)";
pgpDataSource.InsertParameters.Add("MembershipNo", userID);
pgpDataSource.InsertParameters.Add("Date", txtVisitDate.Text);

int RowsAffected = 0;

RowsAffected = pgpDataSource.Insert();

catch (Exception ex)

pgpDataSource = null;

//insert an aerobics record into the aerocibs table

SqlDataSource pgpDataSource = new SqlDataSource();
pgpDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLogin"].ToString();

pgpDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
pgpDataSource.InsertCommand = "INSERT INTO [Aerobics] ([MachineID], [Intensity], [ExerciseTime]) VALUES (@MachineID, @Intensity, @ExerciseTime)";

pgpDataSource.InsertParameters.Add("MachineID", rower.ToString());
pgpDataSource.InsertParameters.Add("Intensity", txtRowerLevel.Text);
pgpDataSource.InsertParameters.Add("ExerciseTime", txtRowerTime.Text);

int RowsAffected = 0;

RowsAffected = pgpDataSource.Insert();

catch (Exception ex)

pgpDataSource = null;
//same code as above for the resistance table

However, i am facing the problem where this does not populate the link table(ExerciseMaster) with any information as i am unable to write the relevant IDs into the table that have been auto generated by SQL Server for each of the subTables.
I have read several forums where they recommend using something called @@IDENTITY but i have no idea how or where to use this in order to fill my exercise table...
Any help would be so much appreciated.... Also, hopefully what i have said all makes sense and someone will be able to help me...oh and one more thing...this is an ASP.NET page coding in C#

View 8 Replies View Related

Delete Multiple Tables At A Time

May 31, 2008

Hi: I have 3 tables namely:
1  Category(CategoryID(int), CategoryName(varchar),
2  SubCategory( CategoryID(int),SubcategoryID(int),SubcategoryName)
3 Productlist (ProductID(int),ProductName(varchar),CategoryID(int), CategoryName(varchar),SubcategoryID(int),SubcategoryName(varchar))
how to delete correspoding subcategories of category from  SubCategory,Productlist  tables using triggers
Ex: Category :TV   Subcategory:ColorTV,Plasma,LCD...Plz Send me the query....

View 1 Replies View Related

How To Delete Rows From Multiple Tables In SQL

Oct 23, 2007

I have an SQL 2000 server. I have multiple tables in the db that have a row with a time stamp of '10-23-2007'. What I am trying to do is delete these specific rows because they don't belong.
So I need to query the db for table names that are like 'elect_Sub%' and then execute a query on those tables that would delete the row with the time_stamp '10-23-2007'. I know that I have to use the db schema to get the table names, but I need help in writing the sql script that will automatically scroll through the tables.


View 6 Replies View Related

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

Feb 5, 2007


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

SQL Server 2012 :: Get Empty Data Set For Inserting Data?

Nov 11, 2014

I have 2 tables in my database.

one is Race table and 2nd one is Age Range.

I want to write a query where I can see all races and age range as column.


ID, RaceName



There is no connection between this two table. I need to display result like below.

Race 17-20 21-30 31-40





How do i get this kind of empty data set so that I can fill it out in front end or any better solution. The age range will be displayed as many row as they have. It's not static. Above is just an example.

View 1 Replies View Related

SQL 2012 :: Generate Stored Procedures For Select / Insert / Update / Delete On Certain Tables?

Apr 3, 2015

Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?

View 4 Replies View Related

Inserting Data Into Tables Having Triggers...

Jul 20, 2005

When you import data using DTS into a table that has triggers - do the triggers fire off if there are triggers for on insert or on after insert?Thanks,--Micah

View 1 Replies View Related

Problem Inserting Data Into Onw Of My Tables

May 14, 2007

i created an SSIS package to look for data in a table on another system and compare it with the table i have in system 2, if there are any changes to system 1 then it must apply them to system 2

My Problem:

It scans through my table and finds all the correct records to insert, but when it has to insert the new data into System 2 table i keep on getting violation and contraint errors because of the primary key and foreign key constraints. how can i get around this, or does anyone have an alternative solution for me.

Total Specification Requirements:

i have 2 systems both running SQL Server. everytime data gets updates in system 1, the same change needs to be made in system 2. The databases and tables are identical.

Any Help would be graetlty appreciated

Kind Regards

Carel Greaves

View 4 Replies View Related

SQL 2012 :: Scramble Data While Inserting Onto Secondary Table

Sep 24, 2014

Do we have any way to insert,update,delete data from one table and update the changes onto second table. Also, while updating records into second table, can the data be encrypted.

I tried using view and it can insert, update, delete without any issues. But if i tried to encrypt any fields after inserting data into view, I am unable to do it.

For ex:

Table A:

Expecting output: by scrambling Lname FILED

Table B - vIEW TABLE


Tried below methods

CREATE Tableb_vw ON TableB
Instead of Insert
update TableA
set Lname = (
--UserName = 'User' + substring(convert(varchar(32), UsersTrID), 1, 8)

What I would like to get:

1. Can we update base tables and encrypt second table data while inserting or updating data
2. If not supported using base tables, can we do using views to encrypt view data [Some fields]

View 0 Replies View Related

SQL 2012 :: Inserting Data Into Temp Table Using 2 While Loop

Apr 21, 2015

I want to insert data (month&year) from 2014 till now - into temp table using 2 while loop.

drop table #loop
create table #loop
seq int identity(1,1),
[month] smallint,
[Year] smallint

For some reason I cant not get 2015 data .

View 4 Replies View Related

Inserting Data To Tables Belonging To Different Databases

Jul 23, 2005

Hi all,we are developing an internal application that involves "Timesheets","Support" and "Project Management".Imagine that there are 3 different databases for the above scenario,under SQL Server 2000.My task is to create one or a few table triggers forINSERT/UPDATE/DELETE operations. For example:- if a row is added in Table A of "Timesheets" database, then Table Bof "Project Management" needs to be updated.The concept is clear i think. The question is how we do the above.Note that I am a new progremmer to SQL Server (I have been dealingwith Oracle so far), and I don't know how we programmatically connectto different database within a trigger, how do we check thepriviledges etc.Can someone help me?ThanksChristos Andronicou

View 1 Replies View Related

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