Reputation: 35
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.
Upvotes: 0
Views: 12737
Reputation: 18316
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.
Upvotes: 5
Reputation: 48066
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
Upvotes: 1