Insert And Delete Statment In One Statment

Jan 18, 2008



Hi all

I have two tables I need to Select the record from the First table and insert them into the second table and delete the record from the first table how can i do that with the SQL Statment?

Thank you in advance .....

Regards,
sms

View 15 Replies


ADVERTISEMENT

Sql Insert Statment

Mar 28, 2008

i have 3 tables  in sql the relation between them is one to one
person ==> employee ==> sales_department_stuff&
another relation ( one to one )between
person(the same as above ) ==> customer 
i want to put person id in employee not repeated in customer becase all (employee and customer is a person ) 
sooooooo
i put this sql statment to try to insert person id in employee then in sales_department_stuff  table to complet his ordersinsert into Person (Person_Name_Ar,Person_Name_En) values ('aaaaa',' ssssssss')select @@identity from Personinsert into Employee values (@@identity,'1') select @@identity from Personinsert into sales_department_Staff values (@@identity,'1')select @@identity from Personinsert into Driver  values (@@identity,'2','2222','1/1/2005','5') /* SET NOCOUNT ON */
      RETURN
 
 the first 3 statment is run and success
but underline stetment return error that
he can insert null value in id field ,,,,,,,,,,,,,,,,
he can compile @@identity in these statment ??????????????
plz help me
 
  
 

View 7 Replies View Related

INSERT STATMENT

May 1, 2007

Hi All,

I have a table "Person" that has two columns "FirstName" and "LastName".
How can I insert multiple rows into this table using the INSERT stsmt.
I want to use just one insert statement.

Thanks in advance,
Vishal S

View 4 Replies View Related

Subqueries In Insert Statment

Feb 26, 2008

Hello, How can I do something like:
Insert Into Displayed(snpshot_id, user_id, user_domain, ddisplay, iWidth, iHeight, disp_size, disp_format, watermark, frc_update, creditcost)
Values ((Select snpsht_id from SiteIndex Where user_domain like '%domain'), 1000, (Select domain_id from UserDomains Where user_domain like 'domain'), GetDate(), 480, 360, 2, 1, 1, 0, 3)
Im getting an error:
Subqueries are not allowed in this context. Only scalar expressions are allowed.
 
Thanks!

View 4 Replies View Related

Insert Statment For A One-to-one Relationship

Apr 29, 2008

I have two tables that have a one-to-one relationship. The reason they are two table is that one table has user data, whereas the other table has security information. The security information will be in a different security zone.

How do I do an insert into the two tables so that I do not get an integrity error?

View 3 Replies View Related

Insert Statment Not Working

Nov 10, 2004

I know this is a sin in dbforums to jump forums to ask other forum questions, but I just had to do it.....

it's actually related to foxpro dbf tables. Here is the case:

I am opening this existing DBF file in Microsoft Visual Fox Pro 6.0 .

In the command window, select, update and even including delete statements works .

What is getting on my nerves is the "insert" statement. It always prompts "syntax error". But the @#$@#$ error message just didn't help much.

The funny thing is, if I use the "Append Mode" and add data directly via the GUI, it works!.

Here is the insert statement, just a very simple one:

<code> INSERT INTO ASSET (ACCNO) values '2000/141'</code>




You can reply me here , or go to the real thread to reply me if you can help out..thanks

http://www.dbforums.com/t1058508.html

View 2 Replies View Related

Question On Insert Statment

May 6, 2008

Hello,
I have a table (SQL Server 2000) which contains data and I want to create insert statements based on this data. The reason for this is to create a .sql file to run this against another database to insert the same data. I know I can do an import but was trying to do this via a .sql script. Is there any way to generate these insert statement automatically using some SQL tool?
Thanks

View 4 Replies View Related

Passing A Querystring To A Insert Statment.

Feb 1, 2008

Hi, I'm having problems passing a querystring from the datanavigateurlformatstring to be used in a insert sp on a webform.  Please can someone take a look at my code and point me in the right direction.   At the moment I'm gettingthis error messageCompiler Error Message: BC30451: Name 'userid' is not declared.  but in the url i can see the querystring I've passed from the previous page.
Thanks in advance Dave
 1 Protected Sub Execute_Clicked(ByVal sender As Object, ByVal e As EventArgs)
2
3 Dim objConn As New System.Data.SqlClient.SqlConnection()
4 objConn.ConnectionString = "My connection string"
5 objConn.Open()
6 Dim objCmd As New System.Data.SqlClient.SqlCommand("test_insert", objConn)
7 objCmd.CommandType = System.Data.CommandType.StoredProcedure
8
9
10 Dim puserid As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@userid", System.Data.SqlDbType.Int)
11 Dim pdate As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@date", System.Data.SqlDbType.DateTime)
12 Dim papplicants As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@applicants", System.Data.SqlDbType.Int)
13 Dim pclients As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@clients", System.Data.SqlDbType.Int)
14 Dim pcontacts As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@contacts", System.Data.SqlDbType.Int)
15
16
17
18
19
20 puserid.Direction = System.Data.ParameterDirection.Input
21 puserid.Value = Convert.ToInt32(userid.QueryString)
22 pdate.Direction = System.Data.ParameterDirection.Input
23 pdate.Value = Convert.ToDateTime([date].Text)
24 papplicants.Direction = System.Data.ParameterDirection.Input
25 papplicants.Value = Convert.ToInt16(applicants.Text)
26 pclients.Direction = System.Data.ParameterDirection.Input
27 pclients.Value = Convert.ToInt16(clients.Text)
28 pcontacts.Direction = System.Data.ParameterDirection.Input
29 pcontacts.Value = Convert.ToInt16(contacts.Text)
30
31
32
33 command.ExecuteNonQuery()
34
35
36 objConn.Close()
37
38 End Sub
 

View 7 Replies View Related

Table JOIN With INSERT Statment

May 11, 2008

How to perform a table JOIN with the INSERT SQL statment, just show a join example between 2 tables will do.

View 2 Replies View Related

Increase The Speed Of Insert Statment

Mar 2, 2008

Hi all

i'm using sqlserver 2005

this statment take 1:30 min to execute

****insert into temotable (select key from table1)

if i used a select statment alone it takes 4 sec

but with insert statment it take 1:30min

by the way i put indexes on the table1

plz how i can increase the speed of insert statment.

thanks in advance

View 4 Replies View Related

Table JOIN With INSERT Statment

May 12, 2008

How to perform a table JOIN with the INSERT SQL statment, just show a join example between 2 tables will do.

View 3 Replies View Related

Function Call In Insert Statment

Jul 3, 2006

Hi



i m trying to call a function in insert statment

Insert Into (value, value1)

Value(@value, dbo.function(@value1)

dbo.function returns a value,

when i test the function in querry builder all goes fine.

In my program i become a error

"Parameterized Query '' ' expects parameter @value1 , which was not supplied."

I m using visual studio , tableadapter.update function to insert datarecords in db



thx for help

View 3 Replies View Related

Insert Into Statment Kills My Database Connection?

Sep 13, 2006

Hi

Simple statment is giving me a logout on the server

INSERT INTO Angebot (Nummer,Variante,Bearbeiter,Datum,Geld,Kurs,Language,Flag,AngebotStatus,TStatus)
VALUES (90360,3,'Admin',DEFAULT,'CPI_BE-D',1,'CPI_AG-1',4,NULL,59)

The statment works fine on production, however i have played a backup from production to my laptop to test something and as soon as I add a record to the database my connection to the database is droped (the data is not added to the table).

the error message I get is double, however I execute the statement only once.

Msg 0, Level 11, State 0, Line 0
Für den aktuellen Befehl ist ein schwerwiegender Fehler aufgetreten. Löschen Sie eventuelle Ergebnisse.

Msg 0, Level 20, State 0, Line 0
Für den aktuellen Befehl ist ein schwerwiegender Fehler aufgetreten. Löschen Sie eventuelle Ergebnisse.

Sugestions anyone?

PS: the message in English is:



Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

View 1 Replies View Related

How To Replace Single Quote In INSERT Or UPDATE Statment Using SqlDatSource?

Jan 15, 2008

I can think of ways to resolve this issue, but I am wondering if there is a standard practice or some setting that is used to ensure that text containing a single quote, such as "Bob's house", is passed correctly to the database when using a SqlDataSource with all of the default behavior.
For example, I have a FormView setup with some text fields and a SqlDataSource that is used to do the insert. There is no code in the form currently. It works fine unless I put in text with a single quote, which of course messes up the insert or update. What is the best way to deal with this?
Thank you
 

View 10 Replies View Related

Help With Sql Statment?

Jul 11, 2006

I cant find the error in this sql statment. What I want it to do is return everything from book, locations.locationname, and author.fullname. I want to to only return the first 10 rows. This will be used in paging, so, eventually it will be first 10, than 11-20, etc. Heres what I have, without the row limitSELECT RowNum, book.*, locations.LocationName, author.fullname FROM (SELECT book.*, locations.LocationName, author.fullname ROW_NUMBER() OVER(ORDER BY book.id) as RowNum FROM book INNER JOIN Author ON book.AuthorID = Author.ID OR book.AuthorID2 = Author.ID OR book.AuthorID3 = Author.ID OR book.AuthorID4 = Author.ID OR book.AuthorID5 = Author.ID INNER JOIN Locations ON book.LocationID = Locations.ID WHERE (Author.FullName LIKE '%' + @Search + '%') OR  (Author.FirstName LIKE '%' + @Search + '%') OR (Author.LastName LIKE '%' + @Search + '%')) as BookInfo

View 6 Replies View Related

SQL Not Statment Help

Jul 11, 2007

I have two tables. One for videos and one for a "block list"Videos : VideoID UserID VideoURL VideoTitle etc. VideoBlockList :VideoID UserIDI have a datalist to show the videos but i want for the datalist to miss out any videos that a user can not see.So if the block list has "VideoID" = 2 and UserID = 1 if user 1 does a search then the search will skip out the video 2.how is this done? i tryed to do it using a specific / custom SQL statment but it errored cos the NOT value conflicted with the search... any ideas? thanks in advance si! 

View 19 Replies View Related

Sql Statment

Nov 13, 2007

Select @ID  = top 1 ID From Contents Where Contents.UserID=@UserID And Contents.Status=4  AND Contents.InEdit=1
why it dosn't get back the ID vlaue but when i remove the top1 one its work well why
 

View 5 Replies View Related

Need Help With Sql Statment

Apr 14, 2008

Hi I have two tablesTABLE 1 named problemas with the field N_problem (numeric)Tabe 2 named  resolvidos with the field Resol (numeric)
How can i select all the records from table 1 who are not in Tabe2 ?
Thank you
mario

View 2 Replies View Related

IF Statment

May 16, 2002

Does anyone know how to write a statement in SQL Server that is similiar to Microsoft Access's IIF function. Im not quite sure how the syntax works in a SQL Server IF statement. Thanks!

View 1 Replies View Related

If Statment

Apr 2, 2007

need help with if statment in a stored procedure
here is what i have and it does not work

Code:


CREATE PROCEDURE Get_ckcompany
@cknum int,
@company varchar

AS

if (@cknum is not null) and (@company is null)
select *
from PMTK_tbl
where Company = @company

else if (@compnay is not null) and (cknum is null)
select *
from PMTK_tbl
where check_Num = @cknum
else
select *
from PMTK_tbl
where Check_Num = @cknum and Company = @company
end if

GO

View 2 Replies View Related

Need Help With An Sql Statment

May 1, 2008

I have a database used for a point of sale system
it has a main table with the total amount of a check - the tip
the tip is save in a different table that holds payments the issue is i need to make a statement that finds checks based of the total with the tips added. the table that holds tips can have more then one tip because you can have multiple payments on a check.

The statement i have now is like this

select jc.total + sum(jp.tip) as total from jc innerjoin jp on key
where total <= @max and total >= @min
group by jc.total

but this statement uses total before the tip is added. I am not sure how else to do this any help would be great

View 4 Replies View Related

SQL Statment

Jun 1, 2006

select zsong.song, zsong.trk
from zsong, zfmt
where zfmt.upc='13117' AND zfmt.muzenbr=zsong.muzenbr


That staement works, but if the UPC code is repeated twice it returns the results twice (i want to only read it once)...

how do i make it only return the first result or the last one or whatever (since they all reference the same thing)

View 4 Replies View Related

Use Statment

Jun 9, 2008

Posted - 06/09/2008 : 11:10:47
--------------------------------------------------------------------------------

trying to run the following script.

use 001
select *
from imitmidx_sql

Get incorrect syntax near 001

If I change the use 001 to Data_58 it works fine. Do I need special syntax when the database name is 001??

View 1 Replies View Related

SQL Statment

Feb 18, 2007

Hello All,

I imported a excel file from SSIS and created a table called Lockbox.
To avoid the user from having to change the excel file -it is being imported as is.
I only need 4 fields: [Contract ID] , [Check Number], [Owner ID], [Site ID]

The table I need to import to Transaction has Diffrent Column Names -ex-CustomerID, ResortID.
The columns are in diffrent order.
And I need to add more information into them like UserID = 'Hwells', Trantype = 'MF'
and convert to a diffrent data type [Site ID] to text.

Is their a sql statment that can do this?

SQL2005

Thanks for your time

View 2 Replies View Related

Sql Statment

Aug 17, 2007

hi,

I Have a following SP running on sql 2005:-

DECLARE @MONTHNO int
DECLARE @DB CHAR (8)
DECLARE @CR CHAR (8)
SET @MONTHNO = 2
SET @DB = 'DB_'+CONVERT(NCHAR(2),@MONTHNO)
SET @CR = 'CR_'+CONVERT(NCHAR(2),@MONTHNO)
SELECT (acctno),@CR from bmaster

. The table has a 12 field with cr_1 ... to 12

. the sql can’t understand the @cr the column papers "with No column name'

View 2 Replies View Related

SQL Statment By Asp.net

Oct 8, 2006

is there any chance to use the follwoing script to add new record to the database

"

rs.addnew

rs.("name")="Scot"

rs.update

"

insted of the old fashion " insert into ...."





View 1 Replies View Related

SELECT Statment

Mar 20, 2007

Hello, Is there an SELECT statement to just return the last 100 row in my tables?  I have about 500 rows in my tables and I only need the info on the last 100 rows.
 Thanks
Steve

View 4 Replies View Related

A SELECT Statment, Please Help Me

Oct 2, 2007

hello,
i have a page "Picture.aspx?PictureID=4"
i have a FormView witch shows details about that picture and uses a stored procedure with input parameter the "@PictureID" token from query string
the Pictures table has among other rows "PictureID", "UserID" - uniqueidentifier - from witch user the picture belongs to
i have a second FormView on the same page, witch should show "other pictures from the same user" and uses a Stored Procedure
how should i write that stored procedure...frist to take the UserID from the picture with PictureID=4, then to pass it as input parameter and select the pictures witch has as owner the user with that UserID, and if can be done, to avoid showing the PictureID=4 again
a solution should be to add at querry the UserID too, but i want to avoid that
any sugestion is welcomed, please help me
THANKS

View 5 Replies View Related

Optimize SQL Statment

Mar 9, 2008

Hello, I have three sql select statments I would like to combine into one.  I have created a statment that works but I am not sure if it is a good solution performance wise.  Is there a better way to run this query?
Thanks Very Much!!
 if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate)
Begin
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate
End
else
if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where nextupdate < GetDate() Order By importance desc)
begin
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where nextupdate < GetDate() Order By importance desc
end
else
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Order By nextupdate 

View 1 Replies View Related

Using Date In SQL Statment

Jun 3, 2006

Hello All;
I am using an sql statement to pass only the year using DatePart function, but i receive the following error.

curYear = DatePart(DateInterval.Year, Now)
Dim Date1 = "'" & curYear & "/01/01'"
Dim Date2 = "'" & curYear & "/31/12'"
Dim strAll As String = "SELECT * FROM Boxes WHERE EntryDate BETWEEN Convert(DateTime, " & Date1 & ") And CONVERT(DATETIME," & Date2 & ")"
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
What is the cause, please?

View 2 Replies View Related

Help On Sql Select Statment

Apr 20, 2007

Okay here is the deal. I need to take all data from tbl 1 and match it to data in 3 other tbls. I need to have it return everything back to me even if it is null....
IE tbl 1 I match the invoice_num to tbl2 site_id and then tbl2 marekt to tbl3 market. however even if tbl1 invoice_num dose not match tb2 site_id I still need to have it retun to a null value to the site_id. Here is what I have so far. This will return everything where the invoice_num and site_id match.

Code:


Select distinct t1.ID,t1.Deposit_date,t1.Ref_Num,t1.Company,t1.check_num,t1.Check_Date,t1.Check_Date,t1.Check_Total, t1.Individual_PMT,t1.Invoice_Num,t1.Invoice_Desc,
t2.site_id,t3.CompanyCode,t3.CostCenter
From( PMTK_tbl as t1
Left Join Leaseinfo as t2 on t2.site_id = t1.Invoice_Num)
inner Join CostCenters AS t3
on t2.market = t3.market and t2.market_region = t3.RegionCode

View 4 Replies View Related

Using Case Statment

Apr 7, 2008

Hi There i have a query that uses conditional Select statment my query is:
Select A,B,C case
when '1' then 'one'
when '2' then 'two'
end
from table

what if i want to include addional condition in the case condition like C case
when '1' and A is null then 'C is one and A is null'

Thank you :D

View 3 Replies View Related

Help With Select Statment

May 23, 2008

whats wrong with this statment?

declare @alpha as nvarchar(50)
set @alpha = select top 1 monthyear from dbo.Performance_Indicators_Rolling order by recordkey

I get an error

Incorrect syntax near the keyword 'select'.

Can anyone please help?

View 3 Replies View Related







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