Hello, all. I'm attempting to insert data into a table variable using dynamic SQL created at runtime.
However, with a Table variable, SQL server will not allow the EXEC method to be used in an INSERT statement.
How do I go about this?
I am trying to insert different number of columns into variables. This is what it does If I use a static columns.
declare @AccountType nvarchar(10)
declare @Total numerical(15,2)
declare @1 numerical (15,2)
declare @2 numerical (15,2)
declare @3 numerical (15,2)
#MonthtoDate temp table is created using a dynamic pivot query.
Data looks like this :
Account Type 1 2
3 Total
Type 1 3
0 4 7
Type 2 5
7 1 13
Select @AccountType = AcctType , @Total = MonthToDate, @1 = [1], @2 = [2], @3 = [3] from #MonthtoDate
However the issue is with [1],[2],[3] columns. Those are the number of days of the month. If today is the 3rd day of the month, we only need to show 3 days. So the final table has column [1],[2],[3] and @AccountType and @Total .
We want to run this query everyday to get the moth to date values.If we run this tomorrow, it will have 4 date columns [1], [2],[3],[4] and @AccountType and @Total .
Hey,I have two tables of data, table1 and table2. In Table1, there is a"id" field, a "name" field, and an e-mail field. In table2, there is an"id" field, and paramters that belong to the certain "id". When I wantto create a new record, I want to make both of the entries at the sametime and have their "id"s match. I thought that the best way of doingthis was to create the data in table1, then get the "id" field of thatentry by way of matching the "name", and then use that id for the "id"field value for the entry to table2. The problem is this... I don'tknow how to collect the value of table1.id and store it in a variableto be sent with the rest of the data to table2. This is what I tried:-------------------------------@name varchar(8000),@email varchar(8000)INSERT INTO table1 (name, email)VALUES (@name, @email)SELECT idFROM table1WHERE table1.name = @name--------------------------------[color=blue]>From there, I don't know what to do...[/color]
View 4 Replies View RelatedHi:
Can anyone tell me if it's possible in SQL Server 2000 to build dynamically a select statement and get some values to variables (simple not tables) from the select ?
Thanks,
Rui Ferreira
Hello there,
i have been asked about a thing that, i think, is not possible. But maybe i am wrong.
question:
Is it possible to have a Stored Procedure in that the declaration of the variables is dynamic?
This means, can i get the variable name and the type of it from a database to create
a dynamic stored procedure that changes itself by firing a trigger.
Thanks for all oppinions and answers.
everWantedLINUX
I am taking my first steps into stored procedures and I am working on a solution for efficiently paging large resultsets with SQL Server 2000 based on the example on 4Guys: http://www.4guysfromrolla.com/webtech/042606-1.shtml
The problem with my stored procedure is, is that it doesn't seem to recognize a variable (@First_Id) in my dynamic Sql. With this particular sproc I get the error message: "Must declare the scalar variable '@First_Id'"It seems to be a problem with 'scope', though I still can't yet figure out. Can anyone give me some hints on how to correctly implement the @First_Id in my stored procedure? Thanks in advance!
Here's the sproc:
ALTER PROCEDURE dbo.spSearchNieuws(@SearchQuery NVARCHAR(100) = NULL,@CategorieId INT = NULL,@StartRowIndex INT, @MaximumRows INT,@Debug BIT = 0)ASSET NOCOUNT ONDECLARE @Sql_sri NVARCHAR(4000),@Sql_mr NVARCHAR(4000),@Paramlist NVARCHAR(4000),@First_Id INT, @StartRow INTSET ROWCOUNT @StartRowIndexSELECT @Sql_sri = 'SELECT @First_Id = dbo.tblNieuws.NieuwsId FROM dbo.tblNieuwsWHERE 1 = 1'IF @SearchQuery IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)' IF @CategorieId IS NOT NULLSELECT @Sql_sri = @Sql_sri + ' AND dbo.tblNieuws.CategorieId = @xCategorieId'SELECT @Sql_sri = @Sql_sri + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'SET ROWCOUNT @MaximumRows SELECT @Sql_mr = 'SELECT dbo.tblNieuws.NieuwsId, dbo.tblNieuws.NieuwsKop, dbo.tblNieuws.NieuwsLink, dbo.tblNieuws.NieuwsOmschrijving, dbo.tblNieuws.NieuwsDatum, dbo.tblNieuws.NieuwsTijd, dbo.tblNieuws.BronId, dbo.tblNieuws.CategorieId, dbo.tblBronnen.BronNaam, dbo.tblBronnen.BronLink, dbo.tblBronnen.BiBu, dbo.tblBronnen.Video, dbo.tblCategorieen.CategorieFROM dbo.tblNieuws INNER JOIN dbo.tblBronnen ON dbo.tblNieuws.BronId = dbo.tblBronnen.BronId INNER JOIN dbo.tblCategorieen ON dbo.tblNieuws.CategorieId = dbo.tblCategorieen.CategorieId AND dbo.tblBronnen.CategorieId = dbo.tblCategorieen.CategorieId WHERE dbo.tblNieuws.NieuwsId <= @First_Id AND 1 = 1' IF @SearchQuery IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)' IF @CategorieId IS NOT NULLSELECT @Sql_mr = @Sql_mr + ' AND dbo.tblNieuws.CategorieId = @xCategorieId' SELECT @Sql_mr = @Sql_mr + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'IF @Debug = 1PRINT @Sql_mr SELECT @Paramlist = '@xSearchQuery NVARCHAR(100), @xCategorieId INT'EXEC sp_executesql @Sql_sri, @Paramlist, @SearchQuery, @CategorieIdEXEC sp_executesql @Sql_mr, @Paramlist, @SearchQuery, @CategorieId
Hi!
Here is a snap form my code
declare @max_id int
declare @the_db varchar (30)
select @the_db = 'mydb'
exec ("select """ @max_id """ = max(id) from " + @the_db + "..mytable")
I got syntex error for the @max_id. The script is writen based on the sample given in MSSQL6.5 Book Online, chapter "Transaction-SQL Reference 6.0" section "C"->"Control-Flow Lang."->"Control-Flow Examples"
Can someone help me on how to assign a value to a local variable from a dynamic query.
Thank for any help in advance
Hank Lee
I have a requirement to create many SSIS packages and no datatransform is required so the BCP task looks a good contender providing it can do both import & export
is it possible to parse the values in bold as package variables into the BCP task. If so how?
BULK INSERT ipcs_wvg.dbo.extract
FROM 'D:IPCSextract.csv'
WITH (FORMATFILE = 'D:ipcsqueryextract.xml');
Thanks in advance
Dave
I have a global database called ETL Configuration for all my SSIS packages that uses a single table. So I can create three global variables
USE [ETLConfiguration]
CREATE TABLE [dbo].[SSIS Configurations](
[ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
Here is the data I would put in here
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'D:IPCSextract.csv'
PackagePath = 'Package.Variables[User::gsFileName].Properties[Value]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'D:ipcsqueryextract.xml'
PackagePath = 'Package.Variables[User::gsFormatFile].Properties[Value]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'ipcs_wvg.dbo.extract'
PackagePath = 'Package.Variables[User::gsTableName].Properties[Value]'
ConfiguredValueType = 'String'
--Database connection info
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = '.mssql2005'
PackagePath = 'Package.Connections[MyDatabaseName].Properties[ServerName]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'MyDatabaseName'
PackagePath = 'Package.Connections[MyDatabaseName].Properties[InitialCatalog]'
ConfiguredValueType = 'String'
I have looked at lots of options to automaticly create SSIS packages and have a hunch that that simple can be better: All these solutions look way to complex to what I want to achieve--
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=17484&SiteID=1
http://lakshmik.blogspot.com/2005/05/how-to-programmatically-create-ssis.html
Create DataFlow Package Sample
http://msdn2.microsoft.com/en-us/library/ms161541.aspx
SMOTableList Sample
http://msdn2.microsoft.com/en-us/library/ms161564.aspx
How do I use table names stored in variables in stored procedures?
Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000
I receive the error 'must declare table variable '@tablename''
I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.
Code Snippet
if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename
It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.
Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.
Heres the actual SQL query that produces the temporary table error.
Code Snippet
Select * into #temptableabcd from TableA
Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB
TRUNCATE TABLE TableA
Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB
The above coding produces
Msg 208, Level 16, State 0, Line 1
Invalid object name '#temptableabcd'.
Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?
Thanks for the help.
I am trying to update one table when records are inserted in another table.
I have added the following trigger to the table “ProdTr” and every time a record is added I want to update the field “Qty3” in the table “ActInf” with a value from the inserted record.
My problem appears to be that I am unable to fill the variables with values, and I cannot understand why it isn’t working, my code is:
ALTER trigger [dbo].[antall_liter] on [dbo].[ProdTr]
for insert
as
begin
declare @liter as decimal(28,6)
[Code] ....
Hello,I am writing a function that uses two table variables. The structures ofboth are shown here:DECLARE @workdates TABLE (conflict CHAR(1),workdate SMALLDATETIME)DECLARE @existing TABLE (workdate SMALLDATETIME)I need to do an update on the first table:UPDATE @workdatesSET conflict = 'X'FROM @existing sWHERE workdate = s.workdateI am concerned that the unqualified 'workdate' in the WHERE clause willgive me an ambiguous column reference. Is this SQL statement valid?Thanks,Andrew
View 3 Replies View RelatedHey everyone,I read in a SQL Server book that you can now create a tablevariable.DECLARE @TMP TABLE (list of fields)Then you can you can use the statementINSERT INTO @TMPSELECT (whatever).I've tried it and it works. The book also says that youshould be able to pass these variables between storedprocedures and functions. Problem is, when I try todeclare the variable at the top of the procedure, thesyntax checker hates it.Anyone else out there try this out?SAM
View 1 Replies View RelatedWill reporting services allow the use of table variables in the SQL query?
View 1 Replies View RelatedI want to querry a table based on the input of a form, I have tables Monday, Tuesday ect., and want to display the records for Monday if the user selects Monday in a menu. I need to delcare a table name but am not sure how?
DELCARE @myVar char(20)
SET myVar = Request.Form("select2");
"SELECT * FROM @myVar"
I know user defined global table variables are not allowed in sql. I'm trying to avoid using temporty tables for speed reasons. I have a function in which a table variable is defined, and a function within that function that needs to call that table variable. Any ideas?
Thanks
Is it possible to do something like this in SQL:
DECLARE @TABLE table
if @GOOD = 1 BEGIN
Set @Table = Table1
END ELSE BEGIN
Set @Table = OtherTable
END
SELECT * FROM @TABLE
So, I've got a problem with using table variable "fields" and a simple variable in calculations. It ain't workin'. See the bolded code below. When I run the SP, it returns 0 for those values. Anyone got any clues? Is this a table variable limitation? ALTER PROCEDURE YearlyTotalsInPercentages(@Year int) ASBEGINDECLARE @TotalSum intDECLARE @Totals TABLE
(
CBDCYearlyTotals int, ProductLine varchar(50))INSERT INTO @Totals (CBDCYearlyTotals, ProductLine)SELECT SUM(dbo.Main.Hours), dbo.Project.ProductLineFROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE 'CI%' AND dbo.Project.ControlLocation = 'IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @Year AND dbo.Main.Active = 1GROUP BY dbo.Project.ProductLine SET @TotalSum = (SELECT SUM(dbo.Main.Hours)FROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE 'CI%' AND dbo.Project.ControlLocation = 'IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @Year AND dbo.Main.Active = 1) SELECT t.CBDCYearlyTotals AS CBDCYearlyTotals, t.ProductLine AS ProductLine, @TotalSum AS TotalSum, ROUND((t.CBDCYearlyTotals/@TotalSum) * 100, 1) AS Percentage FROM @Totals tEND
GO
Thanks Yall
hi all!
I am using table variables instead of creating a temp table because it seems to be faster
But now I need qualify the table variable so I can join it with another table having a field with same name of a field from the table variable. U know if I can do that?
ex: with temp table
create table #tmp.... (F1...)
#tmp.f1
with table variable
declare @temp table(...
@table.f1 - can´t do it
the first question is if I can join the table variable with another table and how to do that qualifying the variable table, that is, putting the name of the var temp with the field, because the other table has a field with same name
thank to all and happiness for all 2004
I am trying to add a variable to a temporary table name. Throughout a stored procedure, I do a lot with this table. I delete, insert, update, and query this table. Is there a way to do the following without having to set the entire 'select * from ...' line as a variable? Below is what I am trying to accomplish. It all works until the select * line. Is there a way to accomplish what I am trying to do below?
Declare @table varchar(255),
@PassedID integer
set @passedID=5
set @table="test"+CONVERT(varchar(20), @passedID)
select * from @table
Thanks,
Jon
I have a cursor which populates a variable with the name of each user table within my DB.
I'm trying to copy the tables one at a time by using a command like this:
SELECT * INTO @NewTable FROM @OrigTable
Query analyzer tells me that there's incorrect syntax near the keyword 'INTO'.
This seems fair enough to me as I assume it's trying to put the data into the variable rather than the table name which is held within the variable. Does any know how I can get around this?
Are there any alternative ways of copying the table structure (preferable without the data)?
BTW both variables are of type char(100)
I have a table that has two columns POLICY_NUMBER and POLICY_TYPE
POLICY_NUMBER POLICY-TYPE
123 1
123 1
123 1
567 2
567 2
789 1
789 1
345 1
345 1
345 1
I need to write a script that give me the following result
policy_type_count policy_type
3 1
1 2
Can we use tables variables in triggers... if yes, any example...
View 4 Replies View RelatedCan an identity be created in a table variable?
Can joins be performed between table variables to be inserted into another table variable?
Am I better of using a temporary table?
(I'm working in a stored procedure here)
Thanks in advance
I have a problem with executing following T-SQL select query.My select statement looks like thisSELECT * FROM (@TableName_FirstPart + @TableName_secondPart)**@TableName_FirstPart & @TableName_SecondPart are Local variables**Im getting Incorrect syntax error for this statement.Following is the error message:"Server: Msg 170, Level 15, State 1, Line 55Line 55: Incorrect syntax near '+'."Is it possible to construct table names in the FROM clause dynamically ?Thanks in advance
View 2 Replies View RelatedIt's come up more than once for me, where I need to DECLARE and SET several SQL variables in a Stored Procedure where many of these values come from the same table record - what is the best method for doing this, where I don't have to resort to making a separate query for each value.
Currently I'll do something like this:
DECLARE @var1 intSET @var1 = (SELECT TOP 1 field1 FROM table1 WHERE recordkey = @somekey)DECLARE @var2 nvarchar(20)SET @var2 = (SELECT TOP 1 field2 FROM table1 WHERE recordkey = @somekey)
Of course, I'd rather just have to query "table1" just once to assign my variables.
What obvious bit of T-SQL am I missing?
Thank you in advance.
How do I know when to use a table variable, and when to use a temp table in my stored procedures? It seems that in most cases table variables are more efficient (in terms of execution time / CPU usage) but some of my stored procedures perform an order of magnitute better with temp tables instead.
Short of testing the stored proc both ways, how do I know what to do?
declare @Temp table
or
create table #Temp
Is it possible to use variable name to dynamically define a query in a stored procedure? EX:
@Column = 'COUNT(*)'
@Category = 'Products'
@Table = 'Items. + @Category
SELECT @Column
FROM @Table
This should be a relatively easy one... Are table variables, the data type, SQL (92 or 99) compliant?
TIA,
R2ro
I need to move data from one table to another using variable percentages and business days.
Here is the basic idea:
Variables: varTable1, varTable2, varPercentage
Get varPercentage of rows of VarTable1 that have a date of "current business date -1" and place into varTable2.
Hi, I am new to using SQL for anything more in depth than querying and reporting.
I am trying to create a series of SQL scripts which will be used across several customer sites so need to be easily customisable. What I want to do is have all of the table names, field names and customisable items handled by variables which will be declared and set at the beginning of the script, making them easy to find and change. The problem I am having at the moment is with creating a new table using variables for table name and field names, can anyone help?
quote:
DECLARE
@a_tmptbl varchar(15),
@a_fieldid1 varchar(15)
set @a_fieldid1 = 'newFieldid'
set @a_tmptbl = 'newTable'
create table @a_tmptbl ( @a_fieldid1 varchar(15), value float, counter INT);
insert into @a_tmptbl values ( "foobar", 21.76, 1);
select * from @a_tmptbl;
The error I am getting is:
quote:Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 10
Must declare the variable '@a_tmptbl'.
Server: Msg 137, Level 15, State 1, Line 12
Must declare the variable '@a_tmptbl'.
Any advice would be gratefully accepted
What would be the correct syntax, if any, that allows updating a tablevariable in a statement that uses the same table variable in a correlatedsubquery? Here's an example:DECLARE @t table (N1 int NOT NULL, N2 int NOT NULL)UPDATE @t SETN1 = (SELECT COUNT(1)FROM @t AS tWHERE t.N2 < @t.N2)This doesn't compile, complaining about "variable @t" in the WHERE clause.I'm not so interested in a way to rewrite this particular statement to makeit work, but rather in a general way to refer to table variables in thecontexts where correlation names cannot be used.Thank you.--remove a 9 to reply by email
View 7 Replies View RelatedI am running SQL Server Best Practices on a SQL 2000database and it is recommending me to change the temptables inside SPs to table variables.I had read already in other places to use table variablesover temp tables. I also know I can't create indexes asI can on temp tables. Instead I'll have to create eithera primary key and/or a unique index on a table variable.One question I have is let's say I will be putting thousandsof records in a temp table, should i still choose a tablevariable over a temp table for this? Or is there arecommended limit where if I have to store certainnumber of records then it's better to store them ina temp table rather than a table variable? Or numberof records is not the factor to decide whether to usetemp tables or table variables?I would like to know when it's ideal or best to usetemp tables instead of table variables and vice versa.Thank you
View 1 Replies View RelatedHi,I have a user-defined function which returns a table (call it '@a'),and has another table defined as a variable (call it '@b'). When I tryto do the following query, I get "Must declare the variable '@b'" and"Must declare the variable '@a'." How do I remedy this?The query:UPDATE @aSETstuff =(SELECT otherStuff From @bWHERE @b.someID = @a.someID)
View 2 Replies View Related