用光标插入语句
一个表大约有 113 列。表中有两条默认记录，一条为未知，另一条为不适用。因此，每一列都有自己的默认值来表示未知和不适用。
我不想编写常规插入语句来获取这两条记录。
所以，我尝试使用光标插入每一列。
从 information_schema.columns 获取该表的列名，并尝试使用“insert into select”语句将精确表中的值插入到另一个位置，但是我们从 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
但是，我没有意识到 @ col_name 将被视为字符串，而不是对象（列）
对于这种情况或任何替代解决方案是否有任何解决方法。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为填充这些默认值是最不重要的问题。
我建议看看这个： 关系数据库设计基础
和如果您仍然想这样做，最好从链接服务器检索所有默认值，将它们放入临时表中，然后加入 information_schema.columns 以填充您的表。您可能需要转置数据才能使其正常工作。
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.
您必须将 INSERT 语句生成为动态 SQL，然后执行它
You will have to generate the INSERT statement as dynamic SQL and then execute it