Posted: 01 Apr 2013 01:11 AM PDT

I need to deliver a large number of code objects (views and functions, not stored procedures) to our customers via a website. Due to dependencies of these objects, we want to have the script create these objects in a specific order. We also want to include error handling and success/failure reporting (as well as possible). Many of these code objects create statements are lengthy and contain many literals and concatenations.Given these "specifications", as well as the limitations of creating code objects and error handling within T-SQL, I believe my choices are limited to 1) using dynamic SQL to create the objects, thereby allowing better error handling and "reporting" , or 2) plain coding, thereby allowing much easier coding, readability, and maintenance.If anyone has another method that might work given the "specs", or a clever work-around for this, please let me know.Thanks,Jeff

Looking for a query to return rows in tableA that are inclusive of ID in tableB

Posted: 31 Mar 2013 11:52 PM PDT

I am looking for a query to get all records from tableA that exists in tableb inclusive.tableB contains the combination of ID that must all exists in table A in order to be returned. Query should return 1, 'Yes1' 2, 'Yes1'99, 'Yes1'2, 'Yes2'99, 'Yes2'30, 'Yes3'Would like to avoid dynamic queries if possible. CREATE TABLE [dbo].[tableA]( [A_ID] [int] NOT NULL, [A_text] varchar(6) NOT NULL) --these records should be returned by queryInsert tableA SELECT 1, 'Yes1'Insert tableA SELECT 2, 'Yes1'Insert tableA SELECT 99, 'Yes1'Insert tableA SELECT 2, 'Yes2'Insert tableA SELECT 99, 'Yes2'Insert tableA SELECT 30, 'Yes3'--query should not return these records because not all records are in Table BInsert tableA SELECT 1, 'No1'Insert tableA SELECT 99, 'No1'Insert tableASELECT 99, 'No2'--these have no records in tableB and should not be returnedInsert tableA SELECT 100, 'No3'Insert tableA SELECT 83, 'No4'Insert tableASELECT 19, 'No5'CREATE TABLE [dbo].[tableB]( [B_ID] [int] NOT NULL, [A_ID] [int] NOT NULL CONSTRAINT [PK_tableb] PRIMARY KEY CLUSTERED ( [B_ID] ASC, [A_ID] ASC ) ) insert tableBSelect 1, 1insert tableBSelect 1, 2insert tableBSelect 1, 99insert tableBSelect 2, 99insert tableBSelect 2, 2insert tableBSelect 3, 30insert tableBSelect 4, 30insert tableBSelect 4, 35insert tableBSelect 5, 31insert tableBSelect 5, 32insert tableBSelect 5, 33insert tableBSelect 5, 34insert tableBSelect 5, 35insert tableBSelect 6, 300insert tableBSelect 7, 300insert tableBSelect 7, 100

Need to unpivot a table

Posted: 31 Mar 2013 06:43 PM PDT

[b]Hello,I have a table like this[/b]CREATE TABLE [temp]( [Country] [varchar](200) NULL, [Country_Group] [varchar](200) NULL, [Year] [varchar](200) NULL, [R1_TV] [decimal](38, 2) NULL, [R2_TV] [decimal](38, 2) NULL, [R3_TV] [decimal](38, 2) NULL, [R1_Google] [decimal](38, 2) NULL, [R2_Google] [decimal](38, 2) NULL, [R3_Google] [decimal](38, 2) NULL, [R1_Yahoo] [decimal](38, 2) NULL, [R2_Yahoo] [decimal](38, 2) NULL) ON [PRIMARY][b] and data is following[/b]INSERT [temp] ([Country], [Country_Group], [Year], [R1_TV], [R2_TV], [R3_TV], [R1_Google], [R2_Google], [R3_Google], [R1_Yahoo], [R2_Yahoo]) VALUES (N'Greece', N'Test111', N'2011', CAST(3304.24 AS Decimal(38, 2)), CAST(5.56 AS Decimal(38, 2)), CAST(30378.95 AS Decimal(38, 2)), CAST(568.23 AS Decimal(38, 2)), CAST(4.25 AS Decimal(38, 2)), CAST(56458.00 AS Decimal(38, 2)), CAST(2456.00 AS Decimal(38, 2)), CAST(6.20 AS Decimal(38, 2)))INSERT [temp] ([Country], [Country_Group], [Year], [R1_TV], [R2_TV], [R3_TV], [R1_Google], [R2_Google], [R3_Google], [R1_Yahoo], [R2_Yahoo]) VALUES (N'Ukraine', N'Test111', N'2011', CAST(792.12 AS Decimal(38, 2)), CAST(6.11 AS Decimal(38, 2)), CAST(32391.28 AS Decimal(38, 2)), CAST(456.14 AS Decimal(38, 2)), CAST(6.69 AS Decimal(38, 2)), CAST(78562.00 AS Decimal(38, 2)), CAST(3652.00 AS Decimal(38, 2)), CAST(736.00 AS Decimal(38, 2)))INSERT [temp] ([Country], [Country_Group], [Year], [R1_TV], [R2_TV], [R3_TV], [R1_Google], [R2_Google], [R3_Google], [R1_Yahoo], [R2_Yahoo]) VALUES (N'ZZZZZZZZZZ', N'Test111', N'2011', CAST(2007.91 AS Decimal(38, 2)), CAST(5.66 AS Decimal(38, 2)), CAST(62770.22 AS Decimal(38, 2)), CAST(785.25 AS Decimal(38, 2)), CAST(3.21 AS Decimal(38, 2)), CAST(35412.00 AS Decimal(38, 2)), CAST(5214.00 AS Decimal(38, 2)), CAST(3.12 AS Decimal(38, 2)))[b]Now I want to Unpivot it like this[/b]CREATE TABLE [tt]( [Country_Group] [varchar](200) NULL, [Country] [varchar](200) NULL, [Year] [varchar](200) NULL, [Media] [nvarchar](4000) NULL, [R1] [decimal](38, 2) NULL, [R2] [decimal](38, 2) NULL, [R3] [decimal](38, 2) NULL) ON [PRIMARY]INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'Ukraine', N'2011', N'TV', CAST(792.12 AS Decimal(38, 2)), CAST(6.11 AS Decimal(38, 2)), CAST(32391.28 AS Decimal(38, 2)))INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'Ukraine', N'2011', N'Google', CAST(456.14 AS Decimal(38, 2)), CAST(6.69 AS Decimal(38, 2)), CAST(78562.00 AS Decimal(38, 2)))INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'Ukraine', N'2011', N'Yahoo', CAST(3652.00 AS Decimal(38, 2)), CAST(736.00 AS Decimal(38, 2)), CAST(0.00 AS Decimal(38, 2)))INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'Greece', N'2011', N'TV', CAST(792.12 AS Decimal(38, 2)), CAST(6.11 AS Decimal(38, 2)), CAST(32391.28 AS Decimal(38, 2)))INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'Greece', N'2011', N'Google', CAST(456.14 AS Decimal(38, 2)), CAST(6.69 AS Decimal(38, 2)), CAST(78562.00 AS Decimal(38, 2)))INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'Greece', N'2011', N'Yahoo', CAST(3652.00 AS Decimal(38, 2)), CAST(736.00 AS Decimal(38, 2)), CAST(0.00 AS Decimal(38, 2)))INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'ZZZZZZZZZZ', N'2011', N'TV', CAST(792.12 AS Decimal(38, 2)), CAST(6.11 AS Decimal(38, 2)), CAST(32391.28 AS Decimal(38, 2)))INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'ZZZZZZZZZZ', N'2011', N'Google', CAST(456.14 AS Decimal(38, 2)), CAST(6.69 AS Decimal(38, 2)), CAST(78562.00 AS Decimal(38, 2)))INSERT [tt] ([Country_Group], [Country], [Year], [Media], [R1], [R2], [R3]) VALUES (N'Test111', N'ZZZZZZZZZZ', N'2011', N'Yahoo', CAST(3652.00 AS Decimal(38, 2)), CAST(736.00 AS Decimal(38, 2)), CAST(0.00 AS Decimal(38, 2)))[b]Please help[/b]

Sql samples, sql tips

Monday, April 1, 2013

[T-SQL] Creating code objects within a single script

[T-SQL] Creating code objects within a single script

No comments:

Post a Comment

Search This Blog