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
ADVERTISEMENT
Apr 4, 2006
Hello. I'm having some difficulty trying to output the results of two seperate queries into the same temporary table.
Does anyone know if it is possible to use the UNION operator to output the results into a temporary table?
Select * From TableA
UNION
Select * From TableB
<---output result to temporary table here--->
Alternatively, is it possible to output the results of two queries in to the same temporary table without the UNION clause?
The following statement fails on the second SELECT INTO due to the fact that #MyTempTable already exists.
Select * INTO #MyTempTable FROM TableA
Select * INTO #MyTempTable FROM TableB
Thanks in advance.
View 2 Replies
View Related
May 20, 2014
I am using SQL 2008 r2. I have two SQL Queries from 2 different database but they share one server. I need to linked these two SQL Queries as they share the same Primary key which CustomerID see example below
Query 1
Database::Student
Select StudentID , FName, LName
From Student
Query 2
Database ::Finance
Select StudentID,Tution
FROM Payment
I need to be able combine two query which come from two database but they share one server.I would like to use two temp tables so that I can perform a left / right join to retrieve the data by linking two queries using primary id which they both share ( StudentID)
Summary : I have two DB's on the same server. I have two simple select queries for each DB which work correctly.
View 3 Replies
View Related
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
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
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
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
View Related
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
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
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
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
Jul 29, 2015
I need to grab data from teradata(using odbc connection).. i have no issues if its just bunch of joins and wheres conditions.. but now i have a challenge. simple scenario, i have to create volatile table, dump data into this and then grab data from this volatile table. (Don't want to modify the query in such a way i don't have to use this volatile table.. its a pretty big query and i have no choice but create bunch of volatile tables, above scenarios is just mentioned on simple 1 volatile table ).
So i created a proc and trying to pass this string into teradata, not sure if it works.. what options i have.. (I dont have a leisure to create proc in terdata and get it executed when ever i want and then grab data from the table. )
View 2 Replies
View Related
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
Dec 8, 2006
Hi,
This question has been asked probably million times, but it sems that I cannod find right answer on search engines.
I need to send parameters to my stored procedure, but not only parameters. For example in where clause I have something like:
where myID = 12
I need to be able to filter results on myID, but one time I need it to be eqal to 12 and other time I need it to be different (<>) from 12. Some time I even need to add another condition like myID2 = 1. Can I solve this without additional procedures?
I believe that I saw solution in 3-Tier ArchitectureTutorial Series few weeks ago but it seems that something changed, and I cannot find it anymore. Can anyone help?
View 3 Replies
View Related
Mar 29, 2005
I am trying to get the sum of two dynamic queriesThat is:EXEC('SELECT COUNT(id) as subtotal1...) + EXEC('SELECT COUNT(id) as subtotal2...)If I assign to a variable, say @total, I get errors or NULLs. Is there a workaround?Thanks in advance for your comments
View 1 Replies
View Related
Mar 19, 2007
Hi,
I have a parameter in the url to the report. According to that parameter, I need to change the query. For example, url = http://localhost/reportserver?param1=A
if Param1=A, then I need to use query1 for the report
if param1 = B, then I need to use query2 for the report.
I would like to know if I can do this. If not, is there any other way to build query dynamically in the report.
thanks
View 1 Replies
View Related
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
Jan 1, 2006
I'm creating a search function right now and I'm running into a problem where my search function doesn't know what fields the user will search for, and therefore doesn't know whether or not to put quote marks around specific values or not.
For example, somebody might search for somebody else with year of birth in which case I might have a query:
SELECT userid FROM users WHERE yob = 1970
but somebody else might search for a name, in which case I need
SELECT userid FROM users WHERE first_name = 'Andrew'
or somebody else might search for both and need
SELECT userid FROM users WHERE yob = 1970 AND first_name = 'Andrew'
I'm accomplishing this by having the function (this is in PHP) take an array as an argument, where the key is the MySQL column and the value is the required value for that column. So in the 3rd example, it would be a 2-item array:
yob => 1970
first_name => 'Andrew'
And then cycling through that and dynamically creating a MySQL query based on the received array.
So... my problem is finding a way to specify when quote marks are required and when they're not. Is there any way to just have MySQL default to the format of the column? Also, if anybody thinks this isn't the right way to create a search function, let me know because I'm new at this .
Thanks!
PS: Right now what I'm doing is I'm creating arrays that include names of columns that do and don't need quote marks. Then in construcing the MySQL statement I'm checking to see which array a column is in, and making the quote decision based on that.
View 6 Replies
View Related
Apr 17, 2008
I am working with a form that I wish to construct a dynamic query from the results of. The forum has a date range and two radio buttons. Each radio button enables a list of items that can be clicked. So for example, if we assume the question,
"What is your favorite food, and what toppings do you like on it?" where the radio buttons are foods, the list boxes are toppings. Assuming the user can choose a Hamburger or a Salad with generic toppings, their choices are as such:
They can choose a Hamburger, with every topping
They can choose a Hamburger with a single topping.
They can choose a Hamburger with multiple toppings.
They can choose a Salad with the same combinations as above.
They cannot choose both a Hamburger and a Salad - mutually exclusive items.
Then, I wish to construct a query that, based on the conditions above, retrieves information relavent to their criteria, such a the number of food items to choose from, their price, etc. - basic information. What is the most efficient way to do this? Should I write a stored procedure with numerous conditionals and all available parameters, constructing the sproc as such:
Code Snippet
CREATE PROCEDURE GetFoodInfo
@from datetime,
@to datetime,
@FoodType varchar(20),
@toppings varchar(20)
AS
BEGIN
DECLARE @query varchar(300)
SET @query = 'SELECT COUNT(DISTINCT ' + @FoodType + ') '
IF @FoodType = 'Hamburger'
SET @query = @query + 'FROM Hamburgers '
ELSE
SET @query = @query + 'FROM Salads '
IF @toppings <> 'ALL'
SET @query = @query ' WHERE Toppings = ' + @toppings
EXEC (@query)
Apologies of this syntax is incorrect, but you get the general idea. Of course, this is a small example - in reality, I would have 5-10 conditional requirements Or, should I generate a stored procedure (or simple query) for each operation? For example, assuming each is a stored procedure:
GetHamburgers <-- would get Hamburgers with all toppings
GetHamburgersWithToppings
GetSalads
GetSaladsWithToppings
What is the best method for what I wish to achieve? What is fastest? Is there a better way than I have listed? Thank you.
Again, this is a small example, but I hope someone can help.
View 3 Replies
View Related
May 5, 2008
I need to create a temporary table using dynamic queries and then i have to use the temporary table for data manipulatuion.
Can someone help me out on this.
EG
sp_executesql N'Select top 1 * into #tmp from table1'
select * from #tmp
View 5 Replies
View Related
Mar 18, 2008
Hello, I really have a problem writing queries for the dynamic values. i follow the below mentioned method to write the queries but its really confusing. ex: str = "SELECT SO.Description,SO.DiscountPct,SO.Type,SO.Category,SO.StartDate,SO.EndDate,SO.MinQty,SO.MaxQty," + "S.Name AS ProductSubCategory,P.Name AS ProductName, C.Name AS ProductCategory FROM Production.Product P " + "INNER JOIN Production.ProductSubcategory S ON P.ProductSubcategoryID = S.ProductSubcategoryID " + "INNER JOIN Production.ProductCategory C ON S.ProductCategoryID = C.ProductCategoryID " + "INNER JOIN Sales.SpecialOfferProduct SOP ON P.ProductID = SOP.ProductID " + "INNER JOIN Sales.SpecialOffer SO ON SOP.SpecialOfferID = SO.SpecialOfferID " + "WHERE '" + txtStartDate.Text + "' between SO.StartDate AND SO.EndDate AND '" + txtEndDate.Text + "' BETWEEN SO.StartDate AND SO.EndDate " + "AND SO.Description Like '" + txtSpecialDesc.Text + "%'"; can anybody help me in writing the queries for dynamic values in an easy way. Thank you Sandeep Chavva
View 3 Replies
View Related
Jun 12, 2006
Hello, I'm trying to create a Stored Procedure who receives the table name as a parameter, then uses a cursor to obtain every column name and then builds a string like SELECT col1, col2, ... from TABLE
In fact that would be the same as SELECT * FROM table; but I can't do this, because I'll be using this stored procedure to loop through many tables that has different quantity of columns with a DTS, and if a specify the *, then the DTS wouldn't let me do the select with tables with different quantity of fields.
Could you help me please, because my code isn't working:
CREATE PROCEDURE dbo.stp_Test
(
@tablename AS VARCHAR(50)
)
AS
DECLARE @columnname varchar(50)
DECLARE @strsql Nvarchar(500)
DECLARE @query varchar(4000)
SET NOCOUNT ON
DECLARE c1 CURSOR FOR
SELECT column_name FROM information_schema.columns
WHERE table_name = @tablename
OPEN c1
FETCH NEXT FROM c1 INTO @columnname
WHILE @@fetch_status = 0
BEGIN
IF (@strsql is null)
BEGIN
SET @strsql=@columnname
END
ELSE
BEGIN
SET @strsql = @strsql + ',' + @columnname
END
FETCH NEXT FROM c1 INTO @columnname
END
CLOSE c1
DEALLOCATE c1
SELECT @query = 'SELECT ' + @strsql + ' FROM ' + @tablename
EXEC @query
SET NOCOUNT OFF
GO
View 4 Replies
View Related
Jun 26, 2007
How to Gererating Dynamic Queries During Run Time and execute the results
Thanks in advance
Suresh
View 1 Replies
View Related
Mar 19, 2008
Hello, i have dynamic queries for example:
DECLARE @query nvarchar(250);
DECLARE @table nvarchar(100);
SET @table = 'table_name';
SET @query=
N'update '+@table+'
set column = 0
where column is null';
EXECUTE sp_executesql @query
SET @query=
N'update '+@table+'
set column2 = 0
where column2 is null';
EXECUTE sp_executesql @query
It is very simple example. My question is: it is possible somehow put these queries in SQL Tasks?
Because if i do like this i get error "The Declare cursor SQL construct or statement is not supported."
Maybe there is other way to solve my problem? Or maybe syntax should be changed?
Thanks for advice
View 1 Replies
View Related
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
Mar 12, 2008
Im reviewing my stored procedures for a new application and got to thinking about protecting against sql injection. I think im pretty safe since im using stored procedures and none of them use any 'exec' commands within them, but im not sure.
I was reading this article, and again all the examples that list a stored procedure, have an 'exec' command somewhere that is the culprit. So, in my case lets say I was doing something like this:
Im generally using regularexpression validation controls on the client side of the application and limiting the max length of the input there as well.
Am I safe, or do I need further input checking within the procedure ?
Code Snippet
CREATE PROCEDURE [dbo].[get_Uploads]
@app varchar(50)
--Init variables
SET @error_number = 0
BEGIN TRY
SELECT [Logid],[Filename],[Label],[UploadDate],[App]
FROM UploadLog au
WHERE [App]=@app
END TRY
BEGIN CATCH
SET @error_number = -2
END CATCH
View 1 Replies
View Related
Apr 16, 2008
I am a business user trying to build an incremental ETL package with SSIS. I have a working prototype on SQL Server 2005 where I select the max(ID) from the last successful run and pass that value into a variable. Then, in my Data Flow step, I select an OLE DB source adapter and use this variable in a custom select statement.
Here's my challenge....the live data is actually in a Postgres DB so I have to use a Data Reader Source adapter. When I try to pass my variable to this adapter the job bombs out. Does anyone know how to dynamically update the query text inside a Data Reader source adapter using variables or otherwise?
View 3 Replies
View Related
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
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
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
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
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
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