July 22, 2020 at 12:17 am
I bulk insert a file into a temp table each month. The file has about 60 fields but sometimes 2 or 3 fields are missing (if they are missing its the same all the time). I'd like to use the same procedure to read the temp table, whether it has 57, 58, 59 or 60 fields. I played around with this but can't seem to come up with a solution.
In the example the query reads #test1 but not #test2. In this simplified example I'd like the same query to work on both tables (but remember I might have several fields missing). I's like to do something like this (know this does not work):
SELECT A, B, C, CASE IF D EXISTS THEN D ELSE 0 END as D FROM #Test2
IF OBJECT_ID('tempdb..#Test1') IS NOT NULL DROP TABLE #Test1
CREATE TABLE #Test1
(A int, B int, C int, D int)
INSERT INTO #Test1 SELECT 1,1,1,1
INSERT INTO #Test1 SELECT 2,2,2,2
-- SELECT A,B,C,D FROM #Test1
IF OBJECT_ID('tempdb..#Test2') IS NOT NULL DROP TABLE #Test2
CREATE TABLE #Test2
(A int, B int, C int)
INSERT INTO #Test2 SELECT 1,1,1
INSERT INTO #Test2 SELECT 2,2,2
-- SELECT A,B,C,D FROM #Test1
-- SELECT A,B,C,D FROM #Test2
IF OBJECT_ID('tempdb..#FieldExist') IS NOT NULL DROP TABLE #FieldExist
SELECT [name] as FieldExist INTO #FieldExist FROM tempdb.sys.columns WHERE object_id = Object_id('tempdb..#test2')
--this does not work
SELECT CASE WHEN (SELECT * FROM #FieldExist WHERE FieldExist = 'D') IS NULL THEN 0 ELSE D END FROM #test2
Thank you for your help!
July 22, 2020 at 12:38 am
I have one idea I haven't tried yet (think it will work):
(1) import data into temp file
(2) use alter database to add the missing fields if they do not exist
I'd still be open to other ideas. Thanks
July 22, 2020 at 1:14 am
I have one idea I haven't tried yet (think it will work):
(1) import data into temp file
(2) use alter database to add the missing fields if they do not exist
I'd still be open to other ideas. Thanks
I was thinking along the same lines – this is a decent solution. The 100% dynamic solutions will probably require the use of dynamic SQL, whereas this one should not (as long as you have a known list of the columns which may be missing). Personally, I avoid dynamic SQL where possible, so I would go with this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 22, 2020 at 1:16 am
Yes, I was also thinking from a maintenance perspective dynamic would not be the best solution. Today its 3 missing, it might be 5 in the future so adding the missing field using alter is a quick fix.
Thank you.
July 22, 2020 at 1:33 am
This is a simplified example of what I'm going to use as the solution:
IF OBJECT_ID('tempdb..#Test2') IS NOT NULL DROP TABLE #Test2
CREATE TABLE #Test2
(A int, B int, C int)
INSERT INTO #Test2 SELECT 1,1,1
INSERT INTO #Test2 SELECT 2,2,2
--is column D missing?
IF NOT EXISTS (SELECT * from tempdb.sys.columns where object_id = object_id('tempdb..#Test2') AND [name] = 'D')
ALTER TABLE #Test2
ADD D int
--is column E missing?
IF NOT EXISTS (SELECT * from tempdb.sys.columns where object_id = object_id('tempdb..#Test2') AND [name] = 'E')
ALTER TABLE #Test2
ADD E int
--is column F missing?
IF NOT EXISTS (SELECT * from tempdb.sys.columns where object_id = object_id('tempdb..#Test2') AND [name] = 'F')
ALTER TABLE #Test2
ADD F int
--if there is a column C, rename it to CC
IF EXISTS (SELECT * from tempdb.sys.columns where object_id = object_id('tempdb..#Test2') AND [name] = 'C')
EXEC tempdb.sys.sp_rename N'#Test2.C', N'CC', N'COLUMN';
SELECT * FROM #Test2 July 22, 2020 at 1:46 am
Some of your SQL syntax can be refined a little (not that there was anything wrong with yours):
DROP TABLE IF EXISTS #Test2;
CREATE TABLE #Test2
(
A INT
,B INT
,C INT
);
INSERT #Test2
(
A
,B
,C
)
VALUES
(1, 1, 1)
,(2, 2, 2);
IF COL_LENGTH('#Test2', 'D') IS NULL
BEGIN
ALTER TABLE #Test2 ADD D INT;
END;
--Etc.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 22, 2020 at 1:48 am
Thank you, that is a lot cleaner.
July 22, 2020 at 3:37 am
Be sure to specify NULL rather than letting nullability default, because the default might be NOT NULL, which would cause an error.
ALTER TABLE #Test2
ADD D int NULL;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 22, 2020 at 5:11 am
You said that the files that are missing columns are the same except for the missing columns. With that in mind, it's a whole lot easier if you make multiple BCP Format Files to control the Bulk Inserts. You could even control which format file to use by reading just the first row as a blob, splitting it, and checking which columns aren't there according to sys.columns for the table.
Shoot, for that matter, you could build the format files on the fly using a similar method just in case you get a surprise.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 22, 2020 at 5:12 am
You said that the files that are missing columns are the same except for the missing columns. With that in mind, it's a whole lot easier if you make multiple BCP Format Files to control the Bulk Inserts. You could even control which format file to use by reading just the first row as a blob, splitting it, and checking which columns aren't there according to sys.columns for the table.
Shoot, for that matter, you could build the format files on the fly using a similar method just in case you get a surprise.
p.s. Don't use the god-forsaken XML format files. Use the old fashioned ones. It's a whole lot easier to do and read.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 22, 2020 at 5:15 am
Yes, I was also thinking from a maintenance perspective dynamic would not be the best solution. Today its 3 missing, it might be 5 in the future so adding the missing field using alter is a quick fix.
Thank you.
I guess tend to be a bit heterodoxic when it comes to things in SQL. For this type of thing, I'd embrace dynamic SQL because it would be "set it and forget it" forever.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 22, 2020 at 7:59 am
Jeff, I have no control over the file that is sent, comes from an external source. Every month its a different column (or columns) not included. Also, the same column sometimes are is named differently (although the two different names are at least consistent). Today I use different BCP; identify the columns structure then use the respective code. The alter database, add column (or rename column) solution will solve the curve balls thrown at the moment. If a new curve is thrown (ne column I didn't anticipate), I have a automated reconciliation function that will let me know something happened different.
July 22, 2020 at 2:55 pm
Maybe this and use it as a cursor so I can create the columns that way?
use tempdb;
GO
IF OBJECT_ID('tempdb..#Test2') IS NOT NULL DROP TABLE #Test2
CREATE TABLE #Test2
(A int, B int, C int, E int, G int);
GO
SELECT * FROM #Test2;
CREATE TABLE #ColumnNames (columnName CHAR NOT NULL);
GO
INSERT INTO #ColumnNames VALUES ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H');
/* query for missing columns */
SELECT columnName FROM #ColumnNames cn
WHERE NOT EXISTS (
SELECT 1
FROM tempdb.sys.columns tsc
where object_id = object_id('tempdb..#Test2')
AND tsc.name = cn.columnName );
Then I could use a simple cursor to loop over those and create the missing columns using that. (Yeah, I know "cursor" is a dirty word, but seems like an easy solution, and I'm looping over column names, not millions of rows of data.)
July 22, 2020 at 3:25 pm
I like the idea of altering the temp file to include missing columns. It's a simple solution that means the important part, the reads, don't change and you don't get strange edge cases from trying to manage this with dynamic SQL.
I like the idea of explicitly including NULL, and setting data here, or maybe you have a "Not sent" or some other item so you know that this wasn't in the file this time. That might be important downstream to know this wasn't a null/unknown/blank value v not even sent.
July 22, 2020 at 4:22 pm
Steve,
Since it's only looking at column names and not rows of data, it seems this could be done easily with a little bit of dynamic SQL (just get a list of missing columns and add them, or do they have to be in sequence?) Or is way harder than it has to be?
Pieter
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply