April 6, 2016 at 1:11 pm
I use the following code to get all the column names except the first one because it is an identity (1,1) column.
I want to load the column names into a #temptable however, the column names are each a record as seen below. How do I extract the column names from the INFORMATION_Schema.Columns and then convert all the rows into one row with all the column names so that I can populate the columns with the correct values then pass the #temptable to an insert stored proc to avoid the identity constraint.
When I insert * into the #temptable from the target table I get all the columns including the identity column. But when I populate the #temptable with values and pass the #temptable to the insert proc I get the following error because I am passing the identity column.
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'JP_CDM.case' can only be specified when a column list is used and IDENTITY_INSERT is ON.
That is why I want to get the table structure from INFORMATION_Schema.Columns except for the first column which is identified as where Ordinal_Position <> 1.
[highlight="#ffff11"]Here is the sample code:[/highlight]
Declare @strSQL VarChar (MAX)
Declare @DatabaseNameParameter as Varchar(100) = 'UCR'
Declare @SchemaParameter as VarChar (100) = 'JP_CDM'
Declare @TableNameParameter as VarChar (100) = 'case'
--SET @strSQL = 'SELECT * FROM ' + @DatabaseNameParameter + '.' + @SchemaParameter + '.' + @TableNameParameter
--exec(@strSQL)
--End
--SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT max(Ordinal_Position) as 'max positions' FROM INFORMATION_SCHEMA.Columns
where TABLE_CATALOG = @DatabaseNameParameter and TABLE_SCHEMA = @SchemaParameter and TABLE_NAME = @TableNameParameter
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns
where Ordinal_Position <> 1
and TABLE_CATALOG = @DatabaseNameParameter and TABLE_SCHEMA = @SchemaParameter and TABLE_NAME = @TableNameParameter
Here is the results:
max positions
21
----------------------
Results from second query:
COLUMN_NAME
ucn
lcn
court_id
case_initiation_date
case_restriction_flag
county
date_disposed
recurrent_flag
intestate_testate_flag
case_referred_to_mediation_flag
contested_flag
jury_trial_flag
outstanding_warsumcap_flag
pro_se_flag
record_source
record_state
create_user
create_date
maint_user
maint_date
Now I need to create an insert statement with the above columns which excludes the identity column so I can pass the #tempTable to the Insert proc.
Any ideas?
Thanks in advance!
This is urgent!
JP Gillum
April 6, 2016 at 1:51 pm
Why are you copying your table to a temp table?
Why does the temp table needs an identity column?
Why aren't you using sys.columns which has the column is_identity (and is_computed)?
The solution you're asking for is not that difficult, but you might want to take a step back to be sure you're doing things correctly and avoid any unnecessary steps.
April 6, 2016 at 2:21 pm
Here is the proposed process:
1. Create a temp table with the structure of the target table (@SomeTable Name)
2. Apply the business rules to the incoming data and load the results into the temp table using the (@SomeTable Name) structure
3. Note that this is to be a dynamic routine that will load many target tables so that is why we want to dynamically get the table structure into a #temptable and after processing then insert the new data into the target table (@SomeTable Name)
4. There are 8 target tables with different structures and we do not want to hard code the columns within the insert statement for all eight target tables, so that is why we create the #tempTable (per the @SomeTable Name being processed) so that we can insert into the target table without providing the column names.
5. Since I get the structure from the target table and it has an identity column, the #temptable will also have the column which is the identity column in the target table; which is not needed in the #temptable
But I need to dynamically create the structure for each @SomeTable Name being processed.
You are correct I do not need the identity column in the #tempTable but the way I am getting the structure from the target table that column comes with it:
drop table #case_tmpTable
SELECT * INTO #case_tmpTable FROM [JP_CDM].[case]
where 1 = 2
If I could just drop the first column (which is position ordinal = 1) without indicating the column name during the select * into #case_tmpTable that would solve my problem.
We do not want to hardcode the column names because I am developing a master insert proc to process several different tables.
I hope this is clearer.
I will look at using the system.columns to see if that helps.
Thanks,
JP
April 6, 2016 at 3:25 pm
Ok,
The solution is simple!
I just drop the column I don't need via the following:
drop table #case_tmpTable
SELECT * INTO #case_tmpTable FROM [JP_CDM].[case]
--where 1 = 2
ALTER Table #case_tmpTable
DROP COLUMN case_id
This removes the first column which is the identity column in the target table.
Then after processing the data and loading into the #case_tmpTable I can then
insert into the target table
drop table #case_tmpTable
SELECT * INTO #case_tmpTable FROM [JP_CDM].[case]
--where 1 = 2
ALTER Table #case_tmpTable
DROP COLUMN case_id
------------------------------------------------------
Do the required processing and load the #case_tmpTable
Then do the insert into the target table without specifying the column names.
I can now use the Select *
------------------------------------------------------
go
INSERT into [JP_CDM].[case]
SELECT *
-- ucn,
--lcn,
--court_id,
--case_initiation_date,
--case_restriction_flag,
--county,
--date_disposed,
--recurrent_flag,
--intestate_testate_flag,
--case_referred_to_mediation_flag,
--contested_flag,
--jury_trial_flag,
--outstanding_warsumcap_flag,
--pro_se_flag,
--record_source,
--record_state,
--create_user,
--create_date,
--maint_user,
--maint_date
FROM #case_tmpTable;
select * from #case_tmpTable
select * from [JP_CDM].[case]
DROP TABLE #TempTable
April 6, 2016 at 3:44 pm
jpgillum (4/6/2016)
------------------------------------------------------[font="Arial Black"]Do the required processing and load the #case_tmpTable
[/font]Then do the insert into the target table without specifying the column names.
I can now use the Select *
------------------------------------------------------
I'm curious... what will the bold section of above consist of?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2016 at 5:28 pm
Jeff Moden (4/6/2016)
jpgillum (4/6/2016)
------------------------------------------------------[font="Arial Black"]Do the required processing and load the #case_tmpTable
[/font]Then do the insert into the target table without specifying the column names.
I can now use the Select *
------------------------------------------------------
I'm curious... what will the bold section of above consist of?
To the OP:
Also curious, how would you use the SELECT *? You won't be able to use it to insert data from the temp table to permanent table.
April 7, 2016 at 2:54 am
Here is the solution:
drop table #case_tmpTable
SELECT * INTO #case_tmpTable FROM [JP_CDM].[case]
where 1 = 2
ALTER Table #case_tmpTable
DROP COLUMN case_id
------------------------------------------------------
The code above creates the temp table structure based on the target table
Next I have to drop the identity column case_id
Next do the required staging ETL to load the #case_tmpTable
Next see below where I do the insert into the target table using SELECT *
This way I do not have to know the column names. This will be a dynamic T-SQL
stored proc and will be able to process many different tables in this way by passing the @dbname, @schema, and @table to process.
------------------------------------------------------
go
INSERT into [JP_CDM].[case]
SELECT *
-- ucn,
--lcn,
--court_id,
--case_initiation_date,
--case_restriction_flag,
--county,
--date_disposed,
--recurrent_flag,
--intestate_testate_flag,
--case_referred_to_mediation_flag,
--contested_flag,
--jury_trial_flag,
--outstanding_warsumcap_flag,
--pro_se_flag,
--record_source,
--record_state,
--create_user,
--create_date,
--maint_user,
--maint_date
FROM #case_tmpTable;
select * from #case_tmpTable
select * from [JP_CDM].[case]
April 7, 2016 at 9:05 am
jpgillum (4/7/2016)
Here is the solution:drop table #case_tmpTable
SELECT * INTO #case_tmpTable FROM [JP_CDM].[case]
where 1 = 2
ALTER Table #case_tmpTable
DROP COLUMN case_id
------------------------------------------------------
The code above creates the temp table structure based on the target table
Next I have to drop the identity column case_id
Next do the required staging ETL to load the #case_tmpTable
Next see below where I do the insert into the target table using SELECT *
This way I do not have to know the column names. This will be a dynamic T-SQL
stored proc and will be able to process many different tables in this way by passing the @dbname, @schema, and @table to process.
------------------------------------------------------
go
INSERT into [JP_CDM].[case]
SELECT *
-- ucn,
--lcn,
--court_id,
--case_initiation_date,
--case_restriction_flag,
--county,
--date_disposed,
--recurrent_flag,
--intestate_testate_flag,
--case_referred_to_mediation_flag,
--contested_flag,
--jury_trial_flag,
--outstanding_warsumcap_flag,
--pro_se_flag,
--record_source,
--record_state,
--create_user,
--create_date,
--maint_user,
--maint_date
FROM #case_tmpTable;
select * from #case_tmpTable
select * from [JP_CDM].[case]
This:
INSERT into [JP_CDM].[case]
SELECT
*
--ucn,
--lcn,
--court_id,
--case_initiation_date,
--case_restriction_flag,
--county,
--date_disposed,
--recurrent_flag,
--intestate_testate_flag,
--case_referred_to_mediation_flag,
--contested_flag,
--jury_trial_flag,
--outstanding_warsumcap_flag,
--pro_se_flag,
--record_source,
--record_state,
--create_user,
--create_date,
--maint_user,
--maint_date
FROM
#case_tmpTable;
select * from #case_tmpTable
Is not going to work as there are not an equal number of columns between [JP_CDM].[case] and #case_tmpTable since you dropped the identity column from #temp table.
April 7, 2016 at 9:27 am
Lynn Pettis (4/7/2016)
This:
INSERT into [JP_CDM].[case]
SELECT
*
--ucn,
--lcn,
--court_id,
--case_initiation_date,
--case_restriction_flag,
--county,
--date_disposed,
--recurrent_flag,
--intestate_testate_flag,
--case_referred_to_mediation_flag,
--contested_flag,
--jury_trial_flag,
--outstanding_warsumcap_flag,
--pro_se_flag,
--record_source,
--record_state,
--create_user,
--create_date,
--maint_user,
--maint_date
FROM
#case_tmpTable;
select * from #case_tmpTable
Is not going to work as there are not an equal number of columns between [JP_CDM].[case] and #case_tmpTable since you dropped the identity column from #temp table.
Actually, it would work because it would ignore the identity column.
CREATE TABLE Source_Test(
case_id int IDENTITY(1,1),
SomeString varchar(30),
SomeDate datetime,
SomeValue decimal(10,2)
);
INSERT INTO Source_Test(SomeString, SomeDate, SomeValue)
VALUES
('A', GETDATE() - 3, 15),
('B', GETDATE() - 2, 25),
('C', GETDATE() - 1, 35),
('D', GETDATE() , 45);
CREATE TABLE Destination_Test(
case_id int IDENTITY(1,1),
SomeString varchar(30),
SomeDate datetime,
SomeValue decimal(10,2)
);
SELECT *
INTO #case_tmpTable
FROM Source_Test;
ALTER Table #case_tmpTable
DROP COLUMN case_id;
INSERT into Destination_Test
SELECT *
FROM #case_tmpTable;
DROP TABLE #case_tmpTable;
DROP TABLE Source_Test;
DROP TABLE Destination_Test;
I'd still prefer to use the columns, though.
April 7, 2016 at 9:54 am
Luis Cazares (4/7/2016)
Lynn Pettis (4/7/2016)
This:
INSERT into [JP_CDM].[case]
SELECT
*
--ucn,
--lcn,
--court_id,
--case_initiation_date,
--case_restriction_flag,
--county,
--date_disposed,
--recurrent_flag,
--intestate_testate_flag,
--case_referred_to_mediation_flag,
--contested_flag,
--jury_trial_flag,
--outstanding_warsumcap_flag,
--pro_se_flag,
--record_source,
--record_state,
--create_user,
--create_date,
--maint_user,
--maint_date
FROM
#case_tmpTable;
select * from #case_tmpTable
Is not going to work as there are not an equal number of columns between [JP_CDM].[case] and #case_tmpTable since you dropped the identity column from #temp table.
Actually, it would work because it would ignore the identity column.
CREATE TABLE Source_Test(
case_id int IDENTITY(1,1),
SomeString varchar(30),
SomeDate datetime,
SomeValue decimal(10,2)
);
INSERT INTO Source_Test(SomeString, SomeDate, SomeValue)
VALUES
('A', GETDATE() - 3, 15),
('B', GETDATE() - 2, 25),
('C', GETDATE() - 1, 35),
('D', GETDATE() , 45);
CREATE TABLE Destination_Test(
case_id int IDENTITY(1,1),
SomeString varchar(30),
SomeDate datetime,
SomeValue decimal(10,2)
);
SELECT *
INTO #case_tmpTable
FROM Source_Test;
ALTER Table #case_tmpTable
DROP COLUMN case_id;
INSERT into Destination_Test
SELECT *
FROM #case_tmpTable;
DROP TABLE #case_tmpTable;
DROP TABLE Source_Test;
DROP TABLE Destination_Test;
I'd still prefer to use the columns, though.
Cool, learned something I always avoided doing. Never wanted to take the chance that something may change. Never rely on the order the columns are defined when the table is created. You never know when a column may be dropped and recreated with another data type changing the position of the column in the order of definition.
Ran into this at a previous employer where we added a column with a different data type, converted the data from the old column and inserted it into the new column, dropped the old column, renamed the new column to the old column. This immediately broke code that relied on the order of definition. The out going developers said they didn't want to be bothered with typing all the column names. Just lazy.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply