Example of SQL Server inserting data into temporary tables

Time:2021-11-25
Copy codeThe code is as follows:
INSERT INTO #DirtyOldWIPBOM SELECT TOP (100) PERCENT dbo.WIP_BOM.Model, dbo.WIP_BOM.PartNumber,WIP_BOM.WIP
FROM dbo.WIP_BOM left OUTER JOIN dbo.BOM_CHINA ON LTRIM(dbo.WIP_BOM.Model) = LTRIM(dbo.BOM_CHINA.Model) AND LTRIM(dbo.WIP_BOM.PartNumber) = LTRIM(dbo.BOM_CHINA.PartNumber) AND dbo.WIP_BOM.Qty = dbo.BOM_CHINA.Qty AND BOM_CHINA.WIP=
WIP_BOM.WIP WHERE (dbo.BOM_CHINA.Model IS NULL) AND EXISTS(SELECT * FROM dbo.BOM_CHINA WHERE WIP = WIP_BOM.WIP AND LTRIM(Model) = LTRIM(WIP_BOM.Model))

So you can succeed.

Copy codeThe code is as follows:
But select top (100) percent dbo.wip_ BOM.Model, dbo.WIP_ BOM.PartNumber,WIP_ BOM.WIP into #DirtyOldWIPBOM
FROM dbo.WIP_BOM left OUTER JOIN dbo.BOM_CHINA ON LTRIM(dbo.WIP_BOM.Model) = LTRIM(dbo.BOM_CHINA.Model) AND LTRIM(dbo.WIP_BOM.PartNumber) = LTRIM(dbo.BOM_CHINA.PartNumber) AND dbo.WIP_BOM.Qty = dbo.BOM_CHINA.Qty AND BOM_CHINA.WIP=
WIP_BOM.WIP WHERE (dbo.BOM_CHINA.Model IS NULL) AND EXISTS(SELECT * FROM dbo.BOM_CHINA WHERE WIP = WIP_BOM.WIP AND LTRIM(Model) = LTRIM(WIP_BOM.Model))

Error “there is already an object named ‘#dirtyoldwipbom’ in the database” will be prompted

Why?

[note]: never use cursors. Replacing cursors with temporary tables and table variables will greatly improve performance. Today, it took 2 hours to process 700 rows of data with cursors, and it took less than 1 second to use temporary tables.

Recommended Today

Collection common API

//Collection common API Collection list = new ArrayList<>(); //1. Adding elements successfully returns true regardless of the return value (here refers to list) list.add(“Java”); list.add(“HTML”); list.add(“HTML”); list.add(“Java”); list.add(“wsl”); System.out.println(list); //2. Clear the elements of the collection //list.clear(); //System.out.println(list); //3. Determine whether the collection is empty or not, return true and vice versa System.out.println(list.isEmpty()); //false //4. […]