I am attempting to insert columns from a temp table and want the ID column to increment sequentially. I'm using the following code but nothing is getting inserted: INSERT INTO TestDataTable SELECT Code, Description, ParentID FROM TempTable Kindly tell me how I can get this to work. ...
What is result difference between: RIGHT JOIN and RIGHT OUTER JOIN LEFT JOIN and LEFT OUTER JOIN ? Can you please explain through the examples ? ...
I have the following trigger but need ti find the identity of the row so I don't update all records in the table. How can I get the identity of the affected row? BEGIN UPDATE tb_Division SET LastModified = GetDate() WHERE "id of inserted/updated row" END ...
Lets say we have a table "Names": ID Name Surname 1 Matt Smith 2 John Doe How would you write some SQLXML to generate this: <people> <person> <name>Matt</name> <surname>Smith</surname> <person> <person> <name>John</name> <surname>Doe</surname> <person> </people> The best I've got is this:...
While doing create table #tmpsr( srid int, W_DiffOriginal decimal(12,2) ) insert into #tmpsr (srid,W_DiffOriginal) select sr_id, --- From Table W_DiffOriginal=DiffOriginal --- From Function From TBL_SR,dbo.fnc_VoucherDetails_Get(sr_id) ---Table-Valued Function Where SRdoid = 12811 --- Column i...
If you have this xml <AssetComponent name="AssetComponent4881" width="300"> <Asset id="5" type="1" /> </AssetComponent> <AssetComponent name="AssetComponent4882" width="300"> <Asset id="5" type="1" /> </AssetComponent> Is it possible to replace all the ids from 5 to 6 in one query? With this sql only one attribute at the time can...
hi, I have a simple stored procedure like so: ALTER PROCEDURE [dbo].[spList_Report] @id INT AS SET NOCOUNT ON SELECT * FROM tblProducts as products WHERE product.intID = @id I have 2 user tables: MainUser and SubUser Both tables have a foreign key column productID which is related to the primary key int...
Is there a way to prevent that a Cursor changes at runtime. If I have a cursor that iterates over all the users and meanwhile, in the processing of each user, I create some additional users, then the Cursor will also iterate over the newly created users... ...
If you have this XML: <people> <person id="1"> <firstname>John</firstname> <surname>Doe</surname> </person> <person id="2"> <firstname>Mary</firstname> <surname>Jane</surname> </person> </people> And you wanted this table: id firstname surname --- ---------- ---------- 1 John Doe 2 Mary Jane ...
So I'm trying to convert a web service that was an Oracle application to T-SQL. The fun part is I only have the web service code and no database code at all. I see there is an input parameter that contains <ROWSET><ROW NUM=\"1\"><TRANSACTIONID>123456</TRANSACTIONID></ROW></ROWSET> I'm looking through the docs for T-SQL and I can't seem...
Hi, The following query performs badly because of a full non-clustered index scan of 6.5 million records in P4FileReleases followed by a hash join. I'm looking for possible reasons the optimizer picks a scan over a seek. SELECT p4f.FileReleaseID FROM P4FileReleases p4f INNER JOIN AnalyzedFileView af ON p4f.FileRelease = ...
I am joining a table that has two record id fields (record1, record2) to a view twice--once on each record--and selecting the top 1000. The view consists of several rather large tables, and it's id field is a string concatenation of their respective Ids (this was necessary for some third party software that requires a unique ID for the ...
I am trying to call an IF statement on my trigger so it won't archive expired files. (I only want to keep files that have been deleted but have not been expired) My error is The multi-part identifier "d.ExpiryDate" could not be bound. My Code: ALTER TRIGGER [dbo].[ArchiveDB] ON [dbo].[TBL_Content] AFTER DELETE AS BEGIN...
Simply put, I have a select that will return multiple single characters, and thus won't work. Is there any way to bunch all the single characters into a single returnable string? My current slow and ugly solution: ,'('+(Select Left(max(AE_D1),1) FROM ACCESS_EVENTS WHERE LEFT(AE_D1,1) like 'W' AND replace(HR.first...
Suppose - I have the following Table Structure elementid, parentid, elementtitle, sortorder 160 0 Brand New Tutorial 1 161 160 Brand New Tutorial New Step 1 168 5 Tutorial Topic 1.1 1 171 168 Tutorial Topic 1.1.1 1 172 171 Tutorial Topic 1.1.1.1 ...
I am experiencing some strange behavior with OSQL, and would appreciate any help. I have a batch file that will copy a database field from one column to another. Here is a sample script: SET NOCOUNT ON; UPDATE Table1 SET Table1.EmailAddress = Table2.GenericField FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID WHERE GenericFiel...
I have the following SQL query: select AuditStatusId from dbo.ABC_AuditStatus where coalesce(AuditFrequency, 0) <> 0 I'm struggling a bit to understand it. It looks pretty simple, and I know what the coalesce operator does (more or less), but dont' seem to get the MEANING. Without knowing anymore information except the query above,...
Hello, I remember back in the day I would make a whole wack of nvarchar(4000) vars, check the length of them as they grew, switch them out as they filled up and then concatenate the whole mess together for the exec call. I was wondering if there was an easier way of doing it. Thanks! Edit: Code Sample, shows me screwing up the case s...
How do I round the result of matchpercent to two decimal places (%)? I'm using the following to return some results: DECLARE @topRank int set @topRank=(SELECT MAX(RANK) FROM FREETEXTTABLE(titles, notes, 'recipe cuisine', 1)) SELECT ftt.RANK, (CAST(ftt.RANK as DECIMAL)/@topRank) as matchpercent, --Round this titles.title_id...
I am writing a User defined function in TSQL trying to get the last day of the month regardless of the date input. I am struggling with this as I am a newbie. Any help will be very much appreciated. Here is my function: Alter Function dbo.FN_Get_Last_Day_in_Month2 (@FN_InputDt Datetime) Returns smalldatetime as Begin Declare @Resu...