February 24, 2015 at 10:14 am
Hi,
I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .Could some one help in resolving this issue Thanks in advance
Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)
Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)
February 24, 2015 at 12:43 pm
So it looks like you are trying to dynamically build the update list for a merge statement. Is this what you need to do?
I would just use your second query if this is what I was trying to accomplish as it is going to give you the same results as the first query if you run it against a table without an identity column. The only changes I might consider is to take into account the case where the primary key of a table is not an Identity Column as you typically don't want to update the primary key of a table. So something like this:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND NOT EXISTS(SELECT 1
FROM sys.key_constraints AS KC JOIN sys.index_columns AS IC ON KC.unique_index_id = IC.index_id AND KC.parent_object_id = IC.object_id
WHERE sys.columns.object_id = IC.object_id AND sys.columns.column_id = IC.column_id AND KC.type = 'PK')
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 24, 2015 at 12:59 pm
RamSteve (2/24/2015)
Hi,I have the following 2 Query's - case when Table has no Identity Column and other with identity Column . I am planning to make it to single Query .Could some one help in resolving this issue Thanks in advance
Query 1:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE object_id =
(
SELECT sys.objects.object_id
FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing1' AND sys.schemas.Name ='dbo'
)
Query2:
SELECT @ColumnNamesWhenNoIdentity = COALESCE(@ColumnNamesWhenNoIdentity + ',', '') + Name +'= SOURCE.'+Name
FROM sys.columns WITH(NOLOCK) WHERE is_identity != 1 AND object_id =
(SELECT sys.objects.object_id FROM sys.objects WITH(NOLOCK)
INNER JOIN sys.schemas WITH(NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.TYPE = 'U' AND sys.objects.Name = 'Testing2' AND sys.schemas.Name ='dbo'
)
Quick question, why the nolock hints, what is the problem you are addressing there? Are you experiencing locking/blocking in the sys schema?
😎
February 24, 2015 at 2:29 pm
I need all columns if no Identity on the table as i am merging and Inserting the Columns data when Not Matched with Identity_Insert ON
for that i need to specify all columns for Insert .
So i am looking for a single Query
February 24, 2015 at 2:59 pm
RamSteve (2/24/2015)
I need all columns if no Identity on the table as i am merging and Inserting the Columns data when Not Matched with Identity_Insert ONfor that i need to specify all columns for Insert .
So i am looking for a single Query
Not sure I understand. I also don't get why you are using the nolock hint when querying the sys schema. Before you dig much deeper into using MERGE you should take a look here. http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2015 at 3:22 pm
So will there be any issues in using with(NOLOCK) for sys schemas ? I had the habit of using every where in SQL Statements ..What would be the pros and cons of using the WITH(NOLOCK) for sys schemas
February 24, 2015 at 10:52 pm
RamSteve (2/24/2015)
So will there be any issues in using with(NOLOCK) for sys schemas ? I had the habit of using every where in SQL Statements ..What would be the pros and cons of using the WITH(NOLOCK) for sys schemas
The NOLOCK hint is no panacea for any problems within SQL Server, suggest you search for "why not using nolock" and look into some of those articles/posts.
😎
February 25, 2015 at 6:45 am
Just to chime in, I'd get rid of the nolock as well. It shouldn't be needed and can have unintended consequences. I'd be more inclined to add and OPTION(RECOMPILE) on the end because you really don't want these types of one-time queries in the cache. Anyway, here's something I think might be closer to what you need since you need the update without the identity and the insert list WITH the identity column:
DECLARE @MergeUpdate NVARCHAR(4000),
@MergeInsert NVARCHAR(4000);
SELECT
@MergeUpdate = COALESCE(@MergeUpdate + ',', '') + CASE WHEN columns.is_identity <> 1 THEN name + '= SOURCE.' + name ELSE '' END,
@MergeInsert = COALESCE(@MergeInsert + ',', '') + 'SOURCE.' + columns.name
FROM
sys.columns
WHERE
object_id IN (
SELECT
sys.objects.object_id
FROM
sys.objects
INNER JOIN sys.schemas
ON sys.objects.schema_id = sys.schemas.schema_id
WHERE
sys.objects.type = 'U' AND
sys.objects.name = 'test' AND
sys.schemas.name = 'dbo'
)
SELECT @MergeUpdate, @MergeInsert
One other thing I'd consider is putting the database into a database project and then using a T-SQL T4 template to generate my merge statements
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 25, 2015 at 7:29 am
RamSteve (2/24/2015)
So will there be any issues in using with(NOLOCK) for sys schemas ? I had the habit of using every where in SQL Statements ..What would be the pros and cons of using the WITH(NOLOCK) for sys schemas
That is a bad habit to get into. The upside is minimal and the downside can be devastating. Here are a few of my favorites on this topic.
http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
The basic gist here is that if accuracy is not critical you might be ok using that hint. But it can and will return missing and/or duplicate rows which produces bugs that are nearly impossible to "fix".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply