April 22, 2014 at 7:50 am
Hi
I have this problem to solve.
I have to get any table as input parameter, then create a temp table in the same structure as the input table parameter, excluding the primary key.
Then after load data from original table to temp table, excluding primary key as well, then update data in the temp table then load them back to the original table
this is how I approached it,
I create a temp table during a run time with Dynamic SQL:
DECLARE @SQLStatement NVARCHAR(MAX),@TableName NVARCHAR(MAX)
SET @SQLStatement = 'CREATE TABLE dbo.Hosea_tempTable (';
SELECT @SQLStatement = @SQLStatement + ',' + 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;
--Inserting data into temp table:
I will run another dynamic sql with this logic
INSERT INTO Hosea_tempTable
SELECT * FROM Hosea_tblDef_RETURNS
My temp table will always be one column less than the original table, because I'm omitting the primary key when I'm creating the temp table.
The problem now is inserting into temp table, because it's one column less and I'm selecting all from original table.
How can I solve this??
April 22, 2014 at 9:49 am
Use a column list in your select statement instead of select *, that way you can just exclude the ID column. Your @sqlstatement to create the table is also incorrect. you end up having a query like
create table dbo.name(,col1,col2,col2
the following should create the table successfully and populate it with only the columns used to create table.
DECLARE @SQLStatement NVARCHAR(MAX),@TableName NVARCHAR(MAX),@columns nvarchar(max),@columnswithtype nvarchar(max)
set @TableName = 'Hosea_tblDef_RETURNS'
SET @SQLStatement = 'CREATE TABLE dbo.Hosea_tempTable (';
SELECT @columnswithtype = STUFF((
SELECT ', '+ 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
FOR XML path('')), 1, 1, '')
SELECT @columns = STUFF((
SELECT ', '+ c.Name
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
FOR XML path('')), 1, 1, '')
SET @SQLStatement = @SQLStatement+@columnswithtype+')'
select @sqlStatement
exec sp_executesql @sqlStatement
set @sqlstatement = '
INSERT INTO Hosea_tempTable
SELECT '+@columns+' FROM Hosea_tblDef_RETURNS'
select @SQLStatement
exec sp_executesql @sqlStatement
April 22, 2014 at 10:40 am
You're almost there. You just have to add one more line to your query. Instead of
SELECT @SQLStatement = @SQLStatement + ',' + 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;
try this
SELECT @SQLStatement = @SQLStatement + ',' + 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
-- new stuff
and not exists (SELECT 1
FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] where table_name = @tablename and column_name = c.name)
-- end new stuff
ORDER BY column_id;
Simple as that.
April 23, 2014 at 12:41 am
I do have that line of code, I just didn't put it here.
SET @SQLStatement = REPLACE(@SQLStatement,'(,','('); -- remove first comma
I don't think I will be able to list columns because I wouldn't know them, the user will be inputting a table name as a parameter, any table, after creating a temp table with that table, then I need to insert into my temp table, the data from that table that was passed as an input parameter. I can't list it's columns because I don't know which table might be inputted.
April 23, 2014 at 1:07 am
I changed my code now and I'm using SELECT ... INTO to create the table rather than generating a CREATE TABLE statement. I want to cancel the identity column to instead make it a standard int column, after which I can just insert the entire table into the temp table without worrying about excluding the identity column. any help on how can I cancel the identity column??
April 23, 2014 at 7:39 am
hoseam (4/23/2014)
I do have that line of code, I just didn't put it here.SET @SQLStatement = REPLACE(@SQLStatement,'(,','('); -- remove first comma
I don't think I will be able to list columns because I wouldn't know them, the user will be inputting a table name as a parameter, any table, after creating a temp table with that table, then I need to insert into my temp table, the data from that table that was passed as an input parameter. I can't list it's columns because I don't know which table might be inputted.
The code I had posted will get you the column list for your insert statement. For the other way you are going (select * into ), you can use ScottPletcher's code found here(http://www.sqlservercentral.com/Forums/Topic1400640-392-1.aspx)
May 9, 2014 at 6:24 am
There has been few changes to this code..
Before I used to pass parameters
declare @Fund_Id varchar(50),
@Product_Id varchar(50),
@NewFund_Id varchar(50),
@NewProduct_Id varchar(50),
@TableName varchar(50
Now I have created a table to get them from a table,
CREATE TABLE [dbo].[Hosea_tblDef_Cloning_Table](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CLONE_ID] [int] NOT NULL,
[TABLE_NAME] [varchar](150) NULL,
[COLUMN_NAME] [varchar](150) NULL,
[OLD_VALUE] [varchar](150) NULL,
[NEW_VALUE] [varchar](50) NULL
)
COLUMN_NAME being all the columns that I need to change.
I have to rows in this column, have same CLONE_ID (1), same TABLE_NAME ([Hosea_tblDef_RETURNS]) then different COLUMN_NAME (Product_Id and Fund_Id) and their different OLD_VALUE and NEW_VALUE.
Now I have this code:
declare
@New_Value varchar(50),
@Col varchar(50),
@TableName varchar(50)
select @TableName = [TABLE_NAME]
from [Hosea_tblDef_Cloning_Table]
select @Col = [COLUMN_NAME]
from [Hosea_tblDef_Cloning_Table]
select @New_Value = [NEW_VALUE]
from [Hosea_tblDef_Cloning_Table]
SELECT '[' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
' (' + STUFF([source].ColumnList,1,2,'') + ')' + CHAR(10)
FROM information_schema.tables t
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' + CASE
WHEN column_name = @Col THEN ' = ' + QUOTENAME(@New_Value,'''')
--WHEN column_name = 'fund_id' THEN ' = ' + QUOTENAME(@NewFund_Id,'''')
ELSE '' END AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) [source] (ColumnList)
WHERE t.table_name = @TableName
This code only runs for one row in the table ([Hosea_tblDef_Cloning_Table]). I'm not sure how to get it right, please help.
May 9, 2014 at 7:19 am
hoseam (5/9/2014)
There has been few changes to this code..Before I used to pass parameters
declare @Fund_Id varchar(50),
@Product_Id varchar(50),
@NewFund_Id varchar(50),
@NewProduct_Id varchar(50),
@TableName varchar(50
Now I have created a table to get them from a table,
CREATE TABLE [dbo].[Hosea_tblDef_Cloning_Table](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CLONE_ID] [int] NOT NULL,
[TABLE_NAME] [varchar](150) NULL,
[COLUMN_NAME] [varchar](150) NULL,
[OLD_VALUE] [varchar](150) NULL,
[NEW_VALUE] [varchar](50) NULL
)
COLUMN_NAME being all the columns that I need to change.
I have to rows in this column, have same CLONE_ID (1), same TABLE_NAME ([Hosea_tblDef_RETURNS]) then different COLUMN_NAME (Product_Id and Fund_Id) and their different OLD_VALUE and NEW_VALUE.
Now I have this code:
declare
@New_Value varchar(50),
@Col varchar(50),
@TableName varchar(50)
select @TableName = [TABLE_NAME]
from [Hosea_tblDef_Cloning_Table]
select @Col = [COLUMN_NAME]
from [Hosea_tblDef_Cloning_Table]
select @New_Value = [NEW_VALUE]
from [Hosea_tblDef_Cloning_Table]
SELECT '[' + t.table_schema + '].[' + t.table_name + ']' + CHAR(10) +
' (' + STUFF([source].ColumnList,1,2,'') + ')' + CHAR(10)
FROM information_schema.tables t
CROSS APPLY ( -- column list *except* identity column
SELECT ', [' + column_name + ']' + CASE
WHEN column_name = @Col THEN ' = ' + QUOTENAME(@New_Value,'''')
--WHEN column_name = 'fund_id' THEN ' = ' + QUOTENAME(@NewFund_Id,'''')
ELSE '' END AS [text()]
FROM information_schema.columns c
WHERE c.table_schema = t.table_schema
AND c.table_name = t.table_name
AND COLUMNPROPERTY(OBJECT_ID(c.table_name),c.column_name, 'IsIdentity') <> 1
FOR XML PATH('')
) [source] (ColumnList)
WHERE t.table_name = @TableName
This code only runs for one row in the table ([Hosea_tblDef_Cloning_Table]). I'm not sure how to get it right, please help.
Please don't continue cross posting. You have two threads with this topic so far and you keep posting updates in both of them. The other thread is here. http://www.sqlservercentral.com/Forums/Topic1564130-391-3.aspx#bm1569228
_______________________________________________________________
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply