Selecting dynamic number of columns into fixed-structure table
in [Programming SQL]
|
Prev: Alternative to dynamic SQL in Table Valued Function?
Next: SQL Connection from VS 2008 Express to SQLExpress database
From: jtertin on 1 Jul 2010 17:45 I have a table with the following columns (the Pen# columns are all REAL data types and all columns support NULL values): dtDateTime Pen1 Pen2 Pen3 Pen4 Pen5 Pen6 Pen7 Pen8 Pen9 Pen10 I have a stored procedure that returns data similar to the following: dtDateTime TT_S02_20 TT_S02_30 TT_S02_50 ----------------------- ------------- ------------- ------------- 2010-06-26 00:00:06.653 148.1323 115.5447 124.679 2010-06-26 00:00:21.687 148.5214 115.5447 124.7763 .... The number of columns varies from 2 to up to 10. The goal is to have these values inserted into the table identified at the beginning of this message (which will handle up to 10 as currently defined) The issue is that my Insert statement must have NULL values defined in the insert statement for the balance of Pens not included in the stored procedure's output (in the example above, 7 NULLS would be used). This is demonstrated in the "@DeviceList +',NULL,NULL,NULL,NULL,NULL,NULL" section of the following statement (which relies on only 3 TT_S02_XX values being returned by the stored procedure). EXEC ('INSERT INTO tbChartValues (dtDateTime,Pen1,Pen2,Pen3,Pen4,Pen5,Pen6,Pen7,Pen8,Pen9,Pen10) SELECT dtDateTime,'+@DeviceList+',NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM tbAnalogs WHERE CONVERT(varchar,datepart(m,dtDateTime)) +''/''+CONVERT(varchar,datepart(d,dtDateTime)) +''/''+CONVERT(varchar,datepart(yyyy,dtDateTime))='''+@strDate +'''ORDER BY dtDateTime') The goal is to have the records inserted into the "tbChartValues" table regardless of the number of columns returned by the stored procedure. I could potentially "count" the columns in the results of the stored procedure by parsing the comma delimited string specified as "@DeviceList" above and adding the necessary number of NULLs dynamically in the query, but this seems very inefficient and unnecessary. I am wondering if there may be some variation of the "DEFAULT" keyword that might be used to take care of this - logically meaning "INSERT INTO tbChartValues (dtDateTime,Pen1,Pen2,Pen3,Pen4,Pen5,Pen6,Pen7,Pen8,Pen9,Pen10) the values specified, in order, but use defult (NULL) values for the remaining columns which no data is supplied for from the stored procedure. I hope this makes sense - it is hard to describe what I am trying to accomplish, but I tried.... ANY thoughts are appreciated.
From: Eric Isaacs on 2 Jul 2010 00:22 You're trying to insert data into tbChartValues from tbAnalogs which is apparently the results from the stored procedure? Where does tbAnalogs come into play and how does the data from the stored procedure get into that table? If you need to count the columns in tbAnalogs, you could possibly use the informationschema.columns system view to count the columns in that table. DECLARE @ColumnCount INT SELECT @ColumnCount = COUNT(*) FROM InformationSchema.Columns WHERE Table_Name = 'tbAnalogs' You could then do an IF statement to decide which format of insert to execute. If that doesn't help you, I would suggest you reverse engineer the stored procedure to get the data in the format you need directly from the tables. -Eric Isaacs
From: jtertin on 2 Jul 2010 10:54 tbAnalogs is a table that has TT_SXX_XX values in it (including TT_S02_20, TT_S02_30, TT_S02_50 used above, but also including TT_S04_20, TT_S04_30, TT_S04_40, TT_S04_50, TT_S04_60, etc.). The stored procedure returns the columns which correspond to a specific "SXX" number (System number). The number of columns returned by the stored procedure varies (3 for S02 and 5 for S04 as indicated in the above example). The challenge is to insert these into the Pens table (structure defined in original post) regardless of the number of columns returned by the stored procedure.
From: Eric Isaacs on 2 Jul 2010 16:15 Do you have control of the stored procedure at all? Or do you know which SXX number is being used before the stored procedure is called? If so, you can determine how many columns will be returned by the stored procedure before it executes, therefore you can do something like this... DECLARE @SColumnNumber CHAR(2) DECLARE @ColumnCount INT --Determine which S column is being used. SET @SColumnNumber = '04' --Determine the count of those columns that will be returned SELECT @ColumnCount = COUNT(*) FROM InformationSchema.Columns WHERE Table_Name = 'tbAnalogs' and Column_Name LIKE ('TT_S' + @SColumnNumber + '%') If @ColumnCount = 3 BEGIN END ELSE IF @ColumnCount = 4 BEGIN END ELSE IF @ColumnCount = 5 BEGIN END If you can't determine the column count before calling the stored procedure, you'll need the stored procedure to figure it out for you and return that possibly as an output parameter. I have to say that this seems like something that could be improved by a different table design or stored procedure design. -Eric Isaacs
From: Erland Sommarskog on 2 Jul 2010 18:21
jtertin (jtertin(a)gmail.com) writes: > The issue is that my Insert statement must have NULL values defined in > the insert statement for the balance of Pens not included in the > stored procedure's output (in the example above, 7 NULLS would be > used). This is demonstrated in the "@DeviceList > +',NULL,NULL,NULL,NULL,NULL,NULL" section of the following statement > (which relies on only 3 TT_S02_XX values being returned by the stored > procedure). > > EXEC ('INSERT INTO tbChartValues > (dtDateTime,Pen1,Pen2,Pen3,Pen4,Pen5,Pen6,Pen7,Pen8,Pen9,Pen10) SELECT > dtDateTime,'+@DeviceList+',NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM > tbAnalogs WHERE CONVERT(varchar,datepart(m,dtDateTime)) > +''/''+CONVERT(varchar,datepart(d,dtDateTime)) > +''/''+CONVERT(varchar,datepart(yyyy,dtDateTime))='''+@strDate > +'''ORDER BY dtDateTime') > > The goal is to have the records inserted into the "tbChartValues" > table regardless of the number of columns returned by the stored > procedure. I could potentially "count" the columns in the results of > the stored procedure by parsing the comma delimited string specified > as "@DeviceList" above and adding the necessary number of NULLs > dynamically in the query, but this seems very inefficient and > unnecessary. I am wondering if there may be some variation of the > "DEFAULT" keyword that might be used to take care of this - logically > meaning "INSERT INTO tbChartValues > (dtDateTime,Pen1,Pen2,Pen3,Pen4,Pen5,Pen6,Pen7,Pen8,Pen9,Pen10) the > values specified, in order, but use defult (NULL) values for the > remaining columns which no data is supplied for from the stored > procedure. In a relational database you are supposed to do things in certain ways. If you make a design in your own way, it usually hurts. Think for a second: what if those columns were rows instead? Wouldn't everything be a lot simpler then? As long as you have that design, it will be messy. I would suggest that your best bet is make the procedure call and produce the INSERT from a stored procedure in C# or VB .Net. They are after a lot better on string manipulation than T-SQL. And, yes, the columns list in an INSERT statement much match the result set you are inserting from. There is no such feature you are asking for. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |