问题描述
我正在尝试将 XML 列中的一些 XML 数据插入到 SQL Server 2012 中的临时表中.
I'm trying to insert some XML data from a XML column into a temp table in SQL Server 2012.
这是我当前的查询
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX) IF OBJECT_ID('tempdb..dbo.#txn','u') IS NOT NULL BEGIN PRINT '#temp exists! drop table' DROP TABLE tempdb.dbo.#txn; END ELSE BEGIN PRINT '#temp does not exist! create table' CREATE TABLE #txn ( accountcode varchar(100), tienda varchar(100), caja varchar(100), cajero varchar(100), fecha varchar(100), transaccion varchar(100), itemcode varchar(100), description varchar(100), quantity numeric(10,3), weight numeric(10,3), qty_weight numeric(10,3), unitprice numeric(15,3), totalprice numeric(15,3), vatcode varchar(100), hashcode varchar(100), anulado varchar(100) ) END SELECT @XML = [LoadedXML] FROM [dbo].[XmlImport] EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML INSERT INTO #txn (accountcode, tienda, caja, cajero, fecha, transaccion, itemcode, description, quantity, weight, qty_weight, unitprice, totalprice, vatcode, hashcode, anulado) SELECT CASE WHEN codigotienda = 1 THEN '01' END as accountcode, tienda, caja, cajero, fecha, transaccion, itemcode, description, quantity, weight, CASE WHEN quantity IS NULL THEN weight WHEN weight IS NULL THEN quantity END as qty_weight, unitprice, totalprice, CASE WHEN vatcode = 4 THEN 'V0' WHEN vatcode = 1 THEN 'V1' WHEN vatcode = 2 THEN 'V2' WHEN vatcode = 3 THEN 'V3' WHEN vatcode is NULL THEN 'V0' END AS vatcode, hashcode, anulado FROM OPENXML(@hDoc, 'tcpos-export/transactions/transaction/trans-item') WITH ( codigotienda [varchar](100) '../shop/code', tienda [varchar](100) '../shop/description', caja [varchar](100) '../till/code', cajero [varchar](100) '../cashier/code', fecha [varchar](100) '../beginning-timestamp', transaccion [varchar](100) '../trans-num', itemcode [varchar](100) 'code', description [varchar](100) 'description', quantity numeric(10,3) 'quantity', weight numeric(10,3) 'weight', unitprice numeric(15,3) 'unit-price', totalprice numeric(15,3) 'taxable-amount', vatcode [varchar](100) 'vat-code', hashcode [varchar](100) 'hash-code', anulado [varchar](100) 'delete-operator-id' ) SELECT * FROM #txn WHERE hashcode IS NOT NULL AND totalprice NOT LIKE '%-%' AND unitprice NOT LIKE '%-%' AND anulado IS NULL ORDER BY CAST(hashcode AS int) --LEFT JOIN [MAXIMERCADODEMO].[dbo].OITM sap --ON #txn.itemcode = sap.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS --where #txn.itemcode is null --SELECT #txn.itemcode FROM #txn --LEFT JOIN [MAXIMERCADODEMO].[dbo].OITM sap --ON #txn.itemcode = sap.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS --where #txn.itemcode is null EXEC sp_xml_removedocument @hDoc
这是第一次有效.当我第二次运行它时,它应该删除临时表,但我收到了这个错误:
This works the first time. When I run it a second time, it should drop the temp table, but I get this error instead:
#temp 不存在!创建表
#temp does not exist! create table
Msg 2714, Level 16, State 6, Line 11
数据库中已经有一个名为#txn"的对象.
Msg 2714, Level 16, State 6, Line 11
There is already an object named '#txn' in the database.
我不知道你们是否建议我使用临时表或在我的数据库中创建一个真实的表来管理这种情况?
I don't know if you guys recommend me using a temp table or create a real table in my database to manage this situation?
推荐答案
这个
IF OBJECT_ID('tempdb..#txn','u') IS NOT NULL
应该
IF OBJECT_ID('tempdb..#txn', 'u') IS NOT NULL DROP TABLE #txn;
你甚至可以逃脱:
IF OBJECT_ID('tempdb..#txn') IS NOT NULL
一旦您进行此更改,您就不再需要为此检查大的 IF 语句.
Once you make this change you no longer need the big IF statement checking for this.