我如何填充临时表.. [英] How do I populate temp table..
问题描述
嘿
1)临时表不是由程序创建的。
2)即使我尝试使用tempdb..tablename但没有帮助
3)所以我修改了代码采用永久表而不是临时表..
和代码工作和永久表填充..
任何人都可以帮忙....
我不知道为什么没有创建temmp表?
提前致谢..
我尝试过:
更改程序USP_FONM_HM
(
@DB_NAME NVARCHAR(MAX), - 数据库名称
@FORMAT_TABLE NVARCHAR(MAX),
@FORM_NODUPS NVARCHAR(MAX) - 删除没有桌子
)
AS
- @ SQL_NO_DUPS_ID NVARCHAR(MAX) - 来自上一篇文章的FETCH MAX ID
BEGIN
DECLARE @SQL_REMOVE_NODUPS NVARCHAR(MAX) - 临时表
P RINT' - 删除重复 - '
BEGIN
SET @ SQL_REMOVE_NODUPS =
CONCAT
(
'DROP TABLE',SPACE(1),'#',@ FORMAT_NODUPS,'
; WITH SAMPLECTE
AS
(
SELECT A. *,ROW_NUMBER()超过(FIRST_NAME,LAST_NAME,ADDRESS,FIRST_NAME的邮政订单分区)作为RNUM
FROM',SPACE(1),@ DB_NAME,'..',@ FORMAT_TABLE,'A
)
SELECT * INTO',SPACE(2),'#',@ FORMAT_NODUPS,'
来自SAMPLECTE,其中RNUM =','1','
'
)
结束
打印@SQL_REMOVE_NODUPS
EXEC(@SQL_REMOVE_NODUPS)
结束
------------------
EXEC USP_FONM_HM'PRAC','TEST_FORMAT','TEST_FORMAT_NODUPS'
Hey
1) Temp table ain't getting created by procedure.
2) even i tried using tempdb..tablename but not helpfull
3) So i modified code taken permanent table instead of temp table..
and code works and permanent table gets populated..
Can anyone please help....
I don't know why temmp table ain't getting created ?
Thanks in advance..
What I have tried:
ALTER PROCEDURE USP_FONM_HM
(
@DB_NAME NVARCHAR (MAX), -- DB NAME
@FORMAT_TABLE NVARCHAR (MAX),
@FORMAT_NODUPS NVARCHAR(MAX) -- REMOVE NO DUPS TABLE
)
AS
--@SQL_NO_DUPS_ID NVARCHAR (MAX) -- FETCH MAX ID FROM PREVIOUS SCRIPT
BEGIN
DECLARE @SQL_REMOVE_NODUPS NVARCHAR (MAX) -- TEMPORARY TABLE
PRINT '-- REMOVE DUPLICATES --'
BEGIN
SET @SQL_REMOVE_NODUPS=
CONCAT
(
'DROP TABLE ',SPACE(1),'#',@FORMAT_NODUPS,'
;WITH SAMPLECTE
AS
(
SELECT A.* , ROW_NUMBER () OVER ( PARTITION BY FIRST_NAME , LAST_NAME , ADDRESS, ZIP ORDER BY FIRST_NAME) AS RNUM
FROM ',SPACE(1), @DB_NAME,'..',@FORMAT_TABLE ,' A
)
SELECT * INTO',SPACE(2) ,'#',@FORMAT_NODUPS , '
FROM SAMPLECTE WHERE RNUM=','1','
'
)
END
PRINT @SQL_REMOVE_NODUPS
EXEC (@SQL_REMOVE_NODUPS)
END
------------------
EXEC USP_FONM_HM 'PRAC','TEST_FORMAT','TEST_FORMAT_NODUPS'
推荐答案
如果它已经存在,将不会创建它。事实上,你应该得到一个错误。
此外,即使你将一个字符串定义为NVARCHAR(MAX),你应该在字符串本身之前加上一个N,就像所以:
It won't be created if it already exists. In fact, you should get an error.
Furthermore, even though you define a string as NVARCHAR(MAX), you should precede the string itself with an N, like so:
DECLARE @query NVARCHAR(MAX) = N'...';
运行没有N的动态查询可能会导致查询非常长切断并且运行不正确。
我也养成了在创建临时表之前的习惯(这是CYA之一):< br $>
Running a dynamic query without the "N" may cause a really long query to be cut off and not run correctly.
I also make it a habit to precede the creation of a temp table with this (it's one of those CYA things):
IF UNIQUE_ID('tempdb..#tempTable', 'U') IS NOT NULL DROP TABLE #tempTable;
这篇关于我如何填充临时表..的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!