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,
LastName
FROM
Master
UNION ALL
SELECT
FirstName,
LastName
FROM
Custom
ORDER 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 Complete Forum Thread with Replies
Related Forum Messages:
Need Gridview To Display Different Columns Based On New SELECT Statement
I have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great. My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around. So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting. I am desperate on this. I've burned 2.5 days researching and testing. Does anyone have any suggestions? Thanks, Brad
View Replies !
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 !
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 !
HELP With A Select/Union Statement
I have 3 tables One table is the order Table, Bill to table and ship to table I have to Views created as followed This query uses the Ship to table to pull the ship to information to the shipping system. SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO); This query uss the Bill to as the ship to inforamtion SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID; I need this infroamtion in one table which I have done in the UNION statement as followed: SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO) UNION ALL SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID; Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information. NAMEADDR_1ADDR_2ADDR_3CITYSTATEZIPCODECOUNTRYSHIP_VIAID DIEBOLD INC (4076A)ATTN: RANCE AARON343 MANOR DRPACIFICACA9404418932 DIEBOLD, INCOHUPS #88X08X18932 MY POINT: Is there a way to select a over all DISTINCT order ID. Thank you for any help hope this make sense!
View Replies !
UNION ALL SELECT Statement Help
I am not completly sure if I have this posted in the right forum so if I don't just let me know and I will move it. Here is my problem. I need to be able to use the WHERE keyword more than once in one SELECT statement and have not been able to figure this out. I need to be able to first search for information under one column with the WHERE keyword like usual and then I need to be able to search the returned results with another WHERE keyword to narrow down the returned results. I tried writing two SELECT statements and joining them with a UNION ALL keyword like this: "SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _ "FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _ "ON LI.CNID = CD.ID WHERE LI.CNID = '1'" & _ "UNION ALL" & _ "SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _ "FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _ "ON LI.CNID = CD.ID WHERE LI.FDName = 'Car1" This hasn't worked and I didn't expect it to. Everytime I run this code I get an Unhandled SqlExecption: Invalid column name 'Towing'. Can anyone help me with figuring out how to use the WHERE keyword more than once. I am using Visual Basic.Net with ADO.Net. Thanks!
View Replies !
Select Statement Based On A Date
i am not getting a result back when i run the query below. select * from users where DateCreated = '2004-02-19' so i went into the table and looked at the record. for DateCreated field i have both date and time. ex: 2004-02-19 08:40:00 how can i select this record with out using the time in the select statement. what i want to see is how many users signed up for a day. any ideas?
View Replies !
Easy Table Based Update Statement???
Hello,I have 2 ways of updating data I'm using often1) via a cursor on TABLE1 update fields in TABLE22) via an some of variables ...SELECT @var1=FLD1, @var2=FLD2 FROM TABLE1 WHERE FLD-ID = @inputVARUPDATE TABLE2SET FLDx = @var1, FLDy = @var2WHERE ...Now I have a system with 2 databases and I need to update table DB2.TABbased on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,I thought it would be much easier to get the required data immediately fromDB1.TAB in the update-statement of DB2.TAB ... but the way to do thatconfuses me. I've checked books online and a lot of newsgrouppostingsgiving good information but still I keep getting errors like this ...The column prefix 'x.ADS' does not match with a table name or alias nameused in the query.while executing the following statement ...UPDATE DB2.dbo.TABSETFLD1 = x.FLD1,FLD2 = x.FLD2,...FROM DB1.dbo.TAB x, DB2.dbo.ADSWHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =@InputParameterSo in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 oftable DB1.TABAND I only want to update the row in DB2.TAB with the unique keyfieldIDOFTAB2 equal to variable @InputParameterDo you see what I'm doing wrong?--Thank you,Kind regards,Perre Van Wilrijk,Remove capitals to get my real email address,
View Replies !
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 !
How To Do Update Of Select Columns Based On...
the following criteria. i have the selection all done but am trying to figure out how to do the following: if column4 < 0 then add column4 to column3, move 0 to column4; if column3 < 0 then add column3 to column2, move 0 to column3; if column2 < 0 then add column2 to column1, move 0 to column2; add column3 to column4; move column2 to column3; move column1 to column2; if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1; these are all numeric data types.
View Replies !
Insert Based On A Select Query
Hi,I have a SQL query:select products.name, products.notes, products.purchase_date,products.serial_no, products.total_value, products.product_code,products.quantity, products.product_group, products.lhs1_name,lhs1.department, lhs1.address1, lhs1.city, lhs1.countryfrom products, lhs1where products.lhs1_id = lhs1.idI want to insert the results of this query into a table called 'temp' in thedatabase. I used to copy and paste this into excel then import it but itdoesn't always work.Is there a way to do it all in a SQL script. Please be aware that myknowledge of SQL is fairly basic so please explain things clearly.Thanks,Darren
View Replies !
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View Replies !
Insert / Update Trigger Based On A Column's Value
I am new to triggers and surely could use some help. I can create a trigger to insert related records based on the main tables ID and insert that value into other related tables fine... but... How do I create a trigger that can insert a record into one table for a columns given value and then insert a record into another table for another given value? For instance: New row... Table1, Column1 (PK) has a value of 101 Table1, Column2 has a value of 'Blue'. // When a new row is created in Table1 and Column2 has a value of 'Blue'... I want to insert a new row into Table2 - with Table1 Column1's value. // Now if Table1, Column2 has a value of 'Red' when the new row was created... I want to insert a new row into Table3 - with Table1 Column1's value. Not Table2 This has to be inserted into one or the other tables based on column2's value, not both. Then I want to populate the other related tables (Table4, Table5) with the regular insert statements based on Table1 Column1's value. This (the conditional part above) has to work with an update to Table1 also. So if someone came back to that record and changed Column2's value from 'Blue' to 'Red', it would have to delete the appropriate record in Table2 and then insert the new row into Table3 and visa-versa. Can I do this with one trigger? Thanks
View Replies !
Select Based On User Identity Name Included In Insert Into Second Table
I am having issues getting this to work. I have the user login to a page to put in a request for vacation. When they login, I have a label that isn't visible that is equal to their User.Identity.Name. I select the user from the employee table where the username = the label User Identity Name and pull in the emp_id which is the primary key that identifies the user. I need to insert the request into the request table with the emp_id from the select statement, without showing the em_id on the screen. I tried using a hidden field and assigning the emp_id as the value, but it isn't working. Not sure if this is the best way to do this. Really new to ASP.NET 2.0 so I really appreciate any help. Thank you!
View Replies !
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 !
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 !
SQL Statement - INSERT INTO And SELECT
Hi,I have a very simple issue: for simplicity lets say I have 2 tables, A andB.- Table A contains 5 fields. Amongst these there is a 'id'-field whichis but a reference to table B.- Table B contains 2 fields: 'id' and 'text'In order to post data to table A I thus (from a known text value that shouldmatch 1 value in B.text) have to get the value of B.text before performingthe UPDATE/INSERT statement.How is this possible?I would have thought something likeINSERT INTO A (val1, val2, val3, ID, val4)VALUES ('x1','x2','x3', SELECT id FROM B WHERE [SOME TEXT VALUE] = B.text,'x4')however this is not possible, so I'm lost - not experienced in the arts ofSQL:-)Hope someone can help.Best Regards,Daniel
View Replies !
Insert Into From Select Statement
I have following codes : insert into table1 (lastname,firstname) select ##Newrows.LAST, #Newrows.FIRST, from ##newrows if @@error > 0 begin select @ReturnError = 91001 end Will this return @@error = 0 if insert fails but select is successful? Thanks in advance. -jfk
View Replies !
SELECT Insert Statement
Hello All, INSERT INTO [SPIResConv5].[dbo].[Batch] ([BATCH_NO], [OPENDT] ,[USERID] ,[MODULE] ,[USERBATCH] ,[RESORT_ID] ) Select [BATCH_NO], [OPENDT] = getdate(), 'Hwells' as [USER_ID], '1 CASH RECEIPT' as [MODULE], [USERBATCH], Resort_ID = Case Resort_ID when 2 then 'Ell' when 3 then 'CSI' when 12 then 'Ell2' when 13 then 'ATR' end from TransactionTempToTransaction where Resort_ID = 3 or Resort_ID = 2 or Resort_ID = 12 or Resort_ID = 13 needing help in modifing the statement above The first would be to grap from the batch table the last [BATCH_NO] and add one number to it-and insert --just that number €“for all records imported to Batch Table. Example: last record was 50 then 51 is the batch for all recordes imported. Second: [USERBATCH] is a varchar, but need to insert based on current date: Say for example -today 2/23/2007 but need to insert in format = '2/23 LBX' Last and again thanks for the help- Resort_ID-For the Batch table only need to import how many rows, Based on how many different resorts or in the table TransactionTempToTransaction For example-- if there are two diffrent resorts €“ only import two rows With the last field Resort_id being the only difrrent field showing the resort_id. If six different resorts or in tabe TransactionTempToTransaction then six rows. Thanks for your JK
View Replies !
Select Statement Insert
I have a select statemnet that I want to insert new rows if the data is not found from table 1 into table 2. How can I add onto this statement? What is the sql code neede? Select * From tbl_Data_OpenOrders WHERE EXISTS (Select * From tbl_TestData WHERE tbl_Data_OpenOrders.oabl = tbl_TestData.oabl AND tbl_Data_OpenOrders.ODLOTSEQ = tbl_TestData.ODLOTSEQ AND tbl_Data_OpenOrders.OACUSTPO = tbl_TestData.OACUSTPO AND tbl_Data_OpenOrders.OABLDATE = tbl_TestData.OABLDATE) Lisa Jefferson
View Replies !
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 !
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 !
How To Update If Exists Else Insert In One SQL Statement
In MS Access I can do in one SQL statement a update if exists else ainsert.Assuming my source staging table is called - SOURCE and my targettable is called - DEST and both of them have the same structure asfollowsKeycolumns==========MaterialCustomerYearNonKeyColumns=============SalesIn Access I can do a update if the record exists else do a insert inone update SQL statement as follows:UPDATE DEST SET DEST.SALES = SOURCE.SALESfrom DEST RIGHT OUTER JOIN SOURCEON (DEST.MATERIAL = SOURCE.MATERIAL ANDDEST.CUSTOMER = SOURCE.CUSTOMER ANDDEST.YEAR = SOURCE.YEAR)This query will add a record in SOURCE into DEST if that record doesnot exist in DEST else it does a update. This query however does notwork on SQL 2000Am I missing something please share your views how I can do this inSQL 2000.ThanksKaren
View Replies !
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 !
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 !
Select And UPDATE Statement Help! (Using SQL Server)
I have a table which I need to obtain data from but am having a problem with select statement. Specifically, I have a table that has multiple records for a particular hostName where the name of the host is either a shortname (say "Larry") or a long name (say "Larry's"). I need to display only the long names (NOT THE SHORT NAME records with the similar hostName). Select winsHostName, len(winsHostName) from winsData where winsClientIPAddress IN (SELECT winsClientIPAddress from winsData Group By winsClientIPAddress Having count(winsClientIpAddress) > 1) Order by winsHostName Which returns data Name Length ATVDDR 6 ATVDDR1 7 This is a s far as I can get but, Now, I need to list all remaining table fields based on the Name with the longer length and this is where I run into trouble. The output should read per below with the remaining table fields which I cannot seem to extract with nested select statement Name IP Addr Location ATVDDR1 1.1.1.1 2ndFloor I tried adding another GROUP BY by this gets me no where because I do not need to execute another aggregate in select statement. Maybe I need to use INNER JOIN Please advise any assistance.
View Replies !
Imbedding A Select Statement Within An Update
I have the following list of fields in a table named call.CallID, CallGroupID, PersonID, ParentID,I have two records.23, 45, John, Null26, <Incorrect Value>, Jane, 23Now, I'm wanting to set the CallGroupID of record two, to that of theCallGroupID of record one.This is what I came up with, but it doesn't work.update call set CallGroupID = (Select CallGroupID from call whereCallID = ParentID) where ParentID is not nullAs you know the Select statment I have embedded is trying to pull up alist CallGroupIDs from records where the CallID = ParentID. What Iwant it to do is pull up the CallGroupID of the record where the CallID= ParentID.The CallID is a unique field, so there is only one such record in thetable.Any help?Thanks
View Replies !
How Do I Use Select Statement In Update Query
hi myself avii am developing one appliacaion in which i am using vb 6 as front end,adodb as database library and sql sever 7 as backend.i want to update one table for which i required data from other table. andiretrive data from second table by giving some condition. when i get data,then to update first table i need to use do while loop. instead of that iwant to use select statement directly in update query.plz give me some help.following is the my queries and its out putStrSql = ""StrSql = "Select * From SalesVchMaterialDesc where TransactionID=" &txtTransactionID.text & ""rsMName.Open StrSql, Conn, adOpenKeysetDo While Not rsMName.EOFStrSql = ""StrSql = "Update StockTable Set Outward=Outward - " &rsMName("Netweight") & ",OutwardQty=OutwardQty - " & rsMName("Qty") & "Where MaterialId=" & rsMName("Material_Name") & " and VoucherDate='" &Format(rsMName("VoucherDate"), "mm/dd/yyyy") & "'RsAdd.Open StrSql, Conn, adOpenStaticrsMName.MoveNextLooprsMName.Closeout put***main querySelect * From SalesVchMaterialDesc where TransactionID=848do while not loopUpdate StockTable Set Outward=Outward - 8.06,OutwardQty=OutwardQty - 1Where MaterialId=221 and VoucherDate='04/01/2004' and SMID=0loop
View Replies !
Dynamic Select/Update Statement Possible?
Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter? For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following... ALTER PROCEDURE u_sp_x @semester int AS Select Semester@semester From ThisTable Just curious. Thanks, Steve Hanzelman
View Replies !
How To Convert SELECT Into UPDATE SQL Statement
Hello! I am trying to write an update t-SQL statement from the following select statement: SELECT EduNextContacts.ssn FROM EduNextContacts Left JOIN EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code GROUP BY EduNextContacts.ssn HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) + SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2) I tried many versions of writing my update statement with no luck. My most recent version is as follows: UPDATE EduNextContacts SET EduNextContacts.overLMLimit = 'Y' FROM EduNextContacts Left JOIN EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code WHERE EduNextContacts.ssn = (SELECT DISTINCT EduContactsAuditChanges.ssn FROM EduNextContacts Left JOIN EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code GROUP BY EduContactsAuditChanges.ssn HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) + SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2)) And gives the following error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Can anyone help shed some light on how to make an update statement from my SELECT query above? Thanks in advance, Harry
View Replies !
Insert From Parameters And Select Statement
Trying to insert into a history table. Some columns will come fromparameters sent to the store procedure. Other columns will be filledwith a separate select statement. I've tried storing the select returnin a cursor, tried setting the values for each field with a separateselect. Think I've just got the syntax wrong. Here's one of myattempts:use ESBAOffsetsgoif exists(select * from sysobjects where name='InsertOffsetHistory' andtype='P')drop procedure InsertOffsetHistorygocreate procedure dbo.InsertOffsetHistory@RECIDint,@LOB int,@PRODUCT int,@ITEM_DESC varchar(100),@AWARD_DATE datetime,@CONTRACT_VALUE float,@PROG_CONT_STATUS int,@CONTRACT_NUMBER varchar(25),@WA_OD varchar(9),@CURR_OFFSET_OBL float,@DIRECT_OBL float,@INDIRECT_OBL float,@APPROVED_DIRECT float,@APPROVED_INDIRECT float,@CREDITS_INPROC_DIRECT float,@CURR_INPROC_INDIRECT float,@OBLIGATION_REMARKS varchar(5000),@TRANSACTION_DATE datetime,@AUTH_USERvarchar(150),@AUTHUSER_LNAMEvarchar(150)asdeclare@idintinsert into ESBAOffsets..HISTORY(RECID,COID,SITEID,LOB,COUNTRY,PRODUCT,ITEM_DESC,AWARD_DATE,CONTRACT_VALUE,PROG_CONT_STATUS,CONTRACT_TYPE,FUNDING_TYPE,CONTRACT_NUMBER,WA_OD,PM,AGREEMENT_NUMBER,CURR_OFFSET_OBL,DIRECT_OBL,INDIRECT_OBL,APPROVED_DIRECT,APPROVED_INDIRECT,CREDITS_INPROC_DIRECT,CURR_INPROC_INDIRECT,PERF_PERIOD,REQ_COMP_DATE,PERF_MILESTONE,TYPE_PENALTY,PERF_GUARANTEE,PENALTY_RATE,STARTING_PENALTY,PENALTY_EXCEPTION,CORP_GUARANTEE,BANK,RISK,REMARKS,OBLIGATION_REMARKS,MILESTONE_REMARKS,NONSTANDARD_REMARKS,TRANSACTION_DATE,STATUS,AUTH_USER,PMLNAME,EXLD_PROJ,COMPLDATE,AUTHUSER_LNAME)values(@RECID,(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),@LOB,(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =@RECID),@PRODUCT,@ITEM_DESC,@AWARD_DATE,@CONTRACT_VALUE,@PROG_CONT_STATUS,(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),@CONTRACT_NUMBER,@WA_OD,(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID= @RECID),@CURR_OFFSET_OBL,@DIRECT_OBL,@INDIRECT_OBL,@APPROVED_DIRECT,@APPROVED_INDIRECT,@CREDITS_INPROC_DIRECT,@CURR_INPROC_INDIRECT,(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master whereRECID = @RECID),@MILESTONE_REMARKS,@NONSTANDARD_REMARKS,@TRANSACTION_DATE,(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),@AUTH_USER,(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =@RECID),@AUTHUSER_LNAME)select@@identity idgogrant execute on InsertOffsetHistory to publicgo
View Replies !
How To Use Select Statement In Insert Query
hi my self avii want to copy data from one table to other table,by giving certaincondition and i want o use insert statement .in this i want to pass somevalue directly and some value from select statement , if i try i ll geterror i.e all column of destination table (i.e in which i want to insertdata) should match with all columns in values column some thing likethis.plz give me some helpful suggetion on this
View Replies !
Using A Select Statement To Only Insert In Certain Rows
Hey, I am not sure how to really explain this, but I'll give it a try. I am looking to use a select statement in a way that I can tell it which rows to insert in depending on when only one result is returned. For example, if I run this statement: SELECT Column1, Column2, Column3 FROM #Temp1 The result set is: Column1---Column2---Column3 99--------6756756---55555 44--------55---------NULL Column3 as only the one returned value, so I do not want it associated with any of the other rows, so I need this: Column1---Column2---Column3 NULL------NULL------55555 99--------6756756---NULL 44--------55---------NULL Another example: The returned result now is: Column1---Column2---Column3---Column4 99---------6756756---55555-----NULL 42---------55---------NULL------12345 So I need: Column1---Column2----Column3----Column4 NULL-------NULL-------55555------NULL NULL-------NULL-------NULL-------12345 99---------6756756----NULL-------NULL 44---------55----------NULL-------NULL Does this make sense, and/or is it even possible? I know it could be more of a presentation thing, but I would like to know how to do it in the code behind. Thanks
View Replies !
Insert..Select Statement Problem
Hi, I am trying to run the following insert statement, but am gettng an error. The table I want to insert to (DimensionMonthTime_hold) has an Identity column defined for its key. The part I cannot figure out is that when I run this insert statement without the order by statement, the insert is successful. If I attempt to run this insert statement with the Order By statement, I get an error saying that I have to provide a value for the identity value in the insert statement (which i don't want to do.) I need to have the data sorted, hence the reason for the order by. I've tried to specify the column names on the insert line, but haven't figure that out. Any suggestions? Thanks Jim ---------------------------- Insert into DimensionMonthTime_hold select distinct substring(period,1,4) + substring(period,6,2), substring(period,1,4) , case substring(period,6,2) when '01' then '1' when '02' then '1' when '03' then '1' when '04' then '2' when '05' then '2' when '06' then '2' when '07' then '3' when '08' then '3' when '09' then '3' when '10' then '4' when '11' then '4' when '12' then '4' else 1 end, substring(period,6,2), case substring(period,6,2) when '01' then 'First Quarter' when '02' then 'First Quarter' when '03' then 'First Quarter' when '04' then 'Second Quarter ' when '05' then 'Second Quarter' when '06' then 'Second Quarter' when '07' then 'Third Quarter ' when '08' then 'Third Quarter ' when '09' then 'Third Quarter ' when '10' then 'Fourth Quarter' when '11' then 'Fourth Quarter' when '12' then 'Fourth Quarter' end, case substring(period,6,2) when '01' then 'January' when '02' then 'February' when '03' then 'March' when '04' then 'April ' when '05' then 'May' when '06' then 'June' when '07' then 'July ' when '08' then 'August ' when '09' then 'September ' when '10' then 'October' when '11' then 'November' when '12' then 'December' end, 1, getdate() from transaction where Account_type ='A' ------------------------
View Replies !
Trigger For INSERT --&&> SELECT Statement
Hello, I have a trigger on a table named Store. The trigger updates the longitude and latitude on store based on the zip. Simple right? Well, I'm trying to import data and of course the trigger is not updating the data as triggers are not on a row by row basis with multi row inserts. Here is the error message I'm receiving: Msg 512, Level 16, State 1, Procedure SetLongLat, Line 17 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. How would one go about resolving this issue? I've includes my snippets below: Trigger: Code Block ALTER TRIGGER [dbo].[SetLongLat] ON [dbo].[Store] FOR INSERT, UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @Long float DECLARE @Lat float SELECT @Long = LONGITUDE, @Lat = LATITUDE FROM Zipcode..ZipcodeLite WHERE ZIP_CODE = (SELECT Zip FROM Inserted) UPDATE Store SET Longitude = @Long, Latitude = @Lat FROM Store INNER JOIN Inserted ON Store.Id = Inserted.Id IF UPDATE(Zip) BEGIN UPDATE Store SET Longitude = @Long, Latitude = @Lat FROM Store INNER JOIN Inserted ON Store.Id = Inserted.Id END END Thanks for your help! Nathan
View Replies !
Trouble With An Insert Into/select Statement
I have several tables in a database which I always want to update with information from one table with new records (containing contact and demographical information). The setup is something like this: NewRecordsTable: fn, ln, streetadd, city, emailadd, phonenumber, gender, birthdate ContactTable: ID(primarykey), fn, ln, streetadd, city, state, zip, phonenumber, email DemographicTable: ID(linked to primary key ID in Contact table), birthdate, gender I want to update the ContactTable and DemographicTable with information from the NewRecords Table. What I have done so far is set the identity insert for the ContactTable to on, then inserted the fn, ln, streetadd, email, etc. from the NewTable. This works fine. I then try to insert ID, birthdate and gender into the DemographicTable where NewRecordsTable.fn=ContactTable.fn AND NRT.ln=CT.ln AND NRT.streetadd=CT.streetadd AND NRT.emailadd=CT.emailadd - This mostly works, but the records which have NULL values any of those fields don't get inserted. What I really want is to insert the records that have matching email addresses OR matching fn, ln, streetadd combos, but I can't figure out how to get that SELECT/WHERE statement to work. The problem that underlies this is that I want to insert the ID values from the ContactTable into the DemographicTable, but the only way I can see to make them match properly is by matching the email addresses or fn, ln, streetadd combos from the NewRecordsTable to the ContactTable (all of the email addresses in our NewRecordsTable are unique, unless the person doesn't have an email address, in which case we make sure they have a unique fn, ln, streetadd combo) Any help would be appreciated, Thank you!!
View Replies !
Problem With SELECT And INSERT T-sql Statement
hello everybody I want to ask for your help in an issue i am having with SQL Server 2005 Developer Edition . here is the issue: We have 2 servers called: c10 and cweb. In both, we manually installed SQL server 2005 Dev Edition with no problems. I created a linked server on c10 to access data on cweb. That is working fine with no problem when executing Select or Insert T-SQL statments like these ones from c10: select * from cweb.DBNAME.dbo.TableNAME Or insert into cweb.DBNAME.dbo.TableNAME (f1, f2, f3) select f1,f2,f3 from c10.DBNAME.dbo.TableNAME All works fine up to here. But then there is a new server we setup called c7. This time we created an image of c10 and restore that image on this new server c7. That way, we didnt need to install all software needed in this new server. All software seemed to work ok..but then SQL server 2005 on that new server started failing when doing SELECT t-sql statements. So Now if i am on c7 and i try to execute this: SELECT * from C7.DNAME.dbo.TableName, it fails C7 in this case is the local server and it should work. however the error it gives me is that :"linked server not recognize"...it shouldnt need a linked server since it is trying to access the local server. Even with that, i tried to create a linked server to the own local server and now that Select t-sql isntruction worked with no problem..But now here is the othe issue i am having: INSERT t-sql statements are not working. When doing this: insert into c7.DBNAME.dbo.TableNAME (f1, f2, f3) select f1,f2,f3 from c7.DBNAME.dbo.TableNAME2 It fails with the following 2 error messages: "OLE DB provider "SQLNCLI" for linked server "c7" returned message "Multiple-Step OLE DB operation generated errors. Check each OLE DB status, if available. No work was done The OLE DB provider SQLNCLI for linked server citrix7 could not insert into table c7.DBNAMe.dbo.TableNAme because of column intID. the data value violated the integrity constraints for the column." I checked that the SELECT part of the INSERT T-sql statement is not retrieving any invalid data for column intID. I tried restoring the BD on c10 server and tried the same INSERT statement and it worked ok..which mean the data to be inserted is valid. So i think it is related to some mis-configuration on the linked server or something in SQL server got broken when restoring c10 server image into the new c7 server So in summary the problem is this: 1. i can not make SELECT T-sql statements using fully qualified names on the local sql server without having a linked server to the local server (which is strange) 2. I can not make INSERT T-sql statements in the local server. This errors happens when doing it "OLE DB provider "SQLNCLI" for linked server "c7" returned message "Multiple-Step OLE DB operation generated errors. Check each OLE DB status, if available. No work was done The OLE DB provider SQLNCLI for linked server citrix7 could not insert into table c7.DBNAMe.dbo.TableNAme because of column intID. the data value violated the integrity constraints for the column." I have been searching thru google and forums but havent found any solutions yet. Hope you can help me with this..i guess my only option right now is just uninstall and re-install sql server..but maybe there is any other solution to this_? thanks a lot Helkyn
View Replies !
Select And Insert Statement Merge Together
is there anyway i can merge select statement and insert statement together? what i want to do is select few attributes from a table and then directly insert the values to another table without another trigger. for example, select * from product and with the values from product, insert into new_product (name, type, date) values (the values from select statment)
View Replies !
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 !
Insert Statement With Multiple Select Statements
hi first of all is it possible? if so, what am i doing wrong with this INSERT into TB2 ( ClientCode, EngagementCode, EngagementDescription ) SELECT (SELECT dbo.tarCustomer.CustID FROM dbo.tPA00175 INNER JOIN dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey), NULL, SELECT (SELECT dbo.tPA00175.chrJobNumber FROM dbo.tPA00175 INNER JOIN dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey) the first select statement for works fine, but the second one and all after i get a syntax error near 'select'. this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks tibor
View Replies !
T-SQL Select Statement Slows Down When Insert Into Is Put In Front
Hi We have a t-sql statement in a SP that generates on average between 50 €“ 60 rows of data, pretty small! The statement references a View, some tables and temporary # table which has been created in the SP. Everything works a treat and runs sub second until you put a Insert Into in front of the above statement scenario. The SP then takes a about a minute to run which happens to be about the same amount of time to generate all the data in the View. I have not attached T-Sql statement at this stage as it runs ok without the Insert Into but would be happy to post it if need be. Anybody else ever had this problem? We are using SQL Server 2005 SP2 64 bit. Art99
View Replies !
SELECT @@IDENTITY During INSERT STATEMENT Error
The following SQL statement fails on SQL CE 3.5 but works on SQL Express 2005: "INSERT INTO BOOKINGS VALUES(@now,'"+note+"'," + p + "); SELECT @@IDENTITY;" Compact 3.5 doesnt like the SELECT statement claiming that: There was an error parsing the query. [ Token line number = 1,Token line offset = 72,Token in error = SELECT ] Can anyone suggest the correct SQL to implement this via Compact? i.e. How do I retrieve the Identity value during and insert statement? I have removed the SELECT @@IDENTITY; portion of the statement and it runs fine.
View Replies !
Trouble Converting SELECT To INSERT Statement
Trying to convert the following SELECT statement into a INSERT statement and having trouble. No doubt this will be a piece of cake to someone. To eventually get this to a trigger stage would be nice, but for the moment I'd settle for just plain SQL. Using MS SQL 2000. The database name is reporting. The table name is CallLog. I'm trying to convert seperate date (RecvdDate) and time (RecvdTime) columns into a single DateTime column. I've scoured a lot of web pages but I'm still lost. ============== use reporting go SELECT RecvdDate + RecvdTime FROM [dbo].[CallLog] =============== Any help much appreciated.
View Replies !
|
TRACKER
