How To Update If Exists Else Insert In One SQL Statement

Insert If Not Exists Else Update
Hello,

it's me again :)
I've got a - what I think - simple question.
There is table A with Col1,Col2,Col3 and Table B with Col1,Col2,Col3

I want all rows from B in A. If a row already exist in A, then update all columns, else just insert the row.
Can someone please help me with a small syntax.
Thank you!

View Replies !   View Related

---

Running An UPDATE Statement Only If A Column Exists
I'm trying to write a script that would only update a column if it exists.

This is what I tried first:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield')
BEGIN
    UPDATE dbo.Enrollment SET nosuchfield='666'
END

And got the following error:

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'nosuchfield'.

I'm curious why MS-SQL would do syntax checking in this case. I've used this type of check with ALTER TABLE ADD COLUMN commands before and it worked perfectly fine.

The only way I can think of to get around this is with:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield')
BEGIN
    declare @sql nvarchar(100)
    SET @sql = N'UPDATE dbo.Enrollment SET nosuchfield=''666'''
    execute sp_executesql  @sql
END

which looks a bit awkward. Is there a better way to accomplish this?

View Replies !   View Related

---

IF Exists UPDATE ELSE INSERT Problem
Hi,

I have a 'Products' table (with: 'uid' and 'CatName' columns) and 'ProductCategory' table (with: 'uid', 'ProductID', 'CategoryID' columns).

I got stored procedure below to update or insert new row to 'ProductCategory' table whenever 'Products' table has been updated or new products has been added to it.

Update part works just fine but when new row has been added to 'Products' this storedProc dosn't insert it into 'ProductCategory' table, it does that only when 'ProductCategory' table is empty, I'm afraid it's because first column 'uid' in 'ProductCategory' table is an Identity column... I’m not sure how should I go about that problem. This is my stored procedure:

DECLARE @CatNo INT, @CatName varchar(10)
SET @CatNo = 2
SET @CatName = 'bracket'

IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @CatName )
BEGIN
UPDATE ProductCategory SET CategoryID = @CatNo
FROM Products WHERE Products.CatName = @CatName and ProductCategory.ProductID = Products.uid
END
ELSE
BEGIN
INSERT INTO ProductCategory ( ProductID, CategoryID)
SELECT uid, @CatNo FROM Products
WHERE Products.CatName = @CatName
END

SET @CatNo = 3
SET @CatName = 'cable'

IF exists (SELECT ProductID from ProductCategory, Products where ProductCategory.ProductID = Products.uid and Products.CatName = @CatName )
BEGIN
UPDATE ProductCategory SET CategoryID = @CatNo
FROM Products WHERE Products.CatName = @CatName and ProductCategory.ProductID = Products.uid
END
ELSE
BEGIN
INSERT INTO ProductCategory ( ProductID, CategoryID)
SELECT uid, @CatNo FROM Products
WHERE Products.CatName = @CatName
END
(... Goes for another 37 categories)



Thank you for help.

Kooba

View Replies !   View Related

---

Record Exists Insert Or Update
 

I had implemented as in the link to insert or update
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

 
What i want to know is... how can i assume there are no duplicate records.
I used Distinct keyword and queried it showed me all are distint but  some where i find some duplicates just don't know why i am having when i look at the data both are exactly same...
 
Please let me know how can i fix it.
Urgent..
 
Thanks

View Replies !   View Related

---

Checking To See If A Record Exists And If So Update Else Insert
I've decided to post this as a sticky given the frequency this question is asked.

For those of you wishing to build a package that determines if a source row exists in the destination and if so update it else insert it, this link is for you.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks Jamie!

If you want to do a similar concept to Jamie's blog post above, but with the Konesan's Checksum Transformation to quickly compare MANY fields, you can visit here:
http://www.ssistalk.com/2007/03/09/ssis-using-a-checksum-to-determine-if-a-row-has-changed/

Phil

View Replies !   View Related

---

Update Table If Record Exists Else Insert ?
Is there a way to structure a query to update an existing table record if it already exists, otherwise insert a new record into that table?

View Replies !   View Related

---

RETURN Of &"IF NOT EXISTS&" INSERT Statement
Hi @ all

I'm using MSSQL and PHP.
I've got the following sql statement:

$msquery = IF NOT EXISTS (SELECT SerienNr FROM tbl_Auftrag a WHERE a.SerienNr='PC8') INSERT INTO tbl_Auftrag (BMS_AuftragsNr, SerienNr, AuftraggNr, Zieltermin, Kd_Name) VALUES ('455476567','PC8','1','2006-3-2','Fritz')

The Statement itself works fine, but i've got a problem getting a return value whether the insert has succeed, or not. :confused:
mssql_query() always returns true if there occured no error in the statement. But i need to know if the insert procedded or not.
I tried:

$result = mssql_query($msquery);
$succeed = mssql_rows_affected ($result);

And:

$result = mssql_query($msquery);
$succeed = mssql_num_rows($result);

to get the rows, affected by the statement, but both return:

supplied argument is not a valid MS SQL-Link resource

Anyone an idea?

View Replies !   View Related

---

SQL Statement, INSERT/UPDATE
What I am attempting to do here is check to see if a record exists in a table, if so, I will update some fields, if not, I will insert a new record into the table.  This is what I have so far, I was hoping someone could let me know if any of these elements are unnecessary. SELECT [IP] FROM [Table]if (strIP == @IP){ UPDATE [Table] SET [Column = value++]} else{ INSERT INTO [Table] (IP) VALUES (@IP) } I obviously left out parameters and whatnot, I am mainly concerned with the logic here.  My apologies if this is something simple. 

View Replies !   View Related

---

Update And/or Insert Statement
I am having an issue with this SQL Statement i am trying to write. i want to insert the values (Test Facility) from CCID table INTO CCFD table where CCID.id = CCFD.id  this is what i have.UPDATE CCFD.id SET TEST_FACILITY = (SELECT CCID.id.TEST_FACILITYFROM CCID.idWHERE CCID.id = CCFD.id)orINSERT INTO CCFD.id(Test_Facility)SELECT TEST_FACILITYFROM CCID.idWHERE (CCFD.id.INDEX_ID = CCID.id.INDEX_ID) thanks in advanced

View Replies !   View Related

---

I Don't Suppose BULK UPDATE Exists?... Like BULK INSERT?
I have to update a field within a table of 60 records or so. Each record has a different field value. it's type varchar. i was given an excel file with the field values and was thinking of a bulk update like bulk insert, but i don't recall that it's possible that way.

Is the only way to create a table, bulk insert, then merge the two tables together with UPDATE?

Just wanted to see if there was an easier way to do it, otherwise i'll take the latter route. Thanks!

View Replies !   View Related

---

Using CASE With INSERT,UPDATE Statement
Is it possible to use CASE statement with INSERT /UPDATE statement?this is what i am trying to do
i have a table like this
Field1 Field2 Field3  FieldType1 FieldType2 FieldType3
1-when there is no data for a given combination of  Field2 and  Field3,i need to do "insert".i.e the very first time and only time
2-Second time,when there is already a row created for a given combination of Field2 and  Field3,i would do the update onwards from there on.
At a time value can be present for only one of the FieldType1,FieldType2,FieldType3) for insert or update
I am passing a parameter to the stored procedure,which needs to be evaluated and wud determine,which field out of (FieldType1,FieldType2,FieldType3)has a value for insert or update .this is what i am trying to do in a stored procedure
CREATE PROCEDURE dbo.StoredProcedure
 ( @intField1 int, @intField2 int, @intField3 int,  @intFieldValue int , @evalFieldName varchar(4)  )So i am trying something like
CaseWHEN @evalFieldName ="Fld1" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,@intFieldValue,cast(null as int) fld2 ,cast(null as int) fld3)
CaseWHEN @evalFieldName ="Fld2" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,@intFieldValue,cast(null as int) fld3)
CaseWHEN @evalFieldName ="Fld3" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,cast(null as int) fld2,@intFieldValue)
END
similar trend needs to be followed for UPDATE as well..obiviousely its not working,gives me synatax error at case,when,then everywher.so can someone suggest me the alternative way?..i am trying to avoid writing  stored procedure to insert/update for each individual fields..thanks a lot
 

View Replies !   View Related

---

Have Insert Statement, Need Equivalent Update.
Using ms sql 2000I have 2 tables.I have a table which has information regarding a computer scan. Eachrecord in this table has a column called MAC which is the unique ID foreach Scan. The table in question holds the various scan results ofevery scan from different computers. I have an insert statement thatworks however I am having troulbe getting and update statement out ofit, not sure if I'm using the correct method to insert and thats why orif I'm just missing something. Anyway the scan results is stored as anXML document(@iTree) so I have a temp table that holds the releventinfo from that. Here is my Insert statement for the temporary table.INSERT INTO #tempSELECT * FROM openxml(@iTree,'ComputerScan/scans/scan/scanattributes/scanattribute', 1)WITH(ID nvarchar(50) './@ID',ParentID nvarchar(50) './@ParentID',Name nvarchar(50) './@Name',scanattribute nvarchar(50) '.')Now here is the insert statement for the table I am having troublewith.INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,ScanID, AttributeValue, DateCreated, LastModified)SELECT @MAC, #temp.ID, #temp.ParentID,tblScanAttribute.ScanAttributeID, tblScan.ScanID,#temp.scanattribute, DateCreated = getdate(),LastModified =getdate()FROM tblScan, tblScanAttribute JOIN #temp ONtblScanAttribute.Name =#temp.NameIf there is a way to do this without the temporary table that would begreat, but I haven't figured a way around it yet, if anyone has anyideas that would be great, thanks.

View Replies !   View Related

---

Single Statement For Insert Or Update
 In VB6 using MDAC 2.8 I could do a single select statement that would act as either an Insert or an update. Is there a way to do this in ADO.net?
My old VB6 code
Dim dbData As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim strParm As String
Dim strCusNo As String
'
strParm = "Provider=SQLOLEDB; Data Source=SQL2000; Initial Catalog=DATA_01; User ID=UserName; Password=password"
dbData.Open strParm
'
strParm = "Select CusNo from CusFil Where CusNo = '" & strCusNo & "'"
rs1.Open strParm, dbData, adOpenStatic, adLockOptimistic, adCmdText
If rs1.BOF And rs1.EOF Then
    rs1.AddNew
Else

End If
With rs1
    !CusNo = strCusNo
    .Update
End With
rs1.Close
'
Set rs1 = Nothing
dbData.Close
Set dbData = Nothing

Is there an ADO.Net equivalent?

thanks,

View Replies !   View Related

---

SQL UPDATE/INSERT Statement Speed
 

Hello,
 
Server hardware: Intel core 2 duo, 2Gb ram, SATA 1500Gb, SQL Server 2005
 
I have big table (~ from 50000 to 500000 rows) :



Code Snippet
CREATE TABLE [dbo].[CommonPointValues](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[pointID] [bigint] NOT NULL,
[pointValue] [numeric](10, 2) NOT NULL,
[qualityID] [int] NOT NULL,
[dateFrom] [datetime] NULL,
[dateTo] [datetime] NULL,
CONSTRAINT [PK_PointValues] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
 
 


UPDATE statement:



Code SnippetUPDATE dbo.CommonPointValues SET dateTo = @last_update WHERE ID = @lastValID
 
 


2000 rows update takes about 1 sec.
 
INSERT statement:
 



Code Snippet
INSERT INTO dbo.CommonPointValues (pointID, pointValue, qualityID, dateFrom, dateTo )
VALUES (@point_id, @value_new, @quality_new, @last_update, @last_update )
 
 




Speed with INSERT is similar like with UPDATE.
 
I need about 10000 updates per 1 second and 10000 inserts per 1 second or together 5000 inserts and 5000 updates per 1 sec.
 
Should I update hardware or try some improvements to table structure.

View Replies !   View Related

---

Update Statement, Then Insert What Wasn't Available To Be Updated.
Using MS SQL 2000I have a stored procedure that processes an XML file generated from anAudit program. The XML looks somewhat like this:<ComputerScan><scanheader><ScanDate>somedate&time</ScanDate><UniqueID>MAC address</UniqueID></scanheader><computer><ComputerName>RyanPC</ComputerName></computer><scans><scan ID = "1.0" Section= "Basic Overview"><scanattributes><scanattribute ID="1.0.0.0" ParentID=""Name="NetworkDomian">MSHOMe</scanattribute>scanattribute ID = "1.0.0.0.0" ParentID="1.0.0.0", etc etc....This is the Update portion of the sproc....CREATE PROCEDURE csTest.StoredProcedure1 (@doc ntext)ASDECLARE @iTree intDECLARE @assetid intDECLARE @scanid intDECLARE @MAC nvarchar(50)CREATE TABLE #temp (ID nvarchar(50), ParentID nvarchar(50), Namenvarchar(50), scanattribute nvarchar(50))/* SET NOCOUNT ON */EXEC sp_xml_preparedocument @iTree OUTPUT, @docINSERT INTO #tempSELECT * FROM openxml(@iTree,'ComputerScan/scans/scan/scanattributes/scanattribute', 1)WITH(ID nvarchar(50) './@ID',ParentID nvarchar(50) './@ParentID',Name nvarchar(50) './@Name',scanattribute nvarchar(50) '.')SET @MAC = (select UniqueID from openxml(@iTree, 'ComputerScan',1)with(UniqueID nvarchar(30) 'scanheader/UniqueID'))IF EXISTS(select MAC from tblAsset where MAC = @MAC)BEGINUPDATE tblAsset set DatelastScanned = (select ScanDate fromopenxml(@iTree, 'ComputerScan', 1)with(ScanDate smalldatetime'scanheader/ScanDate')),LastModified = getdate() where MAC =@MACUPDATE tblScan set ScanDate = (select ScanDate fromopenxml(@iTree,'ComputerScan', 1)with(ScanDate smalldatetime 'scanheader/ScanDate')),LastModified = getdate() where MAC =@MACUPDATE tblScanDetail set GUIID = #temp.ID, GUIParentID =#temp.ParentID, AttributeValue = #temp.scanattribute, LastModified =getdate()FROM tblScanDetail INNER JOIN #tempON (tblScanDetail.GUIID = #temp.ID ANDtblScanDetail.GUIParentID =#temp.ParentID AND tblScanDetail.AttributeValue = #temp.scanattribute)WHERE MAC = @MAC!!!!!!!!!!!!!!!!!! THIS IS WHERE IT SCREWS UP, THIS NEXT INSERTSTATEMENT IS SUPPOSE TO HANDLE attributes THAT WERE NOT IN THE PREVIOUSSCAN SO CAN NOT BE UDPATED BECAUSE THEY DON'T EXISTYET!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID,ScanAttributeID,ScanID, AttributeValue, DateCreated, LastModified)SELECT @MAC, b.ID, b.ParentID,tblScanAttribute.ScanAttributeID,@scanid, b.scanattribute, DateCreated = getdate(), LastModified =getdate()FROM tblScanDetail LEFT OUTER JOIN #temp a ON(tblScanDetail.GUIID =a.ID AND tblScanDetail.GUIParentID = a.ParentID ANDtblScanDetail.AttributeValue = a.scanattribute), tblScanAttribute JOIN#temp b ON tblScanAttribute.Name = b.NameWHERE (tblScanDetail.GUIID IS NULL ANDtblScanDetail.GUIParentID ISNULL AND tblScanDetail.AttributeValue IS NULL)ENDELSEBEGINHere are a few table defintions to maybe help out a little too...if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAssetGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblAsset]GOCREATE TABLE [dbo].[tblAsset] ([AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,[AssetName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL,[AssetTypeID] [int] NULL ,[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[DatelastScanned] [smalldatetime] NULL ,[NextScanDate] [smalldatetime] NULL ,[DateCreated] [smalldatetime] NULL ,[LastModified] [smalldatetime] NULL ,[Deleted] [bit] NULL) ON [PRIMARY]GO-----------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblScan]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[tblScan]GOCREATE TABLE [dbo].[tblScan] ([ScanID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,[AssetID] [int] NULL ,[ScanDate] [smalldatetime] NULL ,[AssetName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL,[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[DateCreated] [smalldatetime] NULL ,[LastModified] [smalldatetime] NULL ,[Deleted] [bit] NOT NULL) ON [PRIMARY]GO----------------------------if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblScanDetail]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblScanDetail]GOCREATE TABLE [dbo].[tblScanDetail] ([ScanDetailID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOTNULL ,[ScanID] [int] NULL ,[ScanAttributeID] [int] NULL ,[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GUIID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL,[GUIParentID] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_ASNULL ,[AttributeValue] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CI_ASNULL ,[DateCreated] [smalldatetime] NULL ,[LastModified] [smalldatetime] NULL ,[Deleted] [bit] NOT NULL) ON [PRIMARY]GO------------------------------------------------------------My problem is that Insert statement that follows the update intotblScanDetail, for some reason it just seems to insert everything twiceif the update is performed. Not sure what I did wrong but any helpwould be appreciated. Thanks in advance.

View Replies !   View Related

---

INSERT, UPDATE, And DELETE Statement Checkbox Inactive
Hi  AllgI have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advancehttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPGhttp://img139.a.com/img.php?image=28285_2_122_937lo.JPG   

View Replies !   View Related

---

Can A Single Insert Statement Will Update Multiple Tables
Hai Guys:

I have a situation where i need to insert a value called StudentID to several tables with one insert statement. Can you please inform me the SQL statement to acheive the above mentioned task. (by the way iam using SQL Server 2000, i dont need triggers / procedures as they are too tricky and hard to use).

Thanks / Regards,
Dotnet Geek.

View Replies !   View Related

---

My Update Statement Isn't Working But Select And Insert Are. What's Wrong?
 

here is my code:
 

Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())

cn.Open()

Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()

adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products
set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" &
textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)

cn.Close()

Response.Redirect("database.aspx")
 
it posts and the page loads but the data is still the same in my datagrid.  what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?

View Replies !   View Related

---

Problem With Data Refreshing After Insert Or Update Statement
Dear All,
 
Im using VS2008, visual basic and SQL Server express.
 
General questions please: Is it necessary, to issue some sort of command, to 'commit' data to a SQL express database after a INSERT or UPDATE sql command?
 
I'm getting strange behavior where the data is not refreshed unless i exit my app and re-enter. In other words, i can run a sql command , the data is apparantly saved (because i get no errors) then if i refresh a data set or do a sql select query the data that i expect to return is not there.
 
Im fairly new to SQL express (and SQL server generally) so i dont know if its my coding or i need to switch some 'feature'
on/off or not.
 
I hope thats clear
 
Also, could someone point me to documentation that explains each parameter in the connection string
 
Many Thanks
 
Chris Anderson
 
 
My code is:
 

ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=C:myfile.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

Connection = New SqlConnection

Connection.ConnectionString = ConnectionString

Connection.Open()
'''''''''''''''''the above code is done at the start of my application
 
 
'''''''this code below called during application many times
 
 
dim sql as string = "my sql string here"

Dim cmd As SqlCommand = Nothing

cmd = New SqlCommand(sql, Connection)

 
 
 
 

Try

cmd.ExecuteNonQuery()

Catch err As SqlException

MessageBox.Show(err.Message.ToString())

MessageBox.Show(sql, "SQL ERROR: ", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

 

cmd.Dispose()

View Replies !   View Related

---

Select Statement (Advvance Button Insert, Update, Deltete
Hello All
I have had asked the same question in another post, i didnt get answer to it i might have had asked it wrongfully
 
Soo the question is:   When creating a SQLDataSource in the wizard  you get to the pont where you select the option . It says that by using this datasource you can select to update delete and insert. So my question is if i am creating a select statement to reterieve the data from the Table, then what does it do it do if my intention is to only reterie the data. Or what is the other way that it could be helpful to me ??
 
thanks all I hope it make sence, if not I wrill write another post to bring step by step info into it.
 

View Replies !   View Related

---

GridView Based On SQLServerDataSource Using A Select Union Statement, Impacts On Update And Insert?
I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):

SELECT    FirstName,    LastNameFROM    MasterUNION ALLSELECT    FirstName,    LastNameFROM    CustomORDER BY    LastName,    FirstName
I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom).  Any ideas if or how this can be done?  Specifically, I want the Custom table to be editable, but not the Master table.  Any examples or ideas would be very much appreciated!
Thanks,
Randy

View Replies !   View Related

---

If Exists Statement Problem...
Can someone give me a hand with this?

Ok I have a table called allstocks, with a primary key of ticker, I also have a todaysstocks table with a primary key of ticker.

Todaysstock table updates the allstocks table with this statement,

insert into allstocks (exchange,transdate,ticker,[opened date],[closed date],[over/under]) select exchange,[todays date],ticker,[opened date], [closed date],[over/under] from todaysstocks

this works fine, unless the ticker already exists. How do I write the statement, that if the ticker already exists, then update the rest of the fields with the new info, or delete the row and recreate it?

ANy help would be appreciated. Thanks

View Replies !   View Related

---

How To Set A Variable In An If Exists Statement
 

Hello,
 
I would like to set a variable within my if exists statement, however SQL is throwing and error stating:

 
Incorrect syntax near '='.
 
If I remove the if exists, the query runs fine. Is there a reason why this is not working the way I have it and what suggestions can I use to accomplish what I am trying to do, which is store the ID into the permissionID variable
 
Here is my code block:



Code Snippet
 
declare @permissionID int;
 
if exists(select @permissionID = Id from Permission

where [Description] = 'SettlementReport')
 
 




Thanks,
Flea#

View Replies !   View Related

---

VS2005 - Using Detailsview To Update, Insert And Delete Rows From SQL 2005 Database. Delete And Insert Work But Update Does Not - No Errors Returned
  Using VS 2005 DetailsView to insert, delete, and update rows in SQL 2005 database.  insert and delete work but update does not.  I recieve no errors and the detailsView comes back unchanged (as well as table row is unchanged).   I am trying to use as little code behind as possible. However I do have ItemUpdating routines that seem to work (i.e Checking table for new login duplicates and encrypting passwords).  The following is the source code generated by VS2005:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="frmDbRegionMgrNew.aspx.vb" Inherits="frmDbRegionMgrNew" Title="Region Manager DB Update" Theme="detailsVeiwTheme" %><%@ Import Namespace="System.Data" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"><title>Untitled Page</title> </head><body><form id="form1" runat="server"><div><asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Style="z-index: 101; left: 14px; position: absolute; top: 10px" Width="199px">Data Base Maintenance </asp:Label><asp:HyperLink ID="HyperLink1" runat="server" BackColor="ActiveBorder" BorderColor="ActiveBorder"BorderStyle="Outset" Font-Bold="True" Font-Size="X-Small" ForeColor="#004000"Height="31px" NavigateUrl="DataBaseMaint.aspx" Style="z-index: 133; left: 524px;position: absolute; top: 7px" Width="96px">DB Main Menu</asp:HyperLink><br /><br /><br /><table style="width: 654px"><tr><td style="width: 120px"><asp:Label ID="Label2" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Text="Regional Manager" Width="189px"></asp:Label></td><td style="width: 100px"></td><td style="width: 203px"></td></tr><tr><td style="width: 120px" valign="top"><asp:Label ID="Label3" runat="server" Font-Bold="True" Font-Size="Small" ForeColor="#004000"Style="z-index: 128; left: 2px; position: absolute; top: 115px" Width="128px">Select Greenhouse -></asp:Label></td><td style="width: 100px" valign="top"><asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"DataTextField="Name" DataValueField="GrnHseID"></asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>"SelectCommand="SELECT [GrnHseID], [Name] FROM [Greenhouse]"></asp:SqlDataSource></td><td style="width: 203px">&nbsp;<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"DataKeyNames="RegionMgrID" DataSourceID="SqlDataSource2" Height="50px" Width="125px"><Fields><asp:TemplateField HeaderText="RegionMgrID" InsertVisible="False" SortExpression="RegionMgrID"><EditItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Eval("RegionMgrID") %>'></asp:Label></EditItemTemplate><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("RegionMgrID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="GrnHseID" SortExpression="GrnHseID"><EditItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Eval("GrnHseID") %>'></asp:Label></EditItemTemplate><InsertItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:TextBox></InsertItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundField DataField="DispLvl" HeaderText="DispLvl" SortExpression="DispLvl" /><asp:BoundField DataField="FName" HeaderText="FName" SortExpression="FName" /><asp:BoundField DataField="Minit" HeaderText="Minit" SortExpression="Minit" /><asp:BoundField DataField="LName" HeaderText="LName" SortExpression="LName" /><asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /><asp:BoundField DataField="Ext" HeaderText="Ext" SortExpression="Ext" /><asp:BoundField DataField="Cell" HeaderText="Cell" SortExpression="Cell" /><asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" /><asp:BoundField DataField="Login" HeaderText="Login" SortExpression="Login" /><asp:BoundField DataField="Password" HeaderText="Password" SortExpression="Password" /><asp:BoundField DataField="AccessLvl" HeaderText="AccessLvl" SortExpression="AccessLvl" /><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /></Fields></asp:DetailsView><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>" DeleteCommand="DELETE FROM [RegionMgr] WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"InsertCommand="INSERT INTO [RegionMgr] ([GrnHseID], [DispLvl], [FName], [Minit], [LName], [Phone], [Ext], [Cell], , [Login], [Password], [AccessLvl]) VALUES (@GrnHseID, @DispLvl, @FName, @Minit, @LName, @Phone, @Ext, @Cell, @Email, @Login, @Password, @AccessLvl)"OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [RegionMgr] WHERE ([GrnHseID] = @GrnHseID)"UpdateCommand="UPDATE [RegionMgr] SET [GrnHseID] = @GrnHseID, [DispLvl] = @DispLvl, [FName] = @FName, [Minit] = @Minit, [LName] = @LName, [Phone] = @Phone, [Ext] = @Ext, [Cell] = @Cell, = @Email, [Login] = @Login, [Password] = @Password, [AccessLvl] = @AccessLvl WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"><DeleteParameters><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></DeleteParameters><UpdateParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></UpdateParameters><SelectParameters><asp:ControlParameter ControlID="DropDownList1" Name="GrnHseID" PropertyName="SelectedValue"Type="Int32" /></SelectParameters><InsertParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /></InsertParameters></asp:SqlDataSource>&nbsp; &nbsp; &nbsp;&nbsp;</td></tr><tr><td style="width: 120px"></td><td style="width: 100px"></td><td style="width: 203px"></td></tr></table></div></form></body></html>Appreciate any and all help!Dave

View Replies !   View Related

---

How To Update With SQL EXISTS(?)
Hi,I have 2 tables in an SQLServer db.I want to compare table A with table B and add any records that EXISTin table B but dont exist in table A, to table A.Can anyone help me with the SQL?TIAHitcher

View Replies !   View Related

---

IF EXISTS Statement In My Perl Program
I am having trouble finishing my query.

This is what I have:

IF EXISTS(Select ApplicationID from Application Where Application = '&_')
Insert Into PCApp(ApplicationID, SystemNetName)
Values( , $HoH->{Host}{SystemNetName})

I am not sure what to put in the blank within the Values parenthesis. I need to obtain the ApplicationID that is checked in the IF EXISTS section. But I cannot put a select statement into the Values() section.

Any suggestions would be appreciated.

Thanks,
Laura

View Replies !   View Related

---

ALTER PROCEDURE Statement Within IF EXISTS
Hello.

I'm trying to create a batch sql script which first alters some existing tables via the ALTER TABLE command, I then want to alter some existing stored procedures via the ALTER PROCEDURE command within the same batch. I have found that I can encompas the alter table scripts within a conditional IF EXISTS (Begin/End) but not the alter procesdure scripts. I have looked in reference material and have found nothing to suggest this type of operation is not possible. Is this possible? Is this a know bug fixed by a service pack?

Thanks in advance for any replies.
David.

View Replies !   View Related

---

Stored Procedure Exists Statement
When I created an SP in Enterprise Manager, I didn't manually type in the existence check at the inception, a la "if exists (select * from sysobjects...)". I just started with the CREATE PROC AS statement.

I noticed that if I generate a SQL script for the SP, SQL2000 automatically generates the existence check statement.

My question is, can I assume that when the SP is actually executed, SQL2000 does the exists check on its own? I EXEC'd the proc in Query Analyzer with no errors.

I just want to make sure that I don't need to enter the exists statement if it's already being done behind the scenes.

View Replies !   View Related

---

Insert If Not Exists
I have two tables that I have to compare:


Code:


Table:PR
WBS1 WBS2 WBS3
123-456 1000 01
123-456 1000 02
123-456 2000 02
567-890 2000 01
567-890 2000 02

Table:PR_Template
WBS2 WBS3
1000 00
1000 01
1000 02
2000 00
2000 01
2000 02

After Insert I should have:

wbs1 wbs2 wbs3
123-456 1000 00
123-456 1000 01
123-456 1000 02
123-456 2000 00
123-456 2000 01
123-456 2000 02
567-890 1000 00
567-890 1000 01
567-890 1000 02
567-890 2000 00
567-890 2000 01
567-890 2000 02





Basically, I need to insert the wbs2 and wbs3 where it does not exist in each wbs1.

What I have now will find the values that need to be inserted for a particular project but I don't know how to go through each project and perform the insert:

Select * from PR_template Where Not Exists
(Select Wbs1, Wbs2, Wbs3 from PR where PR.WBS2 = PR_Template.WBS2
And PR.WBS3 = PR_Template.Wbs3 and pr.wbs1 = '123-456')
Order by wbs2, wbs3 asc

Thank You

View Replies !   View Related

---

Trying To Select Info With If Exists Statement With No Avail
Im trying to select rows on the following criteria

My app has a user that can have x jobs, each job has related entries which are marked has unread or read. I need to return any jobs that contain any unread entries also I need to return other jobs that have no entries marked as read.

I am able to return the following:


Comment UnReadMy Job 164
Comment UnReadFor Cam 166

Comment ReadThe Job 157
Comment ReadThird 159
Comment ReadMy Job 164
On Site Visit ReadMy newest job for log test 167

but as you can see jobid 164 appears in both groups and I need it to be one or the other

I tried using the if exists statement and was only able to return one group either unread or read, not both. I've tried everything but I'm new and I figure that theres got to be a more elegant way. Heres my sql:


CREATE PROCEDURE spGetJobsByUnreadAndReadByUserID
@UserID INT
AS


BEGIN
IF EXISTS(SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who

FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID

WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'UnRead')


BEGIN
SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who

FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID

WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'UnRead'

ORDER BY BackUp_Read ASC
END

ELSE

BEGIN


SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who

FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID

WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'Read'

ORDER BY BackUp_Read ASC
END
END
GO


Thanks again

View Replies !   View Related

---

Using NOT EXISTS In An INSERT Procedure
I am using the following code to insert records into a destination tablethat has a three column primary key i.e. (PupilID, TermID &SubjectGroup). The source table records all the pupils in a school with(amongst other things) a column (about 50) for each subject the pupilmight potentially sit. In these columns are recorded the study groupthat they belong to for those subjects. The destination table holds arecord per pupil per subject per term, against which the teacher willultimately record the pupils performance.The code as shown runs perfectly until the operator tries to insert aselection of records that include some that already exist. What I wouldlike it to do is, record those, which do not exist and discard theremainder. However, whenever a single duplicate occurs SQL rejects thewhole batch. I know that my solution will probably involve using the‘NOT EXISTS’ expression, but try as I might I cannot get it to work. Tofurther complicate things, the code is being run from within VBA usingthe RunSQL command.The variables ‘strFieldName’, ‘strGroup’ & ‘strTerm are declared at thestart of the procedure and originate from options selected on an Accessform.INSERT INTO dbo.yInterimReportData (PupilID, LastName, FirstName,TermID, SubjectGroup) SELECT PupilID, LastName, FirstName," & "'" &strTerm & "'" & "," & "'" & strGroup & "'" & "FROM dbo.Pupils WHERE (" &strFieldName & " = " & "'" & strGroup & "')Any Ideas?RegardsColin*** Sent via Developersdex http://www.developersdex.com ***

View Replies !   View Related

---

Don't Insert If Record Exists
/*if key values exist don't insert new record*/
SELECT

/*if exists don't insert*/
CASE
WHEN ISNULL(gradeId, -1) = -1 THEN
INSERT INTO tblScores
(gtStudentId, assignmentId, score)
VALUES (@nStudent, @nAssignment, 0)
END

FROM tblScores
WHERE gtStudentId = @nStudent AND assignmentId = @nAssignment


tblScores has two fields comprising its primary key (gtStudentId, assignmentId) and the gradeId field is a required filed in this table.

I'm getting syntax errors when I click check syntax (near keywords insert from and end).

one other note: this CASE END is nested inside a BEGIN END loop, is this the problem? Is the 'End" of the 'Case' closing the 'End' of the 'Begin'?

thanks

View Replies !   View Related

---

Insert Record If None Exists
Hello folks,

I am new to msSQL and ASP, I need some help writing an SQL statement that will first check to see if a combination or record exists, if none found thant it will add it. I am working a section of a site that adds favorites to the database. Each user can have more that one favorite hence it has to check for that unique combination of the fields, UserID and FavID

My Code:
Dim objConnection, objRecordset, strSQL
Dim strFavID, strUserID
strFavID = request.Form("favid")
strUserID = request.Form("userid")

Set objConnection = Server.CreateObject("ADODB.Connection")
Set objRecordset = Server.CreateObject("ADODB.Recordset")
objConnection.Open Application("ConnectionString")

strSQL = "INSERT INTO FAV (UserID,FavID) (SELECT DISTINCT " & strUserID & " AS UserID " & _
strFavID & " AS FavID" & " FROM FAV)" & _
" WHERE " & strUserID & " & " & strFavID & " NOT IN (SELECT UserID, FavID FROM FAV)"
response.Write(strSQL)
'response.End()

If strFavID <> "" Then
On Error Resume Next
objConnection.Execute(strSQL)

objConnection.Close
Set objConnection = Nothing
End If


This code is giving me a syntax error, how do I write the correct statement. I am using MS Access 2k

Thanks for your help

View Replies !   View Related

---

Update Rows With Next Higher Id, If It Exists
I am trying to determine the next registered session of a student so Ican calculate the number of skipped sessions.Scenario: I have a student registration summary table. One row foreach student and the student's registered session. I want to update agiven row with the next higher registered session (into a field callednext_registered_session_skey if the row exists). I can then use thediff of the skeys to determine how many sessions the student skippedfor each registration period.Example: Student X registers each fall for one session for 4 years.The file might look like:STUDENT_ID SESSION_ID SESSION_SKEYNEXT_REGISTRED_SESSION_SKEY123456789 200201 100null123456789 200301 104null123456789 200401 108null123456789 200501 112nullI need to update the NEXT_REGISTRED_SESSION_SKEY so I end up with:STUDENT_ID SESSION_ID SESSION_SKEYNEXT_REGISTRED_SESSION_SKEY123456789 200201 100104123456789 200301 104108123456789 200401 108112123456789 200501 112nullI can then say SESSIONS_SKIPPED = NEXT_REGISTRED_SESSION_SKEY –SESSION_KEY (logically speaking, not syntactically)This is what I have so far as example:UPDATE F_REGISTRATIONSET NEXT_REGISTERED_SESSION_SKEY = (select top 1 nextr.session_skeyfrom f_registration rinner joinf_registration nextron r.student_skey = nextr.student_skey and nextr.session_skey[color=blue]> r.session_skey[/color]order by r.session_skey desc)WHERE STUDENT_ID = '577665705';SELECT student_skey, student_id, session_id, session_skey,next_registered_session_skey, * FROM F_REGISTRATION WHERE STUDENT_ID= '577665705' order by session_skey descRESULTS:STUDENT_SKEY STUDENT_ID SESSION_ID SESSION_SKEYNEXT_REGISTERED_SESSION_SKEY125137 577665705 200404 309 311125137 577665705 200403 308 311125137 577665705 200402 307 311125137 577665705 199804 285 311125137 577665705 199803 284 311125137 577665705 199802 283 311125137 577665705 199704 281 311TIARob(I restricted with the where = ‘577665705' so I did not have to waitto update all the rows)

View Replies !   View Related

---

Insert Not Exists Dual PK Problem
Hi  I am trying to populate a table with 2 FKs as its PK (SiteID and ProductDescID).  First 1) I add in all the products whose Manfacturer and Category are supposed to appear on the site and then 2) I add in all the extra products that are needed regardless of their manufacturer or category.   The problem I am having is if the product has already been added to the ProductCatTable due to its Manufacturer or Cateogry but is also in the SatForceProduct table.  The can’t insert duplicate PK error is thrown.  I don’t know how to do this IF NOT EXISTS statement (or what ever else may be needed) so that I can check whether a line from the Forced table needs to be added.  I am not passing in any parameters and I am expecting more than 1 line to be inserted in each of the statements. Please help -- 1) Populate      INSERT INTO            dbo.ProductCatTable            (SiteID, ProductDescID)             SELECT                dbo.SatSite.SiteID, dbo.ProductDesc.ProductDescID      FROM        dbo.SatManu INNER JOIN        dbo.ProductDesc ON dbo.SatManu.Manu = dbo.ProductDesc.Manu INNER JOIN        dbo.SatCats ON dbo.ProductDesc.Cat = dbo.SatCats.Cat INNER JOIN        dbo.SatSite ON dbo.SatManu.SatID = dbo.SatSite.SiteID AND         dbo.SatCats.SatID = dbo.SatSite.SiteID        2) Add Force Ins  IF NOT EXISTS(SELECT SiteID, ProductDescID FROM ProductCatTable WHERE ????????) BEGIN       INSERT INTO                        dbo. ProductCatTable                                    (SiteID, ProductDescID)       SELECT            SiteID, ProductDescID      FROM         dbo.SatForceProduct        END            Thanks in advance J
 

View Replies !   View Related

---

Checking To See If A Record Exists Else Insert
I am checking to see if the source record is available in the target table using a lookup transformation and if not found i have to insert this record.
I have connected the error flow of the lookup transformation to the target. I am acheiving expected results, but is this the best practise? I have not connected to the green arrow to any task.

View Replies !   View Related

---

Loop,file Exists And Insert To Table...
 
Hi,
 
Here is the steps I should take...
1- Check for the log table and find  run status ( there is a date field which tells the day run)
2- Lets say last day was 2008-05-15, So I have to check  A1.DDDDMMYYY file exists in the folder for each
    day like A1.20080516,A1.20080517 and A1.20060518 ( until today)
3- if A1.20080516 text file exist then I have to move it to the table and same thing for other dates
    like if A1.20080517 exists I have to load it to table and so on
 
it looks like for each loop, first I have to get the last date and then I have to check the file exists for each date and
if the date  file exists then I have to load it into table...
 
Please tell me How can I do it.  it looks complex looping...
 
thanks,
J
 

View Replies !   View Related

---

Need To Set A Field In A Select Statement Equal To Yes Or No If Record Exists In Separate Table
Hey gang,
I've got a query and I'm really not sure how to get what I need.  I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query')
I am able to select all of the records from the first two tables that I need.  The problem I'm having is the last step.  I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table.  The table that I need to look into can have up to 99 instances of the customer number.  It's a "Note" table that stores a string, the customer number and the sequence number of the note.  Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling.
Here's my current Query this works fine:
Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum
From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo
Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#')
What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table.  This table has 3 fields CustomerNo, SequenceNo, Note.
I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table.  I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No)
Any advice would be greatly appreciated.

View Replies !   View Related

---

Checking Specific Columns Were NULL On Previous Insert (Using If EXISTS)
Thanks for your time:
 
Background:  After Insert Trigger runs a sproc that inserts values into another table IF items on the form were
populated.  THose all work great, but last scenario wont work:  Creating a row insert based on Checking that all 22 other items from the prior insert (values of i.columns) were NULL:
 
IF EXISTS(select DISTINCT i.notes, i.M_Prior, i.M_Worksheet, ...
from inserted i
WHERE i.notes IS NOT NULL AND i.M_Prior = NULL AND i.M_Worksheet = NULL AND...)

 
BEGIN

Insert into dbo.Items2Fix ...

From inserted i

END

View Replies !   View Related

---

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View Replies !   View Related

---

UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono

View Replies !   View Related

---

JDBC 2005 Update Statement - Failing Multi Row Update.
It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.

View Replies !   View Related

---

Strange Problem: SQL Insert Statement Does Not Insert All The Fields Into Table From Asp.net C# Webpage
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
 
thanks

View Replies !   View Related

---

How To Write Query To Insert 10,000 Rows In A Table Using Insert Statement One Time
create table and inserting 10,000 row values at a time using single insert statment

ex: I want to create table employee having two coloumns
like employeeid , name

note : name can be static i.e : same name for 10,000 rows

employeeid is unique

can any one write a query for me?

View Replies !   View Related

---

Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)
Hello
 I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :

 and here goes the  code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70

and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"


Could You please tell me what am I missing here ?
Thanks a lot.
 

View Replies !   View Related

---

Stored Procedure - Update Statement Does Not Seem To Update Straight Away
Hello,
 
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
 
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?

View Replies !   View Related

---

Case Statement Error In An Insert Statement
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.

View Replies !   View Related

---

Can I Insert/Update Large Text Field To Database Without Bulk Insert?
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind.  I've tried using the new .write() method in my update statement, but it cuts off the text after a while.  Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

View Replies !   View Related

---

Trigger To Update A Table On Insert Or Update
 

Hello
 
I've to write an trigger for the following action
 
When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz
 
all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated
 
statut_tiers to 1
and date_cloture to the same date as entered
 
the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture
 
thank you for your help
I've never done a trigger before
 

View Replies !   View Related

---