Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

INSERT INTO, EXCLUDING ONE COLUMN Expand / Collapse
Author
Message
Posted Monday, February 10, 2014 12:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:21 PM
Points: 1,037, Visits: 6,962
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
Post #1539643
Posted Monday, February 10, 2014 12:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:05 PM
Points: 188, Visits: 322
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.
Post #1539644
Posted Monday, February 10, 2014 1:27 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:06 AM
Points: 649, Visits: 58
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 ....

Post #1539647
Posted Monday, February 10, 2014 2:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869, Visits: 14,178
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
Post #1539656
Posted Monday, February 10, 2014 2:34 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:06 AM
Points: 649, Visits: 58
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
Post #1539666
Posted Monday, February 10, 2014 3:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:05 PM
Points: 188, Visits: 322
@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.

Post #1539677
Posted Monday, February 10, 2014 3:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:05 PM
Points: 188, Visits: 322
@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.
Post #1539678
Posted Wednesday, February 12, 2014 2:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869, Visits: 14,178
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
Post #1540598
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse