Help On Dynamic Sql Results On Temp Table

Nov 21, 2006

The dynamic sql is used for link server. What I need is that the result of the dynamic sql put on a temp table. Can someone help. Im getting an error

CREATE PROCEDURE GSCLink

( @LinkCompany nvarchar(50), @Page int, @RecsPerPage int )

AS

SET NOCOUNT ON

--Create temp table

CREATETABLE #TempTable

(  ID int IDENTITY, Name nvarchar(50), AccountID int, Active bit )

INSERT INTO #TempTable (Name, AccountID, Active)

--dynamic sql

DECLARE @sql nvarchar(4000)

SET @sql = 'SELECT a.Name, a.AccountID, a.Active

                   FROM CRMSBALINK.' + @LinkCompany + '.dbo.AccountTable a

                   LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b

                   ON a.AccountID = b.oaAccountID

                   WHERE oaAccountID IS NULL

                   ORDER BY Name ASC'

EXEC sp_executesql @sql

--Find out the first and last record

DECLARE @FirstRec int

DECLARE @LastRec int

SELECT @FirstRec = (@Page - 1) * @RecsPerPage

SELECT @LastRec = (@Page * @RecsPerPage + 1)

--Return the set of paged records, plus an indication of more records or not

SELECT *, (SELECT COUNT(*) FROM #TempTable TI WHERE TI.ID >= @LastRec) AS MoreRecords

FROM #TempTable

WHERE ID > @FirstRec AND ID < @LastRec

 

Error:

Msg 156, Level 15, State 1, Procedure GSCLink, Line 22

Incorrect syntax near the keyword 'DECLARE'.

View 5 Replies


ADVERTISEMENT

Temp Table Using Dynamic Sql?

May 19, 2005

Does TSQL limits us on creating temp table using dynamic sql? If so any workaround... Here's the sample code that doesn't let me run second exec because it looks like first exec is not able to create a temp table.

declare @str1 varchar(80),@str2 varchar(80)

set @str1='create table #tmp(col1 int)'
set @str2='insert into #tmp values (10)'

exec (@str1)
exec (@str2)

View 2 Replies View Related

How Do I Store Results In TEMP TABLE?

Jul 7, 2005

I'm writing a search engine and I want to make a search and then, after
I've recieved the results, put them into some sort of temporary(in
mememory) table so that I can do another query on that temporary table.

I saw something about  temp tables (in T-SQL)  in the
help guide, but I still want to know how to do a query on a regular
table and then store those results in a temporary table to perform a
query on that. Could anyone show me some example or something?

View 3 Replies View Related

Do QA Results Into A Temp Table Automatically?

Apr 3, 2008

Does anyone know if the results from a query run in QA automatically go into a tempoary table? For instance if ive just run a query that took 5 minutes but it was a normal select query and after it ran i realised i wanted to query the results could i run a simple command to mke a copy of the result set rather than run the original query into a temp table that would then take a few minutes again?

View 1 Replies View Related

Results Of Union Into Temp Table

Apr 3, 2008

This may be a dumb question, but I can't seem to get the syntax right. I have two temp tables that have the same columns, I want to do a union on them and store the results in a temp table. Any ideas?

Ie.

select * from #tmpTable1
union
select * from #tmpTable2
into #tmpTable3

Thanks!!!

View 4 Replies View Related

Help Using Dynamic Queries In Temp Table

Nov 20, 2006

The dynamic sql is used for link server. Can someone help. Im getting an error
CREATE PROCEDURE GSCLink
( @LinkCompany nvarchar(50), @Page int, @RecsPerPage int )
AS
SET NOCOUNT ON
--Create temp table
CREATE TABLE #TempTable
( ID int IDENTITY, Company nvarchar(50), AcctID int, IsActive bit )
INSERT INTO #TempTable (Name, AccountID, Active)
--dynamic sql
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT a.Name, a.AccountID, a.Active
                   FROM CRMSBALINK.' + @LinkCompany + '.dbo.AccountTable a
                   LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b
                   ON a.AccountID = b.oaAccountID
                   WHERE oaAccountID IS NULL
                   ORDER BY Name ASC'
EXEC sp_executesql @sql
--Find out the first and last record
DECLARE @FirstRec int
DECLARE @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
--Return the set of paged records, plus an indication of more records or not
SELECT *, (SELECT COUNT(*) FROM #TempTable TI WHERE TI.ID >= @LastRec) AS MoreRecords
FROM #TempTable
WHERE ID > @FirstRec AND ID < @LastRec
 
Error:
Msg 156, Level 15, State 1, Procedure GSCLink, Line 22
Incorrect syntax near the keyword 'DECLARE'.
 
 

View 3 Replies View Related

Inserting Into Temp Table In Dynamic Sql

Mar 3, 2004

I have a temp table in my stored procedure when I try to insert into the temp table thro dynamic sql, it says that table has to be defined. What could be the problem. i have added the code below

CREATE PROCEDURE USP_RULE
AS
declare @TABLE1 table
(
SlNo int identity(1,1), EqNum varchar(25),Pointnum varchar(25)
)
declare @EqNum varchar(25),@Pointnum varchar(25)
DECLARE @STRDBNAME VARCHAR(50)
SET @STRDBNAME = 'DB1'
EXEC('insert into '+@TABLE1+' select EQNUM,POINTNUM from '+@STRDBNAME+'..TABLE2')

GO

View 4 Replies View Related

Generating Dynamic Temp Table

Jun 9, 2014

I want to generate dynamic temp table so, from one strored procedure am getting an some feilds as shown below

CM_id,CM_Name,[Transaction_Month],[Transaction_Year],''[Invoice raised date],''[Payment Received date],''[Payout date],''[Payroll lock date]

for i want to generate table for the above fields with datatype.

View 2 Replies View Related

Inserting Results From Query Into New Temp Table

Sep 17, 2013

I am try to insert the results from the query into new temp table but keep geeting an error for Incorrect syntax near ')'.

select * into tempCosting
from
(
select top 10* from itemCode itm
where itm.type= 1
)

View 3 Replies View Related

Storing The Stored Procedure Results In A Temp. Table

Mar 28, 2008

All,

I'm trying to store the results of my store procedure in a temp table. when I try it, I got the error saying...

"Insert exec cannot be nested"

I suspsect this is because I have a Insert Exec statement inside my stored procedure...

Is there any way to come over this issue ?

syntax of my code...

create table #Temp1 (ID int)

insert into #Temp1
EXEC SP1

when I try to run the above code I get the error "Insert exec cannot be nested"

SP syntax :

Create Procedure SP1
as
Begin


create table #Temp2
statements.....


Insert into #temp2
exec SP<Name>


staments.. cont...

END

View 5 Replies View Related

T-SQL (SS2K8) :: Returning Stored Procedure Results Into CTE Or Temp Table?

Aug 20, 2013

Is it possible to return the results of a stored procedure into either a CTE or temp table?

In other words, is it possible to do this:

with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???

View 9 Replies View Related

Transact SQL :: Insert Constant Value Along With Results Of Select Into Temp Table?

Dec 4, 2015

I'm trying to fill a temp table whose columns are the same as another table plus it has one more column. The temp table's contents are those rows in the other table that meet a particular condition plus another column that is the name of the table that is the source for the rows being added.

Example: 'permTable' has col1 and col2. The data in these two rows plus the name of the table from which it came ('permTable' in this example) are to be added to #temp.

Data in permTable
col1   col2
11,    12
21,     22

Data in #temp after permTable's filtered contents have been added

TableName, col1   col2
permTable, 11,     12
permTable, 21,     22

What is the syntax for an insert like this?

View 2 Replies View Related

Transact SQL :: Create A Temp Table On Results Of A Pivot Query?

Jun 17, 2015

I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.

IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,

[code]....

The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.".   The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT".  I will post the definitions from another effort below.

CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,

[code]....

SQL Server 2008 R2.

View 3 Replies View Related

Creating A Dynamic Global Temp Table Within A Stored Procedure

Sep 7, 2006

hi,I wish to create a temporary table who's name is dynamic based on theargument.ALTER PROCEDURE [dbo].[generateTicketTable]@PID1 VARCHAR(50),@PID2 VARCHAR(50),@TICKET VARCHAR(20)ASBEGINSET NOCOUNT ON;DECLARE @DATA XMLSET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)CREATE TABLE ##@TICKET (DATA XML)INSERT INTO ##@TICKET VALUES(@DATA)ENDis what i have so far - although it just creates a table with a name of##@TICKET - which isn't what i want. I want it to evaluate the name.any ideas?

View 16 Replies View Related

SQL Server Admin 2014 :: Create Dynamic Columns In Temp Table?

Jun 9, 2014

I want to generate dynamic temp table so, from one strored procedure am getting an some feilds as shown below

CM_id,CM_Name,[Transaction_Month],[Transaction_Year],''[Invoice raised date],''[Payment Received date],''[Payout date],''[Payroll lock date]

for i want to generate table for the above feilds with datatype

View 5 Replies View Related

SQL Server 2008 :: Storing Dynamic Query Output In Temp Table

Apr 6, 2015

I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.

Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!

View 3 Replies View Related

T-SQL (SS2K8) :: Moving Values From Temp Table To Another Temp Table?

Apr 9, 2014

Below are my temp tables

--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL

[Code] ....

I want to loop through the data from #Base_Resource and do the follwing logic.

1. get the Resourcekey from #Base_Resource and insert into #Resource table

2. Get the SCOPE_IDENTITY(),value and insert into to

#Resource_Trans table's column(StringId,value)

I am able to do this using while loop. Is there any way to avoid the while loop to make this work?

View 2 Replies View Related

Insert Into Temp Results Of A Union Query

Nov 20, 2007

Hi,
I have follwing union query. I want to put this all in a temp table.

select Store_Id,batchnumber
From
Adjustments where updatedDt between '10/30/2007' and '11/20/2007' and Store_id in(8637 ,8641)
group by Store_Id, batchnumber
Union
select DestinationId,b.batchNumber
from
batch b
inner join Carton C on C.Carton_Id = b.General_ID
inner join Document d on d.Document_Id = c.Document_Id
where b.BatchType = 'Warehouse' and b.TranTable = 'Carton'
and (d.DestinationId in (8637 ,8641) ) and c.UpdatedDt Between '10/30/2007' and '11/20/2007'
Union
select d.DestinationId,b.Batchnumber
From
batch b
inner join Document d
on d.Document_Id = b.General_Id
where b.BatchType = 'TransferIn' and b.TranTable = 'Document'
and (d.DestinationId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007'
Union
select d.SourceId,b.batchNumber
From
batch b
inner join Document d
on d.Document_Id = b.General_Id
where b.BatchType = 'TransferOut' and b.TranTable = 'Document'
and (d.SourceId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007'
order by batchnumber

Kindly advice.

Thanks
Renu

View 2 Replies View Related

SSIS DYnamic Temp Tables

Nov 27, 2006

Hi,

I have a database with serveral tables, for example 'customer', I want to update this table with a SSIS package. However, to ensure we don't have issues if the update fails then I've put in an intermediate stage

Using an Execute SQL Task I create temporary tables, for example 'customer_tmp'. Data is then imported into these tables. When all the data is imported successfully the original tables are dropped and the temporary tables are renamed, removing the '_tmp'

This works fine and I'm happy with it. However, if someone adds a column to one of the tables in SQL server it is lost on the next upload.

Similarly I have to hard code creating the indexes into the package as well.

Does anyone know how I could copy the original table definitions and create the temporary tables dynamically. So that any new columns would be picked up?

And indeed is it possible to copy the indexes from one table to another before the drop and rename trick?

Thanks in advance.

Iain

View 4 Replies View Related

Dynamic Creation Of Temp Tables Using Managed Code

Jan 23, 2008



Hi,
I have a requirement to create #Temp table in database and insert values to it.

I use following code:

DbCommand dbCreateTable;

dbCreateTable = provider.CreateCommand();

dbCreateTable.Connection = conn;

dbCreateTable.CommandText ="Create table #MyTemp (Id varchar(10))";

dbCreateTable.ExecuteNonQuery();

string[] insertValues = {"Insert into #Mytemp values ('TestString1')",

"Insert into #Mytemp values ('TestString2')"};

DbCommand dbInsertData = provider.CreateCommand();

dbInsertData.Connection = conn;

foreach (String insertStr in insertValues)

{

dbInsertData.CommandText = insertStr;

dbInsertData.ExecuteNonQuery();

}

Code creates the Temp table but when it comes to insert statement, it throws error saying "Temp table not found".
Reason can be Create and Insert statement gets executed as 2 different sessions.
How to get the above requirement work fine?
Thank you.
HV

View 4 Replies View Related

Temp Table Vs Global Temp Table

Jun 24, 1999

I think this is a very simple question, however, I don't know the
answer. What is the difference between a regular Temp table
and a Global Temp table? I need to create a temp table within
an sp that all users will use. I want the table recreated each
time someone accesses the sp, though, because some of the
same info may need to be inserted and I don't want any PK errors.

thanks!!
Toni Eibner

View 2 Replies View Related

Generating A Datatable From Results Of A Dynamic Query?

Aug 25, 2004

I am developing a datagrid which will display a summary "report" of sales revenue grouped by a particular field. There will also be a drill-down on this field that will display in more detail the table of data that was used for the summary.

Because the user is selecting multiple criteria under which to run the search, I have created a dynamic query that gathers all the necessary data I need for both the drill down and the summary based on the variable parameters of the user. I've optimized the query that gathers the detailed data (taken from 7 tables) to execute far under a second.

So, that said, here's my question: It seems the most efficient way to get the summary data is to run it against the datatable (which gathers the detailed data) created by the dynamic query and just pass them both into a dataset. Can this be done? Originally, I was calling 2 separate sprocs in my application - one for the detailed data and another for the summary data using a "group by" on the table that was produced by the dynamic query (using "INTO TABLE" in the SELECT statement of the dynamic query). With this method, I received an access exception by asp.net. for the table created by the dynamic query so have dropped the "INTO TABLE", not to mention it increased the performance considerably NOT using the table.

Any suggestion on the most efficient/optimal method to use the data generated from the dynamic query sproc for the summary data? I am trying desparately to avoid running the dynamic query again just for the summary data.

I am a total newbie, so would appreciate any feedback. Also, would someone please tell me the datetime stamp function to print so that I can see how many milliseconds it takes to execute stored procedures?

TIA!

Sandy

View 1 Replies View Related

How To Check If Results Of Dynamic Query Exist

Dec 27, 2001

Hello,

I have a dynamic query in the stored procedure, and the code looks something like this:

SET @section_test = 'SELECT sectioncode FROM ' + @tablename + ' WHERE sectioncode = "' + @condition + '"'
EXEC (@section_test)

What I need to do is try to check if the query returns any values using EXISTS (possibly), but at the same time I don't want to return the results of that dynamic query's select statement in my stored procedure. Is it possible?

Thanks.

Regards,
Katya

View 1 Replies View Related

SQL Server 2012 :: How To Join Pivot Results With Dynamic Columns

Mar 5, 2015

I have a lookup table, as below. Each triggercode can have several service codes.

TriggerCodeServiceCode
BBRONZH BBRZFET
BBRONZH RDYNIP1
BBRONZP BBRZFET
BCSTICP ULDBND2
BCSTMCP RBNDLOC

I then have a table of accounts, and each account can have one to many service codes. This table also has the rate for each code.

AccountServiceCodeRate
11518801DSRDISC -2
11571901BBRZFET 5
11571901RBNDLOC 0
11571901CDHCTC 0
17412902CDHCTC1 0
14706401ULDBND2 2
14706401RBNDLOC 3

What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.

I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.

What I would like to get is just the service codes and the appropriate trigger code for each account.

SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode
FROM TriggerTable
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)')
,1,2,'')

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Stored Procedures Compiles Even When There Is No CREATE TABLE For A Temp Table

Feb 11, 2015

i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?

--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END

only on calling the proc does this give an execution error

View 3 Replies View Related

Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?

Jul 19, 2012

I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).

Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32

Simple example:
    declare @tTable(col1 int)
    insert into @tTable(col1) values (1)
    select * from @tTable

Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.

But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.

Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.

View 11 Replies View Related

INSERT INTO - Data Is Not Inserted - Using #temp Table To Populate Actual Table

Jul 20, 2005

Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO

View 2 Replies View Related

SQL Tools :: Adding Column To A Table Causes Copying Data Into Temp Table

Sep 23, 2015

If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.

Example of the script is below: in the source project I added columns [MyColumn_LINE_1]  and [MyColumn_LINE_5].

Is there any way I can make it generating an alter statement instead?

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
CREATE TABLE [dbo].[tmp_ms_xx_MyTable] (
[MyColumn_TYPE_CODE] CHAR (3) NOT NULL,

[Code] ....

The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.

View 7 Replies View Related

Transact SQL :: Update Table With Its Value And Data From Row In Temp Table For Matching Record?

Oct 25, 2015

I have a temp table like this

CREATE TABLE #Temp
 (
  ID int,
  Source varchar(50),
  Date datetime,
  CID varchar(50),
  Segments int,
  Air_Date datetime,

[code]....

Getting Error

Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.

View 4 Replies View Related

Column Name Or Number Of Supplied Values Does Not Match Table Definition When Trying To Populate Temp Table

Jun 6, 2005

Hello,

I am receiving the following error:

Column name or number of supplied values does not match table definition

I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:

SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp

INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'

The SP UserPersist_GetByCriteria does a
"SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture",
so why am I receiving this error when both tables have the same
structure?

The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.

Thanks,
Greg.

View 2 Replies View Related

Transact SQL :: Table Structure - Inserting Data From Other Temp Table

Aug 14, 2015

Below is my table structure. And I am inserting data from other temp table.

CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,

[Code] ....

Now in a next step I am deleting the records from #revf table. Please see the delete code below

DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2

[Code] ...

I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:

SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],

[Code] ....

If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.

View 5 Replies View Related

Update Temp Table With Stored Procedure Joined With Table

Sep 8, 2006

Hello

Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?

insert #MyTempTable

exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3

JOIN dbo.OtherTable...

I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.

I just can't figure it out

Many Thanks!

Worf

View 2 Replies View Related

Is A Temp Table Or A Table Variable Used In UDF's Returning A Table?

Sep 17, 2007

In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved