SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


INSERT INTO, EXCLUDING ONE COLUMN


INSERT INTO, EXCLUDING ONE COLUMN

Author
Message
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2038 Visits: 10364
Use the OUTPUT clause on an UPDATE statement. OUTPUT was introduced in SQL Server 2005.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
hoseam
hoseam
SSC-Addicted
SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)

Group: General Forum Members
Points: 462 Visits: 428
On this case, RETURNS_ID is a PK with IDENTITY(1,1). I could do this:

SELECT * INTO #TempTable
FROM Hosea_tblDef_RETURNS
WHERE Product_Id = 'AGP1'
AND (Fund_Id = 'E016' or Fund_Id is null)

ALTER TABLE #TempTable
DROP COLUMN RETURNS_ID

SELECT * FROM #TempTable
DROP TABLE #TempTable


but the problem is that I will never know if the table has a PK with IDENTITY(1,1) or not since the user will be inputting the name of the table.I want a generic solution that will cater for any table. the solution I have now works fine for tables without PK. I tested with a table having PK IDENTITY(1,1) then I ran into this problem.
Arno Ho
Arno Ho
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 64
Another solution is to create a view of your table without the identity column

CREATE TABLE TEST(
CODE_test int IDENTITY(1,1) NOT NULL,
shortname varchar(14) NULL,
Name] varchar(38) NOT NULL,
CONSTRAINT [PK_TEST] PRIMARY KEY (CODE_test)
)
go
create view vtest as select shotname, name from TEST
go

You can do some insert into the view
like
-> insert into test values ('Test', 'what a test !')
-> insert into test select .... from bigtable ....
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16222 Visits: 19544
Can you provide us with a brief description of the end to end process?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Arno Ho
Arno Ho
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 64
CREATE TABLE TEST(
Code_test int IDENTITY(1,1) NOT NULL,
shortname varchar(14) NULL,
Name varchar(38) NOT NULL,
CONSTRAINT [PK_TEST] PRIMARY KEY (Code_test)
)
go
--Create a view without the identity column
create view vtest
as
select shortname, name from TEST
go
--You can do some insert into the view like
insert into vtest values ('Test1', 'what a test !')
insert into vtest values ('Test2', 'what a test !')
insert into vtest values ('Test3', 'what a test !')
--------------------------------------
select * from test
--it returns the 3 rows
--------------------------------------
-- you can else do insert directly with the table
insert into test values ('Test 100', 'what a test !')
insert into test values ('Test 101', 'what a test !')
insert into test values ('Test 102', 'what a test !')
select * from test
--it returns the 6 rows
hoseam
hoseam
SSC-Addicted
SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)

Group: General Forum Members
Points: 462 Visits: 428
@ChrisM@Work

The end to end process is pretty much what I have said, I have products I need to clone. I have 10 tables, in any table I might need to clone a certain product. in each table there are products with their productID. If have productA in TableA and I need to clone this product, if productID of this product is 123, it's clone will have different productID, but everything else will be the same. but the products I need to clone are in different table.

So I'm thinking of a generic solution where the user can just pass the parameters then any table, row or column affected will be processed.

e.g @parameters, tablename, @productID, @newProductID, with these parameters, the proc or fuction should be able to clone any product in any table.

But other tables has IDENTITY(1,1) and other doesn't have. this comes a challenge, giving me errors I mentioned before.
hoseam
hoseam
SSC-Addicted
SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)SSC-Addicted (462 reputation)

Group: General Forum Members
Points: 462 Visits: 428
@a.guillaume

I'm trying to shy away from creating a View or Table for that matter specifying the columns, because if there are any changes to a Table I will need to change a View as well.

hence:

SET @SQLStatement = 'CREATE TABLE dbo.Hosea_tempTable (';

SELECT @SQLStatement = @SQLStatement + ',' + c.name +' '+ + c.name +' '+ CASE
WHEN st.name LIKE '%CHAR%'
THEN st.name + '(' + CONVERT(VARCHAR(4),c.max_length) + ')'
ELSE st.name
END

FROM sys.tables t
JOIN sys.columns c
ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types st
ON st.system_type_id = c.system_type_id
WHERE t.name = @TableName
AND c.is_identity = 0
ORDER BY column_id;


this code is the same as creating a #temp table, any changes to my tables won't impact the procedure.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16222 Visits: 19544
hoseam (2/10/2014)
@ChrisM@Work

The end to end process is pretty much what I have said, I have products I need to clone. I have 10 tables, in any table I might need to clone a certain product. in each table there are products with their productID. If have productA in TableA and I need to clone this product, if productID of this product is 123, it's clone will have different productID, but everything else will be the same. but the products I need to clone are in different table.

So I'm thinking of a generic solution where the user can just pass the parameters then any table, row or column affected will be processed.

e.g @parameters, tablename, @productID, @newProductID, with these parameters, the proc or fuction should be able to clone any product in any table.

But other tables has IDENTITY(1,1) and other doesn't have. this comes a challenge, giving me errors I mentioned before.



10 product tables is quite unusual but I can see how it might arise when you contrast the data storage requirements of brassieres and shipping containers.
Personally, I'd go for a solution which doesn't look anything like your project at all - much simpler, if a little repetitive.
IF @ProductTable = 'Products01'
BEGIN
...
END

Over-engineered solutions such as the one you are suggesting are a pig to maintain and can take much longer than expected to write. Had you chosen the simpler option of ten conditional blocks of conventional TSQL (as opposed to dynamic sql), you wouldn't have needed any help and you would have finished coding and testing the same day.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
bernard.cook
bernard.cook
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 5
Hi Chris, Hoseam, (Arno Ho - I love your view solution ... see alternative below without creating view)

(I know I'm coming in late on this subject I only joined today... but I have a solution for the insert without specifying fields nor having to create dynamic SQL)

There is every good reason for not specifying fields in an insert statement in order to ensure all new fields exist in the source and destination tables!
When dynamic inserts are performed you have to ensure your source table has all the latest fields. If not, the program will fail.

Just as one can get caught time for not specifying all fields in an explicit insert statement (specifying all fields in the insert), it is FAR WORSE(as far as I'm concerned) if you omit new fields that need to be included.

The below very simple solution ensures that you always have all fields in the insert statement without dynamic SQL,

The only catch is to keep a standard of having your identity column as the last column in your table(s). (works in a similar way to Arno Ho's view concept)

Solution in summary: Create a temp table from the destination table, remove the identity column, update the temp table and safely apply an "implicit" insert (no columns specified).

Example ...

/*0. create test destination table for you*/ select 'Mr. Robin Rex' as CustomerName, identity(int,1,1) as CustomerID into dbo.Destination_table -- drop table dbo.Destination_table

/*1. Create the #Temp table from the destination table: */ select top 0 * into #Source from dbo.Destination_table -- DROP TABLE #Source

/*2. This is key of the solution! */ alter table #Source drop column CustomerID

/*3. Create new data or what ever it is you need to do */ insert into #Source select 'Mrs. Tom' union all select 'Dr. French'

/*4. The following will then work NO FIELDS SPECIFIED! */ insert into dbo.Destination_table select * from #Source

/*5. look at the results! */ select * from dbo.Destination_table

No dynamic coding! No field specified in the insert!
Arno Ho
Arno Ho
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 64
Lovely solution !
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search