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

How To Append Multiple Queries To A Temp Table?

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

Linking Two Queries From Two Database Using Temp Table

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

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

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

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 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

Integration Services :: Get Data From Source By Executing Set Of Queries That Have Temp Tables

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

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 Queries

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

SUM Of Dynamic Queries

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

Dynamic Queries

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

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

Running Dynamic Queries

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

Best Way To Construct Dynamic Queries

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

Select Into Using Dynamic Queries

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

Help In Writing Queries For The Dynamic Values

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

Help With Stored Procedures / Dynamic Queries

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

Gererating Dynamic Queries During Run Time

Jun 26, 2007

How to Gererating Dynamic Queries During Run Time and execute the results



Thanks in advance

Suresh







View 1 Replies View Related

Problem With Dynamic Queries In SQL Tasks

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

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

Using Stored Procedures Are You Safe From Sql Injection If Your Not Using Dynamic Queries ?

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

Dynamic Queries With Data Reader Source Adapter

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

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







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