I'm trying to generate an update statement based off select statement results. A very basic example,
SELECT ListID FROM DListing WHERE Status = 2Results return 3 rows.
1234
2345
3456
How can I take those results and turn them into WHERE criteria for UPDATE statement?
Generated UPDATE statement should look like this.
UPDATE DListing SET Status = 1 WHERE ListID IN (1234,2345,3456)
I have started by creating a temp table to hold my SELECT results, but I don't know how to get those results into format for IN condition. Right now I get 3 UPDATE statements with 1 ListID in each IN condition.
CREATE TABLE #TempStatusUpdate(ListID INT)
INSERT INTO #TempStatusUpdate
SELECT ListID FROM DListing WHERE Status = 2
SELECT 'UPDATE DListing SET Status = 1 WHERE ListID IN (' + CONVERT(VARCHAR(30),ListID) + ') AND Status = 2'
DROP TABLE #TempStatusUpdate
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
I would like to provide the names of columns in an insert statement from a schema table, so that when running through a number of Bus Rule checks I can reference the schema table and only maintain the columns in the schema table rather than maintain named columns in multiple insert statements. So my query for one check looks like below. I'm using dynamic sql to execute the insert statement. My question is, is there a better way or different way to do this without using dynamic sql? Ie, Is there a way that I can use the columns parameter like this instead?
Declare @columns as nvarchar(max); Declare @InvSQL as nvarchar(max); SELECT @columns = STUFF (( SELECT ', [' + name + ']' FROM syscolumns WHERE id = OBJECT_ID('dbo.table_pvt')
I have a situation where I want to update a column if and only if it is null.
UPDATE Employee SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL THEN ves.VEmployeeID END FROM Employee E INNER JOIN VEmployeeStaging VES ON E.EID= VES.EID
But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.
So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.
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
In general, What is the best approach in creating a dynamic updatestored procedure, that can handle recieving varying input paramters andupdate the approporiate columns.
Hi, I rewrote my working Sql statement to prevent Sql Injections. I copied some code I used in another project but this time I can't get it to work, possibly because it's an update statement and not an Insert one, which I used before. Sorry for the boring question, but does anyone have a clue what's wrong with the syntax? Here's the original code (I changed the parameter names for clarity and security): Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) Dim strSQL As String = "Update MyTable Set " & typ & num & " = '" & pname & "' WHERE personID = " & fid Dim cmd As SqlCommand = New SqlCommand(strSQL, conn) Here's the code from codebehind: Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) Dim strSQL As String = "Update MyTable Set (" & typ & num & ") Values (@" & typ & num & ") WHERE personID = " & fid Dim cmd As New SqlCommand(strSQL, conn) With cmd.Parameters .Add(New SqlParameter("@" & typ & num, pname)) End With TestLabel.Text = strSQL & " " & pname cmd.Connection.Open() cmd.ExecuteNonQuery() cmd.Connection.Close() Here's my test message; first the sql, then the new string to be inserted: Update MyTable Set (picturename) Values (@pname) WHERE personID = 2 2_adin.jpg Here's my error code: Line 1: Incorrect syntax near '('. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '('. Source Error: Line 489: TestLabel.Text = strSQL & " " & pnameLine 490: cmd.Connection.Open()Line 491: cmd.ExecuteNonQuery()Line 492: cmd.Connection.Close()Line 493: End If I could understand it if I'd get an error in VWD Express for using dynamic variables, but they work correctly in the text message so I'm clueless. Any help is deeply appreciated! Pettrer (VB, Sql Server 2000)
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t Set CASE **WHEN Isnull(td.IsPosted, 0) = 0 THEN t.AODYD = td.ODYD** *ELSE t.DAODYD = td.ODYD* END From #ReportData As t Join @CIR AS tmp On t.RowId = tmp.Max_RowId
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error: Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18 Incorrect syntax near the keyword 'external'. CREATE FUNCTION clrDynamicPivot ( -- Add the parameters for the function here @query nvarchar(4000), @pivotColumn nvarchar(4000),
I have a web page where the user can select the language (FR, EN, BG, ...) in a drop down list.
Next to the drop down list there is a text box where user can type the some text (translation).
User can add several description
On my web page, i have a button this button collect all information create an xml file and save all in database (sql server 2008)
that's work fine for some language => FR, EN and so on
But for bulgarian (bulgare) and greece there are some problem...
Some characters when i display it in sql look like => ???s??. ? d??ta?? a?t?
The value encoded by the user is => Ένωσης. Η διάταξη αυτή
but the result after t sql xpath is => ???s??. ? d??ta?? a?t?
Here is it my sql code where you can find my temporary table and my xml file and my xpath query
declare @tblTranslation table (idDocID int, languageID varchar(10), value varchar(500)) declare @Translations XML
set @Translations = '<?xml version="1.0" ?><Items><Item><eleKey>EN</eleKey><eleValue>This is a test</eleValue></Item><Item><eleKey>FR</eleKey><eleValue>test</eleValue></Item><Item><eleKey>BG</eleKey><eleValue>Ένωσης. Η διάταξη αυτή</eleValue></Item><Item><eleKey>HR</eleKey><eleValue></eleValue></Item><Item><eleKey>RO</eleKey><eleValue></eleValue></Item></Items>'
-- 2) fill the temporary table with information from the xml file
INSERT INTO @tblTranslation(idDocID, languageID, value) SELECT 1 , Convert(nvarchar(max), i.query('eleKey/text()')) as colKey , Convert(nvarchar(max), i.query('eleValue/text()')) as colValue -- FROM @translations.nodes('/Items/Item') as x(i) SELECT * FROM @tblTranslation
I'm trying to do a simple insert into a table, something like this:
insert into sometable (ID, somecolumn) select 'Task-ID', somevalue from SomeOtherTable where something = 'someothervalue' (or something to that effect)
So, the SELECT would generate something that looks like this:
What I don't know is, how do I programatically generate the number sequence? Note: I do not have admin rights to the table, i.e. I cannot just change a column to IDENTITY.
Also the 'Task-ID' must remain part of the ID; in other words, I can't just generate a GUID, and it needs to be easily identifiable.
What I'm hoping to do is rewrite my SQL like this:
insert into sometable (ID, somecolumn) select 'Task-ID.' + (generated seq #), somevalue from SomeOtherTable where something = 'someothervalue'
I have been trying to the query optimizer to generate a parallel execution plan but no matter the MaxDOP (0) or Cost Threshold (5) settings I use it will only execute in serial.
UPDATE [dbo].[Targus_201412_V7_B] SET [URBAN] =( CASE WHEN [METRO_STATUS] = 'Urban' THEN 1 ELSE 0 END)
I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week.Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server e.g,
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'
I want to grab the resultant pivoted table back into excel. how to do it?
USE [Database_ABC] GO DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'
I want to export the data into multiple sheets with same template, all the worksheets have to split dynamically with specific Sheet Name and template also copied to all other sheets
For Example:
Sheet Name: Guru Name Age Guru 24 Sheet Name: Johnson
I would like to generate a working schedule for employees for x-days ahead based on a starting date that the user can enter.
I have got 3 relevant tables:
1. Table X with (1) resourcenumber, (2) starting date working schedule and (3) the daynumber representing the starting date (this is ISO so 1 for Monday, 2 for Tuesday etc.)
2. Table Y has the schedule itself and can hold a 7-days schedule or a 14-days schedule. In case of 7 days schedule there a 14 (!) records with (1) resourcenumber, (2) daynumber, (3) starting hour a.m. (4) ending hour a.m (5) starting hour p.m and (6) ending hour p.m. In case of a 14-days schedule there are 28 records (a.m. and p.m. records)
3. Table Z with resource data.
An example to clarify (for fake employee 100):
Table X: Resource: 100 Starting date: 2012-03-01 (from this date the schedule will be effective) Daynumber: 4 (2012-03-01 was a Thursday)
Table Y (Resource has a 14 days schedule because per 2 weeks Monday is an off-day):
Record 1 shows: Resource: 100, Daynumber: 1 (= Monday, working day), AM-Starting hour: 09:00, AM-Ending hour: 13:00, PM-starting hour: 13:30, PM-ending hour: 17:30 Record 2: same but daynumber is 2 Record 3: same but daynumber is 3 etc. ... Record 8 shows: Resource: 100, Daynumber: 8 (= Monday, off-day), AM-Starting hour: 00:00, AM-Ending hour: 00:00, PM-starting hour: 00:00, PM-ending hour: 00:00 Record 9: same as record 2 but daynumber is 9. etc. ... Record 14: same as record 7 but day is 14 (= last day)
The weekend days show as 00:00 for the hours (same as day 8 in example)
I generated the working schedule with a CROSS APPLY function based on the starting date and the x-number of days ahead.
I then evaluate the actual daynumber corresponding with that date with the daynumber in table Y. That works fine with a 7-days schedule but I can't get it fixed with a 14-days schedule. Day 8 in that schedule represents an actual day 1 but how do I know what actual date day 8 is ... I think I have to start with the starting date in table X ...
I think ideally I would like to have the generated days as follows (as an example in case of a 14-days schedule starting 2014-05-01 for 30 days ahead):
2014-05-01 = day 4 (= actual daynumber) 2014-05-02 = day 5 2014-05-03 = day 6 ... 2014-05-10 = day 13 2014-05-11 = day 14 2014-05-12 = day 1 2014-05-13 = day 2 2014-05-14 = day 3 ... 2014-05-24 = day 13 2014-05-25 = day 14 2014-05-26 = day 1 2014-05-27 = day 2 ... 2014-05-31 = day 6
With this done I can compare the actual daynumber with the daynumber in Table Y.
The rownumber that the CROSS APPLY function generates has to be reset to 1 after day 14. I tried PARTITION BY in THE ROW_NUMBER function but to no avail ... The only field I can partition by is the maximum value of the daynumber (14 is the example) but that is not allowed in the rownumber function.
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.
Using the examples given in Books Online returns compilation errors. See below.
Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?
James
-- SQL ---------------
EXEC SQL BEGIN DECLARE SECTION; char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?"; char szLastName[] = "White"; char szFirstName[30]; EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE author_cursor CURSOR FOR select_statement;
EXEC SQL PREPARE select_statement FROM :szCommand;
EXEC SQL OPEN author_cursor USING :szLastName; EXEC SQL FETCH author_cursor INTO :szFirstName;
--Error-------------------- Server: Msg 170, Level 15, State 1, Line 23 Line 23: Incorrect syntax near ';'. Server: Msg 1038, Level 15, State 1, Line 24 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 1038, Level 15, State 1, Line 25 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 170, Level 15, State 1, Line 27 Line 27: Incorrect syntax near ';'. Server: Msg 170, Level 15, State 1, Line 30 Line 30: Incorrect syntax near 'select_statement'. Server: Msg 170, Level 15, State 1, Line 33 Line 33: Incorrect syntax near 'select_statement'. Server: Msg 102, Level 15, State 1, Line 35 Incorrect syntax near 'author_cursor'. Server: Msg 170, Level 15, State 1, Line 36 Line 36: Incorrect syntax near ':'.
My problem is one of my query is returing party_codes . now i also want a column which returns serial numbers along with it . the serial numbers are not stored anywhere. they should be autogenerated. My query is combining two different databases and its using union in it so i can not use count in it . Is there any other way i can acheive . for eg Now my query output is party_code ---------- R06048 R06600 R06791 (3 row(s) affected) I want it like party_code serial number ---------- ------------- R06048 1 R06600 2 R06791 3 (3 row(s) affected) The serial number column should be auto generated in the select statement it self Is there any system rowid i can use . Please suggest
I have seen in Enterprise manager there is a toll that can script the all tables in a database, but nothing that can generate the insert statements for all the rows in each table in a secified database.
Does any one know of a application, plug in, script that can generate the insert statments for all the tables in a database?
I am writing dynamic sql to create a work table. I would like to format my create script such that the data types are all lined up, rather than one space behind the column name. I tried something like this:
That is, I was trying to put the data type 60 spaces to the right, no matter how long the column name is (as long as it's less than 60 characters). I kept getting an error telling me it couldn't concatenate a character to an int.
Hi all,I have a problem trying to generate the u/m list of customer.I am trying to generate a list of customer whoes last commence date isjan 04 to current.It is part of a billing system which the customer come in and pay fortheir season parking in carpark. They can pay for various periodshortest being 1 week.so i will have customer paying for 1 week, 1 month, 2 months or even 1year. Everytime a customer come in to pay, a new line at the incvoicewill be generated.My DB structure is as followedCustomer Table-Cust_Acc_No ----> (Primary Key)-Customer Name-Customer AddressInvoiceCust_acc_no-- Link to customer tableInvoice_no -- primary keyinvoice detailsinvoice_no -- link to invoice tablecommence_dateexpiry_dateamount_paidif i do aselect * from customer a, invoice b, invoice_details cwhere a.cust_acc_no=b.cust_acc_no andb.invoice_no=c.invoice_no andc.commence_date>1/1/04it doesnt works as it will showjohn, 1/1/04 - 31/1/04john, 1/2/04 - 29/1/04i do not want repetitive customer number just the latest commencedate.can anyone help?thanks
Not sure if this is ever going to be possible, but I'm trying to do a dynamic join on EXECUTE, but the execution string has to come from a table column.... i.e
select * from (select table1.theSql,table1.userid from table1 ) as a
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
