插入带有两个相关表的 SQL 命令
我的网站上有 3 张桌子。
- 用户
- 线程
- 评论
我将评论主键连接到线程评论字段。我想在向特定线程提供注释时使用 insert into 命令。
命令怎么写啊？！？
是这样的：
string myCommand="INSERT INTO [Threads].[Comments] VALUES(....";
消息会被插入到特定的线程中吗？ 如果我想同时向两者插入数据怎么办...例如，线程的标题和评论的日期...我可以将两个命令合并为一个吗？
i have 3 tables in my site.
- Users
- Threads
- Comments
I connected the comments primary key to Threads comments field. I want to use insert into command while feeding comments to specific threads.
How do i write the command?!?
is it like this:
string myCommand="INSERT INTO [Threads].[Comments] VALUES(....";
Will the messages be inserted into a specific thread?
What if i want to insert data to both simultaneously.. e.g. a headline to a thread and a date to the comment...can i combine two commands into one?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您已经从后到前设置了外键，如果线程表中有一个外键链接到评论表的主键，那么一条评论可以与多个线程相关，但一个线程只能有一个评论。
You have set up your foreign key back to front, if you have a foreign key in the threads table that links to the primary key of the comments table then one comment can relate to many threads but a thread can only have one comment.
您必须创建两个单独的 INSERT 语句。您可以将它们包装在一个事务中，以确保除非它们都成功，否则两者都不会提交。
You have to create two separate
INSERTstatements. You can wrap them in a transaction to ensure that neither are committed unless they are both successful.您仍然需要两个 INSERT 语句，但听起来您想从第一个插入中获取 IDENTITY 并在第二个插入中使用它，在这种情况下，您可能需要查看 OUTPUT 或 OUTPUT INTO: http://msdn.microsoft.com/en-us/library/ms177564.aspx
（我的回答之前提出的同一问题： SQL Server：是否可以同时插入两个表？）
You still need two INSERT statements, but it sounds like you want to get the IDENTITY from the first insert and use it in the second, in which case, you might want to look into OUTPUT or OUTPUT INTO: http://msdn.microsoft.com/en-us/library/ms177564.aspx
(my answer to the same question previously asked: SQL Server: Is it possible to insert into two tables at the same time?)
我将创建一个存储过程并将两个插入放入 SP 内的一个事务中。您可以使用 @@SCOPE_IDENTITY 获取 ID插入到线程中并在插入评论表中使用它：
您可以使用 交易 如前所述，使其更加稳健。使用 SQL 命令从 C# 代码调用 SP。
I'd create a stored procedure and put both insert's into one transaction within the SP. You can use @@SCOPE_IDENTITY to get the ID from the insert into threads and use that in your insert into the comments table:
You could use a transaction as previously stated to make it more robust. Call your SP from your C# code using a SQL command.
使用两个命令插入主题和评论。首先插入到线程中并获取 id：
然后使用线程 ID 插入到注释中
在单个 INSERT 命令中以某种方式完成此操作没有真正的价值或意义。
编辑将每个评论建议的@@IDENTITY更改为SCOPE_IDENTITY()。谢谢！
Use two commands to insert into Threads and Comments. First insert into Threads and grab the id:
Then insert into comments using the thread ID
There is no real value or point in somehow accomplishing this in a single INSERT Command.
Edit Changed @@IDENTITY to SCOPE_IDENTITY() per comment suggestions. Thanks!