January 10, 2012 at 2:18 am
Hi, I am ok using MERGE I think its a great compromise on using separate commands but is there a way I can effectively utilise selecting all columns and values from my source tabelw without listing every one. Some of the tables I want to merge have over 60 columns and it would be so much better if I could do a sub select all but I cant see any short cuts.
Thanks
January 10, 2012 at 2:48 am
Could you provide some sample code to illustrate your problem?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 10, 2012 at 3:07 am
Hi, it's literally the very basic MERGE command syntax and it's being used to re-insert deleted records from a table. there are no conditions apart from the WHERE NOT EXIST... INSERT. Some 'backup' tables are being created and; if needed; the merge is to re-insert records back in based on not exist. Its just there are a fair few columns and its cumbersome to the script. I want to leave alone as its okay for me but the question has been asked of me so mine is not to wonder why 🙂
Merge dbo.Storage
using dbo.Storage_orig
on dbo.Storage.Rackseq = dbo.Storage_orig.Rackseq
WHEN NOT MATCHED THEN
INSERT ...... about 50million fields (ok I exaggerate but whomever designed this db!!)
January 10, 2012 at 4:49 am
Essex (1/10/2012)
Hi, it's literally the very basic MERGE command syntax and it's being used to re-insert deleted records from a table. there are no conditions apart from the WHERE NOT EXIST... INSERT. Some 'backup' tables are being created and; if needed; the merge is to re-insert records back in based on not exist. Its just there are a fair few columns and its cumbersome to the script. I want to leave alone as its okay for me but the question has been asked of me so mine is not to wonder why 🙂Merge dbo.Storage
using dbo.Storage_orig
on dbo.Storage.Rackseq = dbo.Storage_orig.Rackseq
WHEN NOT MATCHED THEN
INSERT ...... about 50million fields (ok I exaggerate but whomever designed this db!!)
OK, so essentially the problem boils down to listing the column names for a table, right? There are third-party tools like SQLPrompt from RedGate that can perform this task for you (type SELECT * and [tab] and the column list expands by magic). That said, such tools are not free and not acceptable on all systems. I would be inclined to write a function that returns a properly-delimited column list in the correct order for any supplied table name. The query is fairly straight-forward, using the sys.columns view for example.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 10, 2012 at 4:54 am
Example:
SELECT
STUFF
(
(
SELECT
N',' + SPACE(1) + QUOTENAME(c.name)
FROM sys.columns AS c
WHERE
c.[object_id] = OBJECT_ID(N'Production.Product', N'U')
ORDER BY
c.column_id
FOR XML
PATH (''),
TYPE
).value('./text()[1]', 'NVARCHAR(MAX)'),
1, 2, SPACE(0)
)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 10, 2012 at 4:56 am
Cheers Paul, yes you're right it all boils down to the huge column and values list. Will review your example and give it a whirl. Thanks
January 10, 2012 at 5:37 am
Hi Paul, thats great for the column names but of course I have to parse it the values too. As the tables are identical I was hoping I could negate passing the column names and therefore just the values but I guess that introduces another Select .. Where and the whole point of condensing using merge becomes a little obselete.
Thanks for the quick replies.
January 10, 2012 at 5:58 am
This works for me:
Function definition:
USE AdventureWorks
GO
CREATE FUNCTION dbo.TableColumnList(@TableName sysname)
RETURNS TABLE
AS
RETURN
SELECT
STUFF
(
(
SELECT
N',' + SPACE(1) + QUOTENAME(c.name)
FROM sys.columns AS c
WHERE
c.[object_id] = OBJECT_ID(@TableName, N'U')
AND c.is_computed = 0
ORDER BY
c.column_id
FOR XML
PATH (''),
TYPE
).value('./text()[1]', 'NVARCHAR(MAX)'),
1, 2, SPACE(0)
) AS ColumnList
Data set-up:
-- Create two copies of the example table
SELECT * INTO #Original FROM Production.Product AS p
SELECT * INTO #Current FROM Production.Product AS p
-- Remove some rows
DELETE #Current WHERE ProductID <= 400
Demo:
-- Get the column list (for the INSERT and VALUES list)
SELECT * FROM dbo.TableColumnList(N'Production.Product') AS tcl
-- Allow identity insertion
SET IDENTITY_INSERT #Current ON
-- Perform the merge
MERGE #Current AS c
USING #Original AS o ON
o.ProductID = c.ProductID
WHEN NOT MATCHED THEN
INSERT
-- Our column list
([ProductID], [Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], [SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], [ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], [SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate])
VALUES
-- Our column list again
([ProductID], [Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], [SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], [ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], [SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate]);
-- Reset
SET IDENTITY_INSERT #Current OFF
Check:
-- Check tables are identical
SELECT * FROM #Current AS c
EXCEPT
SELECT * FROM #Original AS o
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 10, 2012 at 7:25 am
Hi Paul, sorry but I dont follow. Where does the function come into play since your merge doesn't use it, you're still literally listing column names and values in entirety?
January 10, 2012 at 7:57 am
Essex (1/10/2012)
Hi Paul, sorry but I dont follow. Where does the function come into play since your merge doesn't use it, you're still literally listing column names and values in entirety?
The output of "SELECT * FROM dbo.TableColumnList(N'Production.Product') AS tcl" is used where the "Our column list" comment is in the script. I could have used dynamic SQL there, but I was lazy and thought it was clear enough what I did. Point is, it would possible to construct the MERGE statement using dynamic SQL and the function. If that's not clear, I'm quite happy to write a more explicit demo.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 10, 2012 at 9:27 am
Ah I see. many thanks for offer but no, don't go to that much trouble, I have to do some leg work afterall 🙂
Thanks for the updates,been much appreciated
January 10, 2012 at 9:36 am
I may be way off here, but why not just right click the table in Object Explorer and Script as Insert then manipulate that?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJanuary 10, 2012 at 9:40 am
Jason, it was mainly the bulk of the insert due to high volume of fields. Unweildy and non friendly so I was asked to cut it down in size using a cursor or soemthign but that wasn't an option since I argued one would have to define all the data items etc making it even worse.
January 10, 2012 at 9:43 am
Gotcha
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply