0

In a table there are like 113 columns. and there are two default records in the table, one is for unknown and another is for inapplicable. So, each column has its own default value to represent unknown and inapplicable.

I dont wanna write regular insert statement to get those two records.

so, I tried to insert each column using a cursor.

Got the names of columns for that table from information_schema.columns and tried to insert values from exact table in another location using "insert into select" statement, but the name of the columns that we get from information_schema 

Declare @col_name varchar(50) declare my_cur CURSOR for select column_name from information_schema.columns where table_name = 'tabl' and table_catalog = 'db' and table_schema = 'dbo' Fetch next from my_cur into @col_name while @@FETCH_STATUS = 0 BEGIN Insert into db.dbo.tabl (***@col_name***) select ***@col_name*** from openrowset('sqlncli', 'server=my_server; trusted_connection=yes;', db.dbo.tabl) fetch next from my_cur into @col_name end close my_cur deallocate my_cur go

But, I did not realize that @col_name would be treated as string, rather than object (column)

Is there any work around for this case or any alternative solution.

Improve this question
4
  • 5
    Did you leave the database design to the secretary at the front desk? Commented Jan 5, 2010 at 20:26
  • the table is a dimension table in a data warehouse. So, can't complain. Commented Jan 5, 2010 at 20:33
  • 1
    "In a table there are like 113 columns"... I think you should stop right there, follow everyone else's advice, and reconsider your design. Commented Jan 5, 2010 at 20:45
  • These could be sparse columns in SQL 2008. 113 columns might be just fine. I think he deserves an upvote for planning to get rid of the cursor. Commented Jan 6, 2010 at 1:54

2 Answers 2

Reset to default
5

I think that getting these defaults populated is the least of your problems.

I'd suggest taking a look at this: Fundamentals of Relational Database Design

And if you still want to do this, it might be better to retrieve all the defaults from the linked server, place them in a temp table, and then join to information_schema.columns to populate your table. You'll probably need to transpose the data to make it work.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Yeah, there are strong hints there that 113 columns with values that aren't related is a bit of a DESIGN FLAW
I ll definitely tell my developers to reconsider their design. However, I want ya'll to know that it is a dimension table in a data warehouse.
Ah, that could change things a bit. But what does your proposed table structure look like? Seems like the cursor you posted would give you 113 rows, each with a single column (out of 113) populated. Do you plan to somehow transform this into a more useful structure aftwerwards?
If the way the data naturally needs to be used isn't the way it's stored, then does it really matter if it's a data warehouse dimension table?
@AlexCuse and @Emtucifor: I dont think factoring that table will help out some way. Because that table would be used in a SSAS cube as a dimension. If factored out, then there would be multiple dimensions.
|
1

You will have to generate the INSERT statement as dynamic SQL and then execute it

Declare @InsertStatement VarChar (Max) SET @InsertStatement = '' SET @InsertStatement = @InsertStatement + ' Insert into db.dbo.tabl (' + @col_name + ') ' SET @InsertStatement = @InsertStatement + ' select ' + @col_name + ' from openrowset(''sqlncli'', ''server=my_server''; ' Exec Sp_SQLExec @InsertStatement
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.