September 26, 2017 at 2:42 pm
Not sure if this is possible. I have one table where the field names may bemodified or new fields added each year. Is there a way to make the naming of the field names in my query dynamicso that when they are changed in the table, the code does not need to behard-coded?
For instance, the INSERT INTO and the SELECTstatements listing the field names would be dynamic for this table.
Thank you for your suggestions.
September 27, 2017 at 2:00 am
If your column names are always in flux, then how are you proposing you INSERT data into them if you don't know what columns are going to be present. You might end up trying to INSERT into columns that don't exist or not inserting into columns that are non nullable.
You need to know the definition of INSERT into it. If your DDL is always going to be changing, I'd suggest speaking to who ever is changing it so that you can work together to ensure that you are ready for any updates that are made to the table prior to the change actually being deployed to your live environment.
On the subject of returning all the columns, SELECT * does this. Just be careful as, for example, some object that reference it won't pick up the new columns if you use SELECT *. For example, a view that uses SELECT * won't see a new column added after the view's creation, unless the view's definition is updated. For example:USE Sandbox;
GO
CREATE TABLE SampleTable (ID int);
GO
INSERT INTO SampleTable
VALUES (1),(2),(3),(4),(5);
GO
CREATE VIEW SampleView AS
SELECT *
FROM SampleTable;
GO
ALTER TABLE SampleTable ADD String varchar(5);
GO
SELECT *
FROM SampleTable;
SELECT *
FROM SampleView;
GO
DROP VIEW SampleView;
DROP TABLE SampleTable;
GO
Notice that the second dataset only returns ID, even though the VIEW is using the statement SELECT *. That's because the VIEW has not been updated.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 27, 2017 at 6:25 am
Danie8 - Tuesday, September 26, 2017 2:42 PMNot sure if this is possible. I have one table where the field names may bemodified or new fields added each year. Is there a way to make the naming of the field names in my query dynamicso that when they are changed in the table, the code does not need to behard-coded?
For instance, the INSERT INTO and the SELECTstatements listing the field names would be dynamic for this table.
Thank you for your suggestions.
One of the inherent problems of allowing tables to operate in this manner is that they become what I'll refer to as "temporally dependent". Basically, when you look at the table determines what column names you see. There's no particularly good reason to EVER use a table structure like that, as there are plenty of well-known (think dynamic cross-tab) and well-performing methods to query a table structure that allows time-based data to occupy new rows instead of new columns. Continuing to support this kind of data structure is in the "genuinely bad idea" category.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 27, 2017 at 8:12 am
Hi Steve,
I agree with you that "Continuing to support this kind of data structure is in the "genuinely bad idea" category." My DBA is suggesting I use
SELECT * FROM sys.columns WHEREobject_id=OBJECT_ID(‘dbo.myTableName’) to get column names and this way, if they change, I will have the most current columns. The problem even with this is that the code refers to specific column names to conduct its processing. If the column name has changed, the code will fail. I don't see a way to support this type of variation in column names for this table and what to explain to the DBA..
September 27, 2017 at 8:29 am
Danie8 - Wednesday, September 27, 2017 8:12 AMHi Steve,
I agree with you that "Continuing to support this kind of data structure is in the "genuinely bad idea" category." My DBA is suggesting I use
SELECT * FROM sys.columns WHEREobject_id=OBJECT_ID(‘dbo.myTableName’) to get column names and this way, if they change, I will have the most current columns. The problem even with this is that the code refers to specific column names to conduct its processing. If the column name has changed, the code will fail. I don't see a way to support this type of variation in column names for this table and what to explain to the DBA..
Exactly. There's NO good way to explain it. It makes no sense. Also, you wouldn't be unable to use a reporting tool, such as SSRS, because the column names can't change and the report still works. I'd even go so far as to say that continuing to make ANY use whatsoever of that kind of data structure is potentially in the negligence category, and not just a bad idea, but a horrid one.
Edit for correct version of the word. Previously "would", now "wouldn't", following "Also, you "
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 27, 2017 at 8:38 am
Danie8 - Wednesday, September 27, 2017 8:12 AMHi Steve,
I agree with you that "Continuing to support this kind of data structure is in the "genuinely bad idea" category." My DBA is suggesting I use
SELECT * FROM sys.columns WHEREobject_id=OBJECT_ID(‘dbo.myTableName’) to get column names and this way, if they change, I will have the most current columns. The problem even with this is that the code refers to specific column names to conduct its processing. If the column name has changed, the code will fail. I don't see a way to support this type of variation in column names for this table and what to explain to the DBA..
That's because, honestly, you can't support that kind of set up. It's a terrible idea, and has all sorts of problems, you're just finding out some now.
Like Steve said, you need to either look at a different solution entirely. One option, like Steve suggested is the "reuse" of columns and their meaning depends on the date of the data. Another would be the only INCREASE the width of your table, but not remove older columns, that means the data isn't lost.
Another option (although I don't like it), it to move away from a 3NF form. :sick: Instead, your table ends up being very tall, and not very wide, and you use a field to denote the field. I really don't like that solution though, as everything ends up being a big mess, data types aren't retained, and queries often perform awfully. I really don't know why I'm even putting the idea in your head in all honesty...
Your DBA should really be aware of how bad the solution you have now is. Data integrity and sustainability are crucial in any data structure, plus even retain-ability is important (as you often need to know how data looked on a particular day, and deleting columns just ruins that) and they seem to be throwing these ideas out the window.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 27, 2017 at 9:27 am
Thank you both for your feedback/suggestions. Great advice!
September 27, 2017 at 10:44 am
I lost a fight over a table design for getting data from a Lotus Notes database into a SQL Server database simply because the Lotus Notes developer couldn't figure out how to pivot the data so that new columns in Lotus Notes would be inserted as rows in a SQL Server database. I said I couldn't support the design they were forcing in SQL Server. Can't remember what happened with the SQL Server database, been too long.
September 28, 2017 at 9:21 am
As much as I don't recommend this method, I believe it should be possible if you don't mind using a stored procedure for selecting from or insterting into the table.
For select, you could do something like:CREATE PROCEDURE [dbo].[tableSelect]
@tableName VARCHAR(255)
AS
DECALRE @sql NVARCHAR(MAX)
DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns+', ','') + [sys].[columns].[name]
FROM [sys].[columns]
JOIN [sys].[tables] on [tables].[object_id] = [columns].[object_id]
WHERE [sys].[tables].[name] LIKE @tableName
SELECT @sql = 'SELECT ' + @columns + ' FROM ' + @tableName
EXEC sp_executesql @sql
GO
And you'd do something similar for inserts:CREATE PROCEDURE [dbo].[tableInsert]
@tableName VARCHAR(255),
@tableValues VARCHAR(max)
AS
DECLARE @sql NVARCHAR(MAX)
DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns+', ','') + [sys].[columns].[name]
FROM [sys].[columns]
JOIN [sys].[tables] on [tables].[object_id] = [columns].[object_id]
WHERE [sys].[tables].[name] LIKE @tableName
SELECT @sql = 'INSERT INTO ' + @tableName + '(' + @columns + ') VALUES ('+@tableValues+')'
EXEC sp_executesql @sql
GO
If the table name is static, you could hard-code it in both of these too. It might not hurt to also add in a parameter for the schema as if you add the schema to the @tablename variable, it won't get you any columns. And putting in some error and sanity checking (such as if @columns is null, don't try to select or insert or if the tableValues list is null, don't try to insert). I didn't test the table insert one, but the table select one seems to work from my limited testing.
The @tableName variable is simply the name of the table you want to select from or instert into.
@tableValues would be a comma separated list of values you want to insert into the table. You would likely need to do sanitation on the tableValues variable before you ran the above so you didn't end up accidentally escaping them when calling the SP.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 28, 2017 at 9:58 am
What about using what I like to call a 'stacked' table to put this in? You use the unpivot, in combination with a prior post on getting the column names. You then have a column in your table that will hold the column name from your input file and another column then contains the value of that field.
Sorry for not giving more detail, need to run, I'll check back later.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply