drew.. (5/18/2011)

---

Hi skcadavre

I've just had a quick look. In the conditional split you are having to hardcode the condition, i.e. Dept = 'BI'. So I would be manually working out...

Jason-299789 (5/18/2011)

---

Paula

you may have a problem with the way you are stripping the start of the postal code, especially those that only have a single letter and single digit...

Lookup "Conditional Split".

http://msdn.microsoft.com/en-us/library/ms137886.aspx

http://www.bimonkey.com/2009/06/the-conditional-split-transformation/

http://sqlblog.com/blogs/andy_leonard/archive/2009/02/20/ssis-expression-language-and-the-conditional-split-transformation.aspx

Not sure as to your reasons, but here is the corrected syntax.

BEGIN TRANSACTION

UPDATE a

SET a.workarea = Substring(REPLACE(b.postcode, ' ', ''), 1, 4)

FROM ctproperty a

LEFT OUTER JOIN ctproperty b ON a.property_ref...

??

--INSERT INTO tbl1

SELECT f1, f2

FROM tbl2

GROUP BY f1, f2

Firstly, could do with DDL and sample data 🙂

e.g.

--==Sample Data, please check this is correct==-

DECLARE @OrderItem AS TABLE(Id INT, Number INT, Part INT, Ticket INT)

DECLARE @Order AS TABLE(Number INT IDENTITY,...

InfiniteError (5/16/2011)

---

Thanks to all that reply to this post.

Yes you are correct. I need to...

Probably a much more elegant solution that this, but here you go: -

--==Sample Data==--

DECLARE @TABLE AS TABLE (pkey INT IDENTITY, col1 INT, col2 INT, col3 INT, col4 INT)

INSERT INTO @TABLE...

Something like this?

SELECT REPLACE(c.name, '@', '') AS parameter, t.name AS typename,

Object_name(c.object_id) AS stored_procedure

FROM sys.parameters c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id AND parameter_id > 0

WHERE t.name = 'varchar'

--==First, lets knock up some sample data==--

DECLARE @TABLE AS TABLE(number VARCHAR(50))

INSERT INTO @TABLE (number)

SELECT TOP 250000

CONVERT(VARCHAR(50),FLOOR(RAND(CHECKSUM(NEWID())) * 20000)+1)

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

--==Query==-

SELECT number AS original,

CONVERT(MONEY,number) /...

Here's your sample data in a readily consumable format: -

--==Sample Data==--

-- check if table exists

IF OBJECT_ID('tempdb..#net_sales') IS NOT NULL

DROP TABLE #net_sales

-- create temporary table

CREATE TABLE #net_sales(

[dic_company_key] [tinyint] NULL,

[dim_customer_key] [int] NULL,

[financial_period]...

Mr Quillz (5/16/2011)

---

Hi Guys,

I'm not a big fan of using sub-queries unless it is absolutely necessary to do so. I've amended the last part of skcadavre's solution (I hope you...

DECLARE @Student AS TABLE (stud_code INT IDENTITY, given_name VARCHAR(30), surname VARCHAR(30), year_group INT)

DECLARE @StudAbsent AS TABLE (stud_code INT, absent_date DATETIME)

INSERT @Student (given_name, surname, year_group)

SELECT 'Bob', 'Bobson', 7

UNION ALL SELECT 'Tim',...

Some options: -

DECLARE @TABLE AS TABLE (ID INT IDENTITY, Name CHAR(1))

INSERT INTO @TABLE (Name)

SELECT TOP 250000

CHAR(ROUND(RAND(CHECKSUM(NEWID())) * 180,2))

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

--Keep smallest ID

SELECT MIN(ID) AS ID,...

I generally use an SSIS package with the following to generate the back-up queries: -

SELECT

'BACKUP DATABASE [' + name + '] TO [' + name + '] WITH...