April 23, 2014 at 1:49 am
Hi
I'm selecting * from my perm table into my temp table(SELECT * temp INTO FROM perm), and it will also select the primary identity column. I want to remove this column, but the issue is I would never know which column is the primary because the perm table name will be passed as a input parameter, then take all the it's data into temp table to update them, then put them back into perm table.
How do I remove do I remove identity column from my temp table, PS. at this point I don't know any of the column's names.
April 23, 2014 at 2:10 am
hoseam (4/23/2014)
... the perm table name will be passed as a input parameter, then take all the it's data into temp table to update them, then put them back into perm table...
If you've got to update the permanent table from the temp table then why not simply update the permanent table in one step?
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
April 23, 2014 at 2:23 am
The scenario is this..
I have a product AGP with Fund E01, and other attributes
then later I want to creat another product with the same attributes as AGP product, except this new product will have to be new Product name(PCC) and Fund name(E33)
So I thought I can get that row, AGP row, put it in the temp, and update only Product and Fund, then put back the new Product back into perm table
April 23, 2014 at 2:27 am
hoseam (4/23/2014)
The scenario is this..I have a product AGP with Fund E01, and other attributes
then later I want to creat another product with the same attributes as AGP product, except this new product will have to be new Product name(PCC) and Fund name(E33)
So I thought I can get that row, AGP row, put it in the temp, and update only Product and Fund, then put back the new Product back into perm table
Is there any good reason for not using INSERT INTO...SELECT?
INSERT INTO permtable (column_list) SELECT recalculated_stuff FROM permtable
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
April 23, 2014 at 2:40 am
For now I'm using SELECT ... INTO
SET @InsertSQL = 'SELECT * INTO Hosea_tempTable FROM ' + @TableName +' WHERE (Product_Id = '+ QUOTENAME(@Product_Id, '''''') +' or Product_Id = '''' ) AND (Fund_Id = '+ QUOTENAME(@Fund_Id, '''''') +' or Fund_Id is null)';
But I can't list column names because as you can see in my code, the user will be inputting the table name, my temp table will be built in the same structure as the inputted table, hence the need to use SELECT INTO.
but then as I update the column, I will have to insert it back to perm table, with have IDENTITY(1,1), so I have to insert it back without the identity column that was copied into the temp table, my issue is around that, that how do I discard the identity in temp table
April 23, 2014 at 2:45 am
hoseam (4/23/2014)
For now I'm using SELECT ... INTOSET @InsertSQL = 'SELECT * INTO Hosea_tempTable FROM ' + @TableName +' WHERE (Product_Id = '+ QUOTENAME(@Product_Id, '''''') +' or Product_Id = '''' ) AND (Fund_Id = '+ QUOTENAME(@Fund_Id, '''''') +' or Fund_Id is null)';
But I can't list column names because as you can see in my code, the user will be inputting the table name, my temp table will be built in the same structure as the inputted table, hence the need to use SELECT INTO.
but then as I update the column, I will have to insert it back to perm table, with have IDENTITY(1,1), so I have to insert it back without the identity column that was copied into the temp table, my issue is around that, that how do I discard the identity in temp table
Do you really have lots of tables storing the same attributes?
The scenario is this..
I have a product AGP with Fund E01, and other attributes
then later I want to creat another product with the same attributes as AGP product, except this new product will have to be new Product name(PCC) and Fund name(E33)
So I thought I can get that row, AGP row, put it in the temp, and update only Product and Fund, then put back the new Product back into perm table [/unquote]
Also, haven't we all been through this before, or was it with someone else?
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
April 23, 2014 at 2:47 am
Is it this that you are looking for?
😎
SELECT *
INTO #MyTemp
FROM Sales.SalesOrderHeader;
SELECT *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE N'#MyTemp%'
DROP TABLE #MyTemp;
April 23, 2014 at 2:54 am
Eirikur Eiriksson (4/23/2014)
Is it this that you are looking for?😎
SELECT *INTO #MyTemp
FROM Sales.SalesOrderHeader;
SELECT *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE N'#MyTemp%'
DROP TABLE #MyTemp;
Would you do it like this Eirikur or would you build INSERT INTO...SELECT using dynamic sql? 😉
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
April 23, 2014 at 3:28 am
ChrisM@Work (4/23/2014)
Eirikur Eiriksson (4/23/2014)
Is it this that you are looking for?😎
SELECT *INTO #MyTemp
FROM Sales.SalesOrderHeader;
SELECT *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE N'#MyTemp%'
DROP TABLE #MyTemp;
Would you do it like this Eirikur or would you build INSERT INTO...SELECT using dynamic sql? 😉
He he 😀 few posts appeared from the time I wrote the answer until I submitted it, wanted to demonstrate how to list columns in a #temporary table.
I must say I find the issue here somewhat skewed, there is no need to drop the originating identity column, just ignore it in the insert.
To answer your question Chris, I would prefer INSERT INTO...SELECT, the only dynamic part is the name of the source table.
😎
April 23, 2014 at 3:29 am
Here's the case;
CREATE permTable
(
[RETURNS_ID] [int] IDENTITY(1,1) NOT NULL,
[REPORT_ID] [varchar](50) NOT NULL,
[COMPANY] [varchar](150) NULL,
[PRODUCT_TYPE] [varchar](150) NULL,
[PRODUCT_ID] [varchar](150) NULL,
[PRODUCT_DESC] [varchar](150) NULL,
[FUND_ID] [varchar](150) NULL
)
INSERT INTO permTable ([REPORT_ID]
,[COMPANY]
,[PRODUCT_TYPE]
,[PRODUCT_ID]
,[PRODUCT_DESC]
,[FUND_ID])
VALUES ('ABSREP', 'PSG PTY', 'Glass', 'AGP1', 'Scheme List', 'E016')
There is my PermTable, so there are new requirements, that I need to duplicate that row, but only PRODUCT_ID and FUND_ID must change, everything should be the same.
my approach was, I'm going to copy the whole row into tempTable, then update PRODUCT_ID and FUND_ID to the new PRODUCT_ID and FUND_ID, then send back the updated row back to PermTable.
But, as soon as I SELECT INTO the temp table, it also copy the RETURNS_ID, which is the primary key to the permTable...
When I return the updated records I shouldn't include the RETURNS_ID. this code will be running from Store Proc, the user should be able to insert any table name as an input. Hence my first code was
SET @InsertSQL = 'INSERT INTO Hosea_tempTable SELECT * FROM ' + @TableName +' WHERE (Product_Id = '+ QUOTENAME(@Product_Id, '''''') +' or Product_Id = '''' ) AND (Fund_Id = '+ QUOTENAME(@Fund_Id, '''''') +' or Fund_Id is null)';
April 23, 2014 at 3:40 am
hoseam (4/23/2014)
...the user should be able to insert any table name as an input...
If you're updating two very specific columns, they should exist in one very specific table, surely?
If not, then Eirikur has alluded to a very clean way of achieving what you are trying to do. Pick a row from a source table, change a couple of columns, then insert it into the source table.
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
April 23, 2014 at 3:51 am
If you're updating two very specific columns, they should exist in one very specific table, surely?
Yes, I'm updating two specific columns, which is [PRODUCT_ID] and [FUND_ID], then insert the new updated ones back to the table as a new record. I don't know if I make sense..
If I have only ONE record in the table, with PRODUCT_ID 'AGP1' and FUND_ID 'E016', and other columns. I want to update this row, new PRODUCT_ID 'PIC1' and FUND_ID 'D016', and keep the other columns the same.
then I will now be having TWO records, these:
1. PRODUCT_ID 'AGP1' and FUND_ID 'E016'
2. PRODUCT_ID 'PIC1' and FUND_ID 'D016
This is what I want to archive. and it should happen for every table that a user can pass in as input parameter.
April 23, 2014 at 3:55 am
I'm planning to run everything inside the PROC:
CREATE PROC (@Product_Id, @Fund_Id, @NewProduct_Id, @NewFund_Id, @TableName)
AS
BEGIN
END
that's just the shell. I'm still trying to get the inside code correctly.
April 23, 2014 at 4:21 am
hoseam (4/23/2014)
I'm planning to run everything inside the PROC:CREATE PROC (@Product_Id, @Fund_Id, @NewProduct_Id, @NewFund_Id, @TableName)
AS
BEGIN
END
that's just the shell. I'm still trying to get the inside code correctly.
A suggestion for the proc code, not complete but you should be able to work this out 😎
DECLARE @NEW_PRODUCT_ID VARCHAR(50) = 'PIC1';
DECLARE @NEW_FUND_ID VARCHAR(50) = 'D016';
DECLARE @RETURNS_ID INT = 1;
INSERT INTO dbo.permTable
(REPORT_ID
,COMPANY
,PRODUCT_TYPE
,PRODUCT_ID
,PRODUCT_DESC
,FUND_ID
)
SELECT
REPORT_ID
,COMPANY
,PRODUCT_TYPE
,@NEW_PRODUCT_ID AS PRODUCT_ID
,PRODUCT_DESC
,@NEW_FUND_ID AS FUND_ID
FROM dbo.permTable PT
WHERE PT.RETURNS_ID = @RETURNS_ID;
SELECT * FROM dbo.permTable;
April 23, 2014 at 4:32 am
Now that's an easy one cause I created a table for you, so you are working with the data you know. say now the user input tblDef_RETURNS as table, and you have to do the same to this table. Your code wouldn't work because you don't know anything about this table.
The solution has to cater for any table that the user might pass in. as I indicated that the input parameters are @Product_Id, @NewProduct_Id and @TableName
Viewing 15 posts - 1 through 15 (of 52 total)
You must be logged in to reply to this topic. Login to reply