Saturday, March 30, 2013

[T-SQL] MERGE TSQL in SQL SERVER 2008

[T-SQL] MERGE TSQL in SQL SERVER 2008

MERGE TSQL in SQL SERVER 2008

Posted: 29 Mar 2013 06:34 PM PDT

Hi ALL,Need one help/suggestion in tsql using MERGE in sql server 2008.I'm using the below code to do upsert in one of our table. [code="sql"]MERGE [dbo].[Dly_Target_Comp] AS DSTUSING [dbo].[Dly_Target_Comp_Temp] AS SRCON (SRC.[DateKey] = DST.[DateKey]ANDSRC.[Wghtd_WI_Key] = DST.[Wghtd_WI_Key])WHEN NOT MATCHED THENINSERT ([DateKey], [Wghtd_WI_Key], [Item_Cd], [Metric_Desc], [Volume])VALUES (SRC.[DateKey], SRC.[Wghtd_WI_Key], SRC.[Item_Cd], SRC.[Metric_Desc], SRC.[Volume])WHEN MATCHED AND ( ISNULL(DST.[Item_Cd],'') <> ISNULL(SRC.[Item_Cd],'') OR ISNULL(DST.[Metric_Desc],'') <> ISNULL(SRC.[Metric_Desc],'') OR ISNULL(DST.[Volume],'') <> ISNULL(SRC.[Volume],'') )THEN UPDATE SET DST.[Item_Cd] = SRC.[Item_Cd] ,DST.[Metric_Desc] = SRC.[Metric_Desc] ,DST.[Volume] = SRC.[Volume];[/code]Now the requirement has changed, instead of doing update when there is a MATCH i need to do insert with the help of a new column called 'Projection_Date' . So there will same records but with different projection date.But while trying to insert when matched, it says insert not allowed in 'when matched' clause.Please advice.Thanks in advanceSam

i need the max length value of every column in every table in a database

Posted: 29 Mar 2013 07:34 AM PDT

I'm creating some dynamic sql to loop through each table and column and basically generate some dynamis sql that will get the max length of each column in each table in a database. Then store it locally into a table like the below.create table TableColLengths(id int identity(1,1), tablename varchar(255), colName varchar(255), MaxColLength bigint)I'm going to write it myself however didnt know if somewone has already done it so i dont have to go though the hassle.

harnessing sp_msforeachdb to return one result set

Posted: 29 Mar 2013 06:16 AM PDT

I found a nifty but unsupported stored procedure that loops through all databases on your server and allows you do to things to each database. I would like it to just return a list of db_users for each database. I tried executing as follows but I get a seperate result set for each loop:[code="plain"]execute sp_msforeachdb 'use[?]; SELECT name, type, type_desc FROM sys.database_principals '[/code]Can someone show how to get only one result set listing all databases with corresponding users, type, and type_desc in one result set?

please help with cursor syntax

Posted: 29 Mar 2013 03:56 AM PDT

I have a simple cursor and I need help fixing syntax.(I know that in this scenario I am using cursor properly).The purpose of the cursor (copied below) is just to output the names of all databases on my server. Later I will use dynamic sql (not copied below) to display the respective metrics (user_scans, user_lookups) for each database from sys.dm_db_index_usage_stats.For the cursor part I'm getting the following error:Msg 16915, Level 16, State 1, Line 4A cursor with the name 'getDatabaseName' already exists.Msg 137, Level 15, State 2, Line 3Must declare the scalar variable "@DatabaseName".Msg 137, Level 15, State 2, Line 6Must declare the scalar variable "@DatabaseName".Msg 137, Level 15, State 2, Line 8Must declare the scalar variable "@DatabaseName".my cursor script[code="plain"]use MiguelsAppDECLARE @DatabaseName varchar(max)DECLARE getDatabaseName CURSOR FOR SELECT name, database_id, create_dateFROM sys.databases where name not in ('master', 'tempdb', 'model', 'msdb');GOOPEN getDatabaseNameFETCH NEXTFROM getDatabaseName INTO @DatabaseNameWHILE @@FETCH_STATUS = 0BEGINprint @DatabaseNameFETCH NEXT FROM getDatabaseNameINTO @DatabaseNameENDclose getDatabaseNamedeallocate getDatabaseName[/code]Will someone please tell me where my syntax is wrong so it will run?

How to select data in a particular format

Posted: 29 Mar 2013 04:40 AM PDT

Greetings all.I have data in a view that when selected from looks like this:CertYear CertCode Quantity-------- --------- --------2008 HI-Master 22008 HI-Instr 12008 HI-Train 12008 HI-Master 32008 HI-Train 12009 HI-Master 32009 HI-Instr 4There are a few other fields that are in this view, but I left them out because they're not really relevant to this question (apart from simply knowing that there are more fields in this view, which is why the data that I have above looks redundant in nature, but isn't). I've also left out more rows (such as year 2010 and later). [b]What I need: [/b]Using a single select statement (I am not allowed to do this using a stored procedure), I would like to return a record set from this view that looks like the following:CertYear QtyMaster QtyInstr QtyTrain-------- ---------- -------- --------2008 7 2 42009 3 4 3I have a query that I've constructed which gets me part way there, but not quite. What I have is this:[code="sql"]select v.CertYear, v.CertCode , case when v.CertCode = 'HI-Master' then sum(v.Quantity) else 0 end as QtyMaster , case when v.CertCode = 'HI-Instr' then sum(v.Quantity) else 0 end as QtyInstr , case when v.CertCode = 'HI-Train' then sum(v.Quantity) else 0 end as QtyTrainfrom dbo.vCerts vgroup by v.CertYear, v.CertCodeorder by v.CertYear asc, v.CertCode asc[/code]But what this select yields (like I said, it's not quite what I want) is this:CertYear CertCode QtyMaster QtyInstr QtyTrain-------- --------- ---------- -------- --------2008 HI-Master 7 0 02008 HI-Instr 0 2 02008 HI-Train 0 0 42009 HI-Master 3 0 02009 HI-Instr 0 4 02009 HI-Train 0 0 3I tried remove the CertCode from the GroupBy and the select (except not from the case statement itself), because I thought that might get me to the record set that I want, but instead I get an error that says "Column 'dbo.vCerts.CertCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."Thanks in advance for any help on this, and sorry for the complexity involved!edit: Sorry for the data that is hard to read. I'm not sure how to get whitespacing to persist on this site (it appears to be mostly ripped out).

Alter Column with Identity

Posted: 18 Aug 2010 06:14 PM PDT

Hi All,The following one is the Table definition.After inserting some rows ...i want to add identity property to Owner_id.So please give me alter statement.CREATE TABLE [dbo].[Testowners]([owner_id] [int] NULL,[owname] [varchar](15) NULL)INSERT INTO Testowners Select 1,'asas'Select * from Testowners

Generate all possible number combinations for a provided list of numbers

Posted: 29 Mar 2013 01:17 AM PDT

Hi Experts,I want to get "all possible number combinations" for provided list of numbers.As an example, if I have 4 numbers: [b]1, 2, 3 and 4 [/b]For these numbers, I want to generate following numbers [which should be in order, so as to get unique list]:11,21,31,41,2,31,2,41,3,41,2,3,421,2 (for 2,1) [which is duplicated]2,32,41,2,3 (for 2,1,3) [which is duplicated]1,2,4 (for 2,1,4) [which is duplicated]1,2,3,4 (for 2,1,3,4 ) [which is duplicated]31,3 (for 3,1) [which is duplicated]2,3 (for 3,2) [which is duplicated]3,41,2,3 (for 3,1,2) [which is duplicated]1,3,4 (for 3,1,4) [which is duplicated]1,2,3,4 (for 3,1,2,4) [which is duplicated]41,4 (for 4,1) [which is duplicated]2,4 (for 4,2) [which is duplicated]3,4 (for 4,3) [which is duplicated]1,2,4 (for 4,1,2) [which is duplicated]1,3,4 (for 4,1,3) [which is duplicated]2,3,4 (for 4,2,3)1,2,3,4 (for 4,1,2,3) [which is duplicated]Therefore unique list of possible number combinations (which are ordered) for 1,2,3 & 4 is:1; 2; 3; 4;(1,2); (1,3); (1,4); (2,3); (2,4); (3,4);(1,2,3); (1,2,4); (1,3,4); (2,3,4)(1,2,3,4)Could this be achieved using sql? Thanks much for your help!Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog