A few Queries that are related to Agent Information
Posted: June 4, 2014 Filed under: SQL Server 2008, SQL Server 2012, SQL Server 2014 Leave a commentYou can tweak a few useful queries Datta Sindol compiled to make them useful for you if you want to get some information about agent jobs from MSDB.
Here is the link:
http://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/
Execute Stored Procedure with Parameters and Insert the Resultset Into a Table
Posted: May 9, 2014 Filed under: SQL Server 2008 Leave a comment
--Set parameters
declare @did int = 1,
@eid int =88
declare @sqltax varchar(1000);
select @sqltax = 'SET NOCOUNT ON;SET FMTONLY OFF; EXEC [mydb].[dbo].[getMytaxes]
@did = '+cast(@did as varchar(15))
+', @eid = '+cast(@eid as varchar(15))
EXEC ('SELECT * INTO mytaxes FROM OPENROWSET(''SQLNCLI'', ''Server=MC12\MSSQL08R2;Trusted_Connection=yes;'',''' + @sqltax + ''') AS t;')
;
select eid, sum(tax) as taxtotal
into mytaxes2
from mytaxes
group by eid
Drop table mytaxes
Drop table mytaxes2
How to SUM odd and even digits ina big number with T-SQL
Posted: April 17, 2014 Filed under: SQL Server 2008 Leave a comment
create table test (number varchar(50))
Insert into test values('196421744643'),('12121212121212')
select number, SUM(Case WHEN num%2 =1 then Cast(substring(number,num,1) as int) end) OddSum,
SUM(Case WHEN num%2 =0 then Cast(substring(number,num,1) as int) end) EvenSum
from test
cross apply (Values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) d(num)
WHERE substring(number,num,1)<>''
Group by number
drop table test
Script DBMail Profiles
Posted: April 14, 2014 Filed under: SQL Server 2005, SQL Server 2008 Leave a commentUser Lowell posted a script to script out DBMail’s profile at this thread:
http://www.sqlservercentral.com/Forums/Topic982618-391-1.aspx
I include the script here for future reference:
USE msdb
GO
Declare @TheResults varchar(max),
@vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults + '
--#################################################################################################
-- BEGIN Mail Settings ' + p.name + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')
BEGIN
--CREATE Profile [' + p.name + ']
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ''' + p.name + ''',
@description = ''' + ISNULL(p.description,'') + ''';
END --IF EXISTS profile
'
+
'
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
BEGIN
--CREATE Account [' + a.name + ']
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ' + CASE WHEN a.name IS NULL THEN ' NULL ' ELSE + '''' + a.name + '''' END + ',
@email_address = ' + CASE WHEN a.email_address IS NULL THEN ' NULL ' ELSE + '''' + a.email_address + '''' END + ',
@display_name = ' + CASE WHEN a.display_name IS NULL THEN ' NULL ' ELSE + '''' + a.display_name + '''' END + ',
@replyto_address = ' + CASE WHEN a.replyto_address IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address + '''' END + ',
@description = ' + CASE WHEN a.description IS NULL THEN ' NULL ' ELSE + '''' + a.description + '''' END + ',
@mailserver_name = ' + CASE WHEN s.servername IS NULL THEN ' NULL ' ELSE + '''' + s.servername + '''' END + ',
@mailserver_type = ' + CASE WHEN s.servertype IS NULL THEN ' NULL ' ELSE + '''' + s.servertype + '''' END + ',
@port = ' + CASE WHEN s.port IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ',
@username = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity + '''' END + ',
@password = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ',
@use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
@enable_ssl = ' + CASE WHEN s.enable_ssl = 1 THEN ' 1 ' ELSE ' 0 ' END + ';
END --IF EXISTS account
'
+ '
IF NOT EXISTS(SELECT *
FROM msdb.dbo.sysmail_profileaccount pa
INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE p.name = ''' + p.name + '''
AND a.name = ''' + a.name + ''')
BEGIN
-- Associate Account [' + a.name + '] to Profile [' + p.name + ']
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ''' + p.name + ''',
@account_name = ''' + a.name + ''',
@sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ;
END --IF EXISTS associate accounts to profiles
--#################################################################################################
-- Drop Settings For ' + p.name + '
--#################################################################################################
/*
IF EXISTS(SELECT *
FROM msdb.dbo.sysmail_profileaccount pa
INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
WHERE p.name = ''' + p.name + '''
AND a.name = ''' + a.name + ''')
BEGIN
EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + '''
END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
BEGIN
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + '''
END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')
BEGIN
EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + '''
END
*/
'
FROM msdb.dbo.sysmail_profile p
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
LEFT OUTER JOIN msdb.dbo.sysmail_server s ON a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials c ON s.credential_id = c.credential_id
;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows
E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows
E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows
E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows
--E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,
Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),
ItemSplit(
ItemOrder,
Item
) as (
SELECT N,
SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))
FROM Tally
WHERE N < DATALENGTH(@vbCrLf + @TheResults)
--WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf
AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter
)
select
row_number() over (order by ItemOrder) as ItemID,
Item
from ItemSplit
Find Broken Loop COnnection from Two Columns
Posted: April 11, 2014 Filed under: SQL Server 2005, SQL Server 2008 Leave a commentCreate table Sample ( Rec_Id Int Not null, Name varchar(30) null, ID1 varchar(10) null, ID2 varchar(10) null, CONSTRAINT [PK_Sample] PRIMARY KEY NONCLUSTERED ( [Rec_Id] ASC )) Insert into sample (Rec_ID,Name,ID1,ID2) values ( 1 ,'A', 'A1X31','A2XX1') Insert into sample (Rec_ID,Name,ID1,ID2) values ( 2 ,'A', 'A2XX1','B2C45') Insert into sample (Rec_ID,Name,ID1,ID2) values ( 3 ,'A', 'B2C45' ,'A1X31') --> No need to display these 3 records becoz they form one loop( 1st row ID1='A1X31' value matches 3rd row ID2 ='A1X31'value) Insert into sample (Rec_ID,Name,ID1,ID2) values ( 4 ,'B', 'B1Y35' , 'B2Y15') Insert into sample (Rec_ID,Name,ID1,ID2) values ( 5 , 'B', 'B2Y15', 'B5Y13') Insert into sample (Rec_ID,Name,ID1,ID2) values ( 6 , 'B' , 'B5Y13' , null) --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='B1Y35' value doesn't matches 3rd row ID2 = null value) Insert into sample (Rec_ID,Name,ID1,ID2) values ( 7 ,'C', 'C5Z19' , 'C5B12') Insert into sample (Rec_ID,Name,ID1,ID2) values ( 8 ,'C' , 'C5B12' , 'C5JPQ') Insert into sample (Rec_ID,Name,ID1,ID2) values ( 9 ,'C' , 'C5JPQ' , 'C3JPQ') Insert into sample (Rec_ID,Name,ID1,ID2) values (10 ,'C' , 'C6JPQ' , 'C5Z19') --> Need to display this 4th record becoz it is broken loop ( 3rd row ID2='C3JPQ' value doesn't matches 4th row ID1 = 'C6JPQ' value) Insert into sample (Rec_ID,Name,ID1,ID2) values ( 11 ,'D', 'D5Z16' , 'D5Z17') Insert into sample (Rec_ID,Name,ID1,ID2) values ( 12 , 'D', 'D5Z17', 'D5Z18') Insert into sample (Rec_ID,Name,ID1,ID2) values ( 13 , 'D' , 'D5Z18' , 'D5Z17') --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='D5Z16' value doesn't matches 3rd row ID2 = 'D5Z17' value) ;with mycte as (select Rec_Id,name, id1, id2 ,ROW_NUMBER() over(partition by name order by Rec_Id) as rn1 ,ROW_NUMBER() over(partition by name order by Rec_Id DESC) as rn2 from sample ) , mycte1 as( select m0.Rec_id,m0.name, m0.id1, m0.id2 , Coalesce (m1.id2, m2.id2,'') id2_2, m0.rn2 , Sum(Case when m0.id1=Coalesce (m1.id2, m2.id2,'') Then 0 Else 1 END) Over(Partition By m0.Name) as cnt from mycte m0 Left Join mycte m1 On m0.name =m1.name and m0.rn1=m1.rn1+1 Left Join mycte m2 On m0.name =m2.name and m0.rn2=m2.rn1 ) Select Rec_id,name, id1,id2 from mycte1 WHERE rn2=1 AND cnt>0 Order by Rec_id /* Rec_id name id1 id2 6 B B5Y13 NULL 10 C C6JPQ C5Z19 13 D D5Z18 D5Z17 */ drop table sample
Merge Update Sample
Posted: April 9, 2014 Filed under: SQL Server 2008 Leave a commentCreate table MinuteCategory([Minute] int, MinuteCategory varchar(50)) Insert into MinuteCategory values (1,'a1'),(33,'a1'),(65,'a1') Create table MinuteLookup( MinuteCategoryId int identity(1,1), MinuteLow int, MinuteHigh int, MinuteCategory varchar(50)) Insert into MinuteLookup values(1,15,'<15min'),(16,30,'<30 min'),(31,45,'<45 min'),(46,60,'<60 minutes'),(61,75,'<1 hour 15 minutes') Merge MinuteCategory mc Using MinuteLookup ml on mc.[Minute] between ml.MinuteLow and ml.MinuteHigh When Matched Then UPDATE SET MinuteCategory = ml.MinuteCategoryId; select * from MinuteCategory mc inner join MinuteLookup ml on mc.MinuteCategory = ml.MinuteCategoryId ----Clean up drop table MinuteCategory,MinuteLookup
How to select a max row for each group in SQL
Posted: April 7, 2014 Filed under: SQL Server 2008 Leave a comment
CREATE TABLE [dbo].[test1]([Country] [varchar](4) NULL, [grpid] [int] NOT NULL, [Value] [int] NULL, [Row_num] [int] NULL)
INSERT [dbo].[test1] VALUES (N'US', 49707, 604456458, 1)
, (N'US', 909, 604456458, 2),(N'US', 231, 604456457, 3)
, (N'GB', 5086, 497654945, 4), (N'GB', 50147, 405759433, 5),(N'GB', 909, 405759433, 6),(N'GB', 231, 405759433, 7)
, (N'CA', 49707, 353500201, 8), (N'CA', 5086, 353500201, 9),(N'CA', 909, 353500201, 10),(N'CA', 231, 353500201, 11)
, (N'JP', 49707, 198291290, 12),(N'JP', 5086, 198291290, 13), (N'JP', 909, 198291290, 14)
,(N'JP', 231, 198291290, 15)
, (N'MX', 49707, 181884714, 16), (N'MX', 909, 181884714, 17)
,(N'MX', 231, 181884714, 18),(N'BR', 49707, 105147054, 19), (N'BR', 5086, 105147054, 20),(N'BR', 909, 105147054, 21)
, (N'AR', 49707, 94774929, 22), (N'AR', 5086, 94774929, 23), (N'AR', 909, 94774929, 24),(N'AR', 231, 94774929, 25)
, (N'ZA', 49707, 84560514, 26),(N'ZA', 909, 84560514, 27),(N'ZA', 231, 84560514, 28), (N'CN', 49707, 68966682, 29)
,(N'CN', 909, 68966682, 30),(N'CN', 231, 68966682, 31),(N'CB', 49707, 65020665, 32)
, (N'CB', 5086, 65020665, 33), (N'CB', 909, 65020665, 34),(N'CB', 231, 65020665, 35)
,(N'GT', 49707, 42016510, 36), (N'GT', 5086, 42016510, 37),(N'GT', 909, 42016510, 38), (N'CR', 49707, 37116544, 39)
,(N'CR', 5086, 37116544, 40), (N'CR', 909, 37116544, 41),(N'CR', 231, 37116544, 42), (N'SV', 49707, 31103322, 43)
, (N'SV', 5086, 31103322, 44), (N'SV', 909, 31103322, 45),(N'NI', 49707, 17415843, 46),(N'NI', 5086, 17415843, 47)
, (N'NI', 909, 17415843, 48), (N'HN', 49707, 12516273, 49),(N'HN', 5086, 12516273, 50),(N'HN', 909, 12516273, 51)
GO
/*
Country grpid Value Row_num
US 49707 604456458 1
GB 5086 497654945 4
CA 909 353500201 10
JP 231 198291290 15
*/
--source table with a generated row number based on value and grpid
;with mytest as (
select grpid,country,value , Row_num, row_number() over( Order by value DESC, grpid DESC) rn from test1
)
,mycte1 as (select grpid,country,value, Row_num, row_number() over(Order by value DESC, grpid DESC) rn
from mytest a where not exists(select 1 from mytest b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte2 as (select grpid,country,value , Row_num, row_number() over( Order by value DESC, grpid DESC) rn
from mycte1 a where not exists(select 1 from mycte1 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte3 as (select grpid,country,value , Row_num, row_number() over( Order by value DESC, grpid DESC) rn from mycte2 a
where not exists(select 1 from mycte2 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
--Add more
--,mycte4 as (select grpid,country,value , Row_num, row_number() over( Order by value DESC, grpid DESC) rn
-- from mycte3 a where not exists(select 1 from mycte3 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
--,mycte5 as (select grpid,country,value , Row_num, row_number() over( Order by value DESC, grpid DESC) rn from mycte4 a
--where not exists(select 1 from mycte4 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
--Final combine all
select grpid,country,value , Row_num from mytest WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte1 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte2 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte3 WHERE rn=1
----Add more
--Union ALL
--select grpid,country,value , Row_num from mycte4 WHERE rn=1
--Union ALL
--select grpid,country,value , Row_num from mycte5 WHERE rn=1
drop table test1
--Early version
CREATE TABLE [dbo].[test1](
[Country] [varchar](4) NULL,
[grpid] [int] NOT NULL,
[Value] [int] NULL,
[Row_num] [int] NULL
)
INSERT [dbo].[test1] VALUES (N'US', 49707, 604456458, 1)
, (N'US', 909, 604456458, 2),(N'US', 231, 604456457, 3)
, (N'GB', 5086, 497654945, 4), (N'GB', 50147, 405759433, 5),(N'GB', 909, 405759433, 6),(N'GB', 231, 405759433, 7)
, (N'CA', 49707, 353500201, 8), (N'CA', 5086, 353500201, 9),(N'CA', 909, 353500201, 10),(N'CA', 231, 353500201, 11)
, (N'JP', 49707, 198291290, 12),(N'JP', 5086, 198291290, 13), (N'JP', 909, 198291290, 14),(N'JP', 231, 198291290, 15)
, (N'MX', 49707, 181884714, 16), (N'MX', 909, 181884714, 17)
,(N'MX', 231, 181884714, 18),(N'BR', 49707, 105147054, 19), (N'BR', 5086, 105147054, 20),(N'BR', 909, 105147054, 21)
, (N'AR', 49707, 94774929, 22), (N'AR', 5086, 94774929, 23), (N'AR', 909, 94774929, 24),(N'AR', 231, 94774929, 25)
, (N'ZA', 49707, 84560514, 26),(N'ZA', 909, 84560514, 27),(N'ZA', 231, 84560514, 28), (N'CN', 49707, 68966682, 29)
,(N'CN', 909, 68966682, 30),(N'CN', 231, 68966682, 31),(N'CB', 49707, 65020665, 32)
, (N'CB', 5086, 65020665, 33), (N'CB', 909, 65020665, 34),(N'CB', 231, 65020665, 35)
,(N'GT', 49707, 42016510, 36), (N'GT', 5086, 42016510, 37),(N'GT', 909, 42016510, 38), (N'CR', 49707, 37116544, 39)
,(N'CR', 5086, 37116544, 40), (N'CR', 909, 37116544, 41),(N'CR', 231, 37116544, 42), (N'SV', 49707, 31103322, 43)
, (N'SV', 5086, 31103322, 44), (N'SV', 909, 31103322, 45),(N'NI', 49707, 17415843, 46),(N'NI', 5086, 17415843, 47)
, (N'NI', 909, 17415843, 48), (N'HN', 49707, 12516273, 49),(N'HN', 5086, 12516273, 50),(N'HN', 909, 12516273, 51)
GO
/*
Country grpid Value Row_num
US 49707 604456458 1
GB 5086 497654945 4
CA 909 353500201 10
JP 231 198291290 15
*/
;with mytest as (select grpid,country,value , Row_num, row_number() over( Order by value DESC, grpid DESC) rn from test1)
,mycte1 as (select grpid,country,value , Row_num from mytest a where not exists(select 1 from mytest b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte2 as (select grpid,country,value , Row_num, row_number() over(Order by value DESC, grpid DESC) rn from mycte1 )
,mycte3 as (select grpid,country,value , Row_num from mycte1 a where not exists(select 1 from mycte2 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte4 as (select grpid,country,value , Row_num, row_number() over( Order by value DESC, grpid DESC) rn from mycte3 )
,mycte5 as (select grpid,country,value , Row_num from mycte3 a where not exists(select 1 from mycte4 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte6 as (select grpid,country,value , Row_num, row_number() over( Order by value DESC, grpid DESC) rn from mycte5 )
,mycte7 as (select grpid,country,value , Row_num from mycte5 a where not exists(select 1 from mycte6 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte8 as (select grpid,country,value , Row_num, row_number() over( Order by value DESC, grpid DESC) rn from mycte7 )
,mycte9 as (select grpid,country,value , Row_num from mycte7 a where not exists(select 1 from mycte8 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
--Final combine all
select grpid,country,value , Row_num from mytest WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte2 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte4 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte6 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte8 WHERE rn=1
drop table test1
A Group DisplayOrder Comparison Sample
Posted: March 10, 2014 Filed under: SQL Server 2008 Leave a commenthttp://forums.asp.net/t/1970818.aspx?Comparing+a+collection+of+Rows+with+other+collection+of+rows
declare @UserRecord table
(id int identity(1,1), JobId char(1),categoryId char(1), categoryItem char(1),displayOrder char(1),userId char(1));
insert into @UserRecord values
('1','1', 'a','1', '0'),
('1','1', 'b','2', '0'),
('1','1', 'c','3', '0'),
('1','2', 'a','2', '0'),
('1','2', 'b','1', '0'),
('1','2', 'c','3', '0'),
('1','3', 'a','2', '0'),
('1','3', 'b','1', '0'),
('1','3', 'c','3', '0'),
('1','1', 'a','1', '1'),
('1','1', 'b','2', '1'),
('1','1', 'c','3', '1'),
('1','2', 'a','1', '1'),
('1','2', 'b','2', '1'),
('1','2', 'c','3', '1'),
('1','3', 'a','1', '1'),
('1','3', 'b','2', '1'),
('1','3', 'c','3', '1'),
('1','1', 'a','1', '2'),
('1','1', 'b','2', '2'),
('1','1', 'c','3', '2'),
('1','2', 'a','1', '2'),
('1','2', 'b','2', '2'),
('1','2', 'c','3', '2'),
('1','3', 'a','1', '2'),
('1','3', 'b','2', '2'),
('1','3', 'c','3', '2');
--select * from @UserRecord
;with mycte as (
SELECT Distinct jobid,categoryid,userId, Cols = STUFF((
SELECT ',' + displayOrder
FROM @UserRecord m
Where m.jobid=m1.jobid AND m.categoryid=m1.categoryid AND m.userId=m1.userId
Order by id
FOR XML PATH('')), 1, 1, '')
FROM @UserRecord m1
)
,mycte1 as
(
select jobid,categoryid, cols, userId, count(cols) Over(Partition by categoryid, cols) rn, Min(userid) Over(Partition by categoryid, cols) minUserId
from mycte
)
----If you are using SQL Server 2008 or above
-----Option 1
Merge @UserRecord u
Using ( select jobid,categoryid, cols, userId from mycte1
Where rn>1 And minUserId=0 and userid>0 ) m
On (m.jobid=u.jobid AND m.categoryid=u.categoryid AND m.userId=u.userId )
When matched Then
Delete;
----Option 2
--Merge @UserRecord u
--Using (
--select jobid,categoryid, userId from mycte1 m WHERE exists (Select categoryid, cols From mycte1
--WHERE userid=0 and categoryid=m.categoryid and m.cols=cols ) and userid<>0 ) m
--On (m.jobid=u.jobid AND m.categoryid=u.categoryid AND m.userId=u.userId )
--When matched Then
--Delete;
select id, jobid,categoryid,displayOrder, userId from @UserRecord
Format File for BCP Or Bulk Insert
Posted: March 7, 2014 Filed under: SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2012 Leave a comment------bcp create non-XML format file --One option: DECLARE @sql VARCHAR(4000) SET @sql='bcp [myDB].[dbo].[myTable] format nul -c -f C:\temp\myFormat2.fmt -T -t\t -S'+ @@servername exec master..xp_cmdshell @sql --Another exec master..xp_cmdshell 'bcp [myDB].[dbo].[myTable] format nul -f C:\temp\myFormatFile.fmt -c -T -S myServer\MyInstance'; go ------bcp create XML format file ----Another Sample: DECLARE @sql VARCHAR(4000) SET @sql='bcp [myDB].[dbo].[myTable] format nul -c -x -f C:\temp\yourFORMATFIle.xml -T -t\t -S'+ @@servername exec master..xp_cmdshell @sql exec master..xp_cmdshell 'bcp [myDB].[dbo].[myTable] format nul -c -x -f C:\temp\myFormatFile.xml -T -S myServer\MyInstance'; go --http://technet.microsoft.com/en-us/library/ms179250.aspx
Use a Format File to Map Table Columns to Data-File Fields (SQL Server)
–http://technet.microsoft.com/en-us/library/ms191175.aspx
Use a Format File to Skip a Table Column (SQL Server)
–http://technet.microsoft.com/en-us/library/ms179250.aspx
Use a Format File to Skip a Data Field (SQL Server)
–http://technet.microsoft.com/en-us/library/ms187908.aspx
XML Format file
–Simply remove the that column from section
Non-XML Format File
— Keep the leftmost row number as is (Host file field order) and change the Server coloumn order value to 0 and renumber all other columns as well.
SSIS or T-SQL Stored Procedure
Posted: March 4, 2014 Filed under: SQL Server 2008, SQL SSIS Leave a commentI have seen a solution to use dynamic SQL to migrate data into SQL Server database. But I feel the solution is pushing pretty hard in using T-SQL. It needs quite good of mastering of T-SQL skills to handle the customized task and for working with file names and scheduling, it becomes harder.
SSIS is a low cost ETL tool in SQL Server stack. It comes with many versions of SQL Server included into the server licence. It is easy to handle many tasks which may pose some difficulties with pure T-SQL approach.
I came across a summary of the comparison of these two from Infosys, which is a good source to have a quick overview of this topic.
Here is the link to that document:
Click to access sql-server-data-migration-approaches.pdf
Jingyang Li 