天道酬勤,学无止境

insert-into

活动记录插入(Active Record Insert Into)

问题 我试图让我的 Rails 应用程序在创建关系方面具有一些高级功能。 我正在运行 MySQL 作为后端。 下面的查询在 PHPMyAdmin 中运行得很好,但是在 Rails 中尝试执行时,它什么也不做,没有错误,也没有数据库写入...... 我使用活动记录来创建 Campaign 和 Location 之间的关系,然后下面的查询应该可以在之后在 campaign_metros 表中构建: class CampaignLocation < ActiveRecord::Base belongs_to :campaign belongs_to :location after_touch :create_campaign_metro private def create_campaign_metro @sql_insert="INSERT INTO `campaign_metros` (`campaign_id`,`metro_id`, `created_at`,`updated_at`) SELECT f.`campaign_id`, f.`city_id`, NOW(), NOW() FROM (SELECT d.`campaign_id`, d.`city_id` FROM (SELECT c.`campaign_id`, c.`city_id` FROM (SELECT

2021-09-16 19:27:07    分类:技术分享    mysql   ruby-on-rails   subquery   insert-into

在插入时在 SQL 中自动生成唯一标识符的问题(Issues with getting uniqueidentifier auto generate in SQL on insert)

问题 我正在尝试将一个表中的数据插入到另一个表中。 表 1 没有唯一标识符,但表 2 有。 我尝试使用NEWID()插入,但出现语法错误。 有人可以告诉我我做错了什么吗? INSERT INTO Table2 (NEWID(), [Item Description], [Item Cost]) SELECT Description, Cost FROM Table1 WHERE Id = '1' 回答1 如果尚未创建 Table2,您可以使用以下查询: SELECT NEWID() AS [ID] ,Description AS [Item Description] ,Cost AS [Item Cost] INTO Table2 FROM Table1 WHERE Id = '1' 但是,如果表 2 的架构已经创建,并且它有一列用于标识符,那么您可以使用: INSERT INTO Table2 ([ID], [Item Description], [Item Cost]) SELECT NEWID() , Description , Cost FROM Table1 WHERE Id = '1' 但是,如果您尚未为 Table2 创建架构,那么我建议使用以下代码为表创建架构并使用Table1的数据填充其中的数据。 CREATE TABLE Table2 ( [ID] INT

2021-09-16 10:19:08    分类:技术分享    sql   sql-server   uniqueidentifier   auto-generate   insert-into

创建一个表单以正确使用 PDO POST 到 MySQL?(Creating a Form to POST into MySQL using PDO properly?)

问题 我正在尝试使用 PDO 包装器类,并且通常将针对 SQL 注入的适当安全性作为良好实践。 试图学习填写表格以 PO​​ST 到 MySQL 的干净基本要素。 因此,在某一时刻,我的表单将数据插入到 MySQL 表中,但在提交后刷新时做了多条记录。 然后我研究了一种更简洁的方法来编写处理器,但现在无法将其插入表中。 不确定处理器和“class.db.php”文件之间是否存在差异? 我已经搜索了很多“操作方法”,但在一致的答案中没有成功。 试图了解我做错了什么,希望得到最佳实践的答案。 我所看到的一切都在地图上。 这是我所在的位置:作为参考,我首先从这里开始 http://webdevelopingcat.com/php-mysql-tutorial-for-beginners-inserting-rows-with-pdo/ 然后在文档的顶部,如果你使用谷歌,https://code.google.com/p/php-pdo-wrapper-class/ 项目作为类实现的基础。 <?php include("class.db.php"); $version = "1.0.2"; $released = "December 9, 2010"; ?> 然后在体内形成一个简单的形式。 <?php if ( empty( $_POST ) ){ ?> <form name=

2021-09-16 05:36:59    分类:技术分享    php   mysql   pdo   prepared-statement   insert-into

从访问中提取数据到链接的 SQL Server 的 INSERT INTO 语句中的 F# OleDb 语法错误(F# OleDb Syntax Error in INSERT INTO Statement Pulling Data from Access to Linked SQL Server)

问题 我正在运行 F# 应用程序以将数据从 Access 表中提取到链接的 SQL Server 表。 这是结果查询: INSERT INTO dbo_TempTerm (UnitID, PolicyTermYear, InsuredName, PolicyNumber, RenewalDate, CovATotal, CovBTotal, CovLTotal, DwellExtn, AllOtherPerilDeductible, MedPay, TotalPremium, HurricaneDeductible, Zone, Subzone, PercentCRC, PercentCRD, YearBuilt, RenYrs, PercentCFD, PercentHA, PercentMLD, GRP1, PercentNH, QCLM, RateV, CRI, AgentCode, AgentName, AFOCode, PolicyType, PolicyForm, OC, DateCreated) SELECT [UNIT ID], [POLICY TERM YEAR], [INSURED NAME], [POLICY #], [RENEWAL DT], [COV A TOTAL], [COV B TOTAL], [COV L TOTAL], [DWELL EXTN]

2021-09-12 09:12:54    分类:技术分享    f#   sql-server-2008-r2   oledb   ms-access-2010   insert-into

Insert into with union all and nextval doesn't work with duplicate values

I want to insert 2 lines in a table within one insert into statement with Oracle SQL. This code works: insert into a_glw select tt.*, work_id_seq.nextval from (select 11111, 'one text', 12345, 'new text', NULL, 'some text', 'nice text', 'test', 'text', 'great text' from dual union all select 11111, 'one text', 12345, 'new text', NULL, 'some text', 'nice text', 'test', 'text', 'great text' from dual) tt; When I change the value test to text this code produces the error 00918. 00000 - "column ambiguously defined": insert into a_glw select tt.*, work_id_seq.nextval from (select 11111, 'one text'

2021-09-08 08:04:03    分类:问答    sql   oracle   insert-into   union-all

Active Record Insert Into

I am trying to allow my Rails App some advanced functionality in way of creating relationships. I am running MySQL as the back-end. The query below runs just fine in PHPMyAdmin, but upon attempted execution in rails, it does nothing, no errors and no database writes.... I use active record to create the relationships between Campaign and Location, and then the query below should work to build afterward in campaign_metros table: class CampaignLocation < ActiveRecord::Base belongs_to :campaign belongs_to :location after_touch :create_campaign_metro private def create_campaign_metro @sql_insert=

2021-09-06 04:27:17    分类:问答    mysql   ruby-on-rails   subquery   insert-into

Issues with getting uniqueidentifier auto generate in SQL on insert

I am trying to insert data from one table into another table. Table1 doesn't have a uniqueidentifier, but Table2 does. I have tried to insert using NEWID(), but I get a syntax error. Can someone please tell me what I am doing wrong? INSERT INTO Table2 (NEWID(), [Item Description], [Item Cost]) SELECT Description, Cost FROM Table1 WHERE Id = '1'

2021-09-05 15:03:41    分类:问答    sql   sql-server   uniqueidentifier   auto-generate   insert-into

F# OleDb Syntax Error in INSERT INTO Statement Pulling Data from Access to Linked SQL Server

I'm running an F# application to pull data from an Access table to a linked SQL Server table. Here is the resulting query: INSERT INTO dbo_TempTerm (UnitID, PolicyTermYear, InsuredName, PolicyNumber, RenewalDate, CovATotal, CovBTotal, CovLTotal, DwellExtn, AllOtherPerilDeductible, MedPay, TotalPremium, HurricaneDeductible, Zone, Subzone, PercentCRC, PercentCRD, YearBuilt, RenYrs, PercentCFD, PercentHA, PercentMLD, GRP1, PercentNH, QCLM, RateV, CRI, AgentCode, AgentName, AFOCode, PolicyType, PolicyForm, OC, DateCreated) SELECT [UNIT ID], [POLICY TERM YEAR], [INSURED NAME], [POLICY #], [RENEWAL

2021-09-05 11:12:14    分类:问答    f#   sql-server-2008-r2   oledb   ms-access-2010   insert-into

SQL Server 2008: Sql Insert/Update into another table using insertion IDs output from another table

I have a procedure for insert in multiple dependent tables (update in case record exist). I have input parameters as comma separated string which I am reading in table. After 1st insertion I am getting InsertedIds in another table variable. I am struggling over how to do insert in 2nd table. I have following input parameters for 2nd table: Declare @IdsToBeUpdated table (primary key identity pkey, id int) -- values are 1,-1,3,-1 Declare @CommentsTobeInserted table( primary key identity pkey, comment varchar (max)) -- values are 'com1', 'com2', 'com3' -1 input in table @IdsToBeUpdated depicts

2021-09-04 20:23:06    分类:问答    sql   sql-server   sql-update   sql-insert   insert-into

INSERT INTO SELECT gives: Column name or number of supplied values does not match table definition

I'm getting this error message when executing the code below. Column name or number of supplied values does not match table definition. --DROP TABLE #UniqueBuildingUsageFkAndBuildingFk CREATE TABLE #UniqueUsageFkAndBuildingFk ( Building_fk INT NOT NULL ,Usage_fk INT NOT NULL DEFAULT(0) ); ;WITH UnknownUsageFkAndBuildingFkCTE AS ( SELECT DISTINCT mm.Building_fk, 0 AS Usage_fk FROM Customers.StandardizedRecord sr JOIN Customers.MidasMatch mm ON mm.CustomersBuildingNumberKey = sr.CustomersBuildingNumberKey WHERE sr.CustomersBuildingNumberKey NOT IN (SELECT DISTINCT emm.CustomersBuildingNumberKey

2021-09-04 10:44:06    分类:问答    sql-server   common-table-expression   insert-into