quote:

---

Originally posted by Shamee_1321

Without the subquery below which is used for inserting values for username, the select statement is working fine. The problem is with the subquery statement below... Is there another way of writing this subquery.

SELECT aspnet_membership.Email
FROM aspnet_membership INNER JOIN
ITGC_ES_TEMP.dbo.Usuarios on Usuarios.Email = aspnet_membership.Email collate SQL_Latin1_General_CP1_CI_AS
INNER JOIN ITGC_ES_TEMP.dbo.TRUCOS ON trucos.autor = Usuarios.id_usuario)

---

SELECT NEWID() --articleid
    ,CASE T.seccion
        WHEN 'belleza' THEN '5A60C79F-A5A9-4FA2-91A6-536569DD52D1'
        WHEN 'moda' THEN '15BDADD1-E07C-44F8-889C-83C0E97FD9E8' 
        ELSE '38A6642F-DA62-4C67-8697-B2833F9BF66F'
    END -- categorythemeid
    ,T.titulo--title
    ,'0' --displayonhomepage
    ,T.fecha_mod-- startdate
    ,NULL -- enddate
    ,T.resumen-- shortdescription
    ,NULL --visual
    ,NULL --thumbnail
    ,'0'--numberofvotes
    ,'0'
    ,CASE T.publicado
        WHEN '1' THEN 'o'
        ELSE 'f'
    END -- statusid
    ,'2' -- is this always 2 becoz hints
    ,'0' --etatvalider
    ,D.EMail --username
    ,T.fecha_mod --createdate
    ,'1'--allowcomments
    ,NULL--previewhtml
    ,'1'--isuserpostedtips
    ,T.id_truco
FROM itgc_es_temp.dbo.trucos T
    LEFT JOIN
    (
        SELECT
            ROW_NUMBER() OVER (PARTITION BY U.id_usuario ORDER BY U.EMail) AS RowId
            ,U.id_usuario AS autor
            ,U.EMail
        FROM ITGC_ES_TEMP.dbo.Usuarios U
        WHERE EXISTS
        (
            SELECT *
            FROM aspnet_membership.Email M
            WHERE M.EMail COLLATE SQL_Latin1_General_CP1_CI_AS = U.EMail
        )
    ) D
        ON T.autor = D.autor
            AND D.RowId = 1
WHERE T.seccion IN ('belleza', 'moda', 'cocina')

INSERT		Article
		(
			ArticleId,
			CategoryThemeId,
			Title,
			DisplayOnHomePage,
			StartDate,
			EndDate,
			ShortDescription,
			Visual,
			Thumbnail,
			NumberOfVotes,
			Marks,
			StatusId,
			[Type],
			EtatValider,
			UserName,
			CreateDate,
			AllowComments,
			PreviewHtml,
			IsInUserPostedTips,
			ArticleIdXXX
		)
SELECT		NEWID(),--articleid
		CASE seccion
			WHEN 'belleza' THEN '5A60C79F-A5A9-4FA2-91A6-536569DD52D1'
			WHEN 'moda' THEN '15BDADD1-E07C-44F8-889C-83C0E97FD9E8' 
			WHEN 'cocina' THEN '38A6642F-DA62-4C67-8697-B2833F9BF66F'
		END, -- categorythemeid
		titulo,--title
		'0', --displayonhomepage
		fecha_mod,-- startdate
		NULL, -- enddate
		resumen,-- shortdescription
		NULL,--visual
		NULL,--thumbnail
		'0',--numberofvotes
		'0',
		CASE publicado
			WHEN '1' THEN 'o'
			WHEN '0' THEN 'f'
		END, -- statusid
		'2', -- is this always 2 becoz hints
		'0', --etatvalider
(SELECT TOP 1 aspnet_membership.Email
FROM		aspnet_membership
INNER JOIN	ITGC_ES_TEMP.dbo.Usuarios on Usuarios.Email = aspnet_membership.Email collate SQL_Latin1_General_CP1_CI_AS
INNER JOIN	ITGC_ES_TEMP.dbo.TRUCOS ON trucos.autor = Usuarios.id_usuario),--username
		fecha_mod, --createdate
		'1',--allowcomments
		NULL,--previewhtml
		'1',--isuserpostedtips
		id_truco
FROM		[itgc_es_temp].[dbo].trucos
WHERE		seccion in ('belleza', 'moda', 'cocina')