Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

How Do I Insert Variables?

into database.
Is there any other way except using parameters? and if there isn't, how can i insert 2 parameters in the same query?

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Insert Trigger Using Variables Help
Can somebody please help me with compiling my insert trigger below. I am fairly new to SQL server 2000 and I am having troubles with using variables in insert triggers. The trigger that I am creating will basically update another table based on a certain criteria that is not specified below. I am hoping to first get my trigger to work then apply the criteria on when to fire afterwards. I just need help with being able to store values in my declared variables for insert into another table. Thanks in advance for everyones help.

Use database_testing

IF EXISTS (SELECT name FROM sysobjects
WHERE type = 'TR' AND name = 'Trigger_Name')

ON [trigger_table] FOR INSERT
Declare @resource_id int = inserted.resource
@type = varchar(100) = inserted.type
@date_logged (datetime) = inserted.creation_date
@created varchar(100) = inserted.username

Insert into table_A (resource_id, resource_type, date_created, created_by)
values (resource_id,type, date_logged, created)

View Replies !   View Related
BULK INSERT Using Variables
I'd like to "pass" the BULK INSERT statement a variable for the table_name and file_name arguments. I've tried the following:

BULK INSERT @strTableName
FROM @strDataFileName

where the @strTableName and @strDataFileName variables are passed in via stored procedure arguments. I get the error:

Incorrect syntax near '@str_TableName'.

Is there a way to do this?

View Replies !   View Related
Insert Into Multiple Variables From One SQL Call
I was wondered what the most efficient way to do the following in MS SQL (2000, 2005, and above):
 Psudo Code:
DECLARE @var1, @var2, @var3 VARCHAR(100)
SET @var1, @var2, @var3 = (SELECT var1, var2, var3 FROM Table WHERE [ID] = @someID) <-- Returns only one row
Right now I'm making a call for every variable. I should play around with temp tables but seems like allot of overhead code just to get 3 values. I'm thinking there's a simple way to do this.
 Thanks for any ideas

View Replies !   View Related
Using Declared Variables In SQL INSERT Statement.
I am new to scripting in general and I've run into an issue when attempting to write a VB variable to a database table in SQL Express.  I am trying to record the value of the variable to the db, but it does not appear that the value is being passed to SQL.  If I hard code the values in the SQL statement it works fine.  Can someone explain what I'm doing wrong accomplish this?  My code is below.  Thanks in advance. 
<asp:SqlDataSource ID="SqlDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>"
SelectCommand="SELECT * FROM [Table]"
InsertCommand="INSERT INTO [Table] (field1, field2) VALUES (& variable1 &, & variable2 &);" >
Protected Sub Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button.Click
Dim variable1 As String = FileUpload.FileName
Dim variable2 As String = Date.Now
Dim path As String = Server.MapPath("~/directory/)
If FileUpload.HasFile = True Then
FileUpload.PostedFile.SaveAs(path & _
End Try
End If
End Sub

View Replies !   View Related
DTS For Bulk Insert With Global Variables...
I have a flat file of records in csv format. I want ti create a dts that can be executed from using to load data from .csv file into sql server 2000 table.

Now what further i want that this DTS must have a parameter mapped to file name of .csv. so when i execute this dts from web application i will set the file path to this variable and then execute it on server.

So that meanz dts stays on server all it require before execution a new file path to load data from file that is already uploaded on server in a directory.

if any one have any idea to how to create such a dts and secondly how to execute in

thanks in advance.

View Replies !   View Related
INSERT INTO ..... SELECT Using Local Variables.
I am Mukund Joshi ( from India. If anybody has used INSERT INTO....SELECT statement where the Table Name is stored in a local variable as well as in the Select statement you are using a local varibale 's value to compare with a Column's value then please Forward me the right syntax with examples, if any.

Thanks & Regards

View Replies !   View Related
Pass Variables Across Forms, Then Insert Into Database
Hey Guys:

--- Not sure if this should be moved to webforms forum, or if it belongs here ---

Alright, I have been dealing with this issue for a few days now, and have found a few solutions but they all seem to throw different errors so I figured I'd ask here.

What i am trying to do is have a webform where user enter data, and have the data passed across forms, then displayed and inserted into a database on another form. THe first for has an asp:rangevalidator control dymamicly built so I cannot simply take of the tags and use the old style.

Eventually the user will be directed to a paypal form, and upon successful completion be redirected to the page with the insert command within it, but for now, passing it to a second page for review, then inserting it will work.

I am not sure how to accomplish this, a tutorial or a code example would be great!! I have though about panels, creating public objects, etc, but all the solutions I have found have one issue or another when I attempt to create them.

I'm using 1.1, and SQL server.

Brian Sierakowski

View Replies !   View Related
Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)

     I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on  " Execute DTS 2000 Package Task Editor - Inner Variables ".  I believe the SSIS variables  must be mapped on  " Execute DTS 2000 Package Task Editor  - OuterVariables ".   How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"?  How can I do it?  Much Thanks.





View Replies !   View Related
How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?

I would like to design a SSIS package, which have couple of variables.  It loads a xls file specified in a variable [varExcelFileFullPath] .

I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).

It seems it does not get the values passed in for those variables. I deployed the package to a sql server.

are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.

exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****


/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"

/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"

/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"






View Replies !   View Related
Insert :) I Have Different Insert Code Lines (2 Insert Codelines) Which One Best ?
hello friends
my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn)
'you should use sproc instead
cmd.Parameters.AddWithValue("@UserId", textbox1.text)
'your value
conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery()
conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString()))
Catch sex As SqlExceptionThrow sex
If conn.State <> Data.ConnectionState.Closed Then
End If
End Try
MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource()
SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString
SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text
SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)"
SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0
rowsaffected = SglDataSource2.Insert()Catch ex As Exception
SglDataSource2 = Nothing
End Try
If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx")
End If

View Replies !   View Related
Strange Problem: SQL Insert Statement Does Not Insert All The Fields Into Table From C# Webpage
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62

View Replies !   View Related
Can I Insert/Update Large Text Field To Database Without Bulk Insert?
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind.  I've tried using the new .write() method in my update statement, but it cuts off the text after a while.  Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

View Replies !   View Related
How To Write Query To Insert 10,000 Rows In A Table Using Insert Statement One Time
create table and inserting 10,000 row values at a time using single insert statment

ex: I want to create table employee having two coloumns
like employeeid , name

note : name can be static i.e : same name for 10,000 rows

employeeid is unique

can any one write a query for me?

View Replies !   View Related
OPENROWSET (INSERT) Insert Error: Column Name Or Number Of Supplied Values Does Not Match Table Definition.
Is there a way to avoid entering column names in the excel template for me to create an excel file froma  dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
 Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'  
IF @File_Name = ''      Select @fn = 'C:Test1.xls'     ELSE      Select @fn = 'C:' + @File_Name + '.xls'        -- FileCopy command string formation     SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn     
-- FielCopy command execution through Shell Command     EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT        -- Mentioning the OLEDB Rpovider and excel destination filename     set @provider = 'Microsoft.Jet.OLEDB.4.0'     set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn   
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet1$]'')      '+ @sql1 + '')         exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *     FROM [Sheet2$]'')      '+ @sql2 + ' ')   

View Replies !   View Related
Cannot INSERT Data To 3 Tables Linked With Relationship (INSERT Statement Conflicted With The FOREIGN KEY Constraint Error)
 I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :

 and here goes the  code1 DataSet ds = new DataSet();
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();

and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"

Could You please tell me what am I missing here ?
Thanks a lot.

View Replies !   View Related
Insert Command Fails When I Want To Insert Records In Data Table
On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer  form that logged in users have access to.
it has 2 questions including a text box for Q1 and  dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
 Be very helpful if somebody could check the code and advise where the problem is??
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<asp:DataList ID="SideBarList" runat="server">
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
<SelectedItemStyle Font-Bold="True" />
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<asp:WizardStep runat="server">
<table class="style1">
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
<td class="style5">
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
  Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
 Event Handler
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
 SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());

View Replies !   View Related
Can't Insert New Data To Sql Using Sqldatasource.insert, Web Forms And A Master Page
Hello, I'm new to the forum and new to SQL, ASP.NET, etc.  I am creating an intranet site for my company in VS 2005 and have run into a very annoying problem that I can't seem to solve.  I have tried Googling it and came up empty.  I have a database in SQL Express 2005 and my website will be accessing several tables within the database.  I can retrieve info just fine and I can update, delete, etc just fine using gridview or other prebuilt tools, but when I add a few text boxes and wire a button to the SqlDataSource.Insert() command, I get a new record that is full of null values except for the identity key I have set.  The kicker is that I am also using a master page and when I duplicate the web page without the master page link, everything works just fine.  The following snippets show what I'm doing:<InsertParameters><asp:FormParameter Name="Name" Type="String" FormField="txtName" /><asp:FormParameter Name="Location" Type="String" FormField="ddlLocation" /><asp:FormParameter Name="Issue" Type="String" FormField="txtProblem" /></InsertParameters>Of course I match the formfields to the text boxes, create an onclick event for my button, the sqldatasource is configured correctly, it just doesn't work with the master page no matter what I do. Any help would be appreciated. Thanks

View Replies !   View Related
How To Insert Data From A File Into Table Having Two Columns-BULK INSERT

i have a file which consists data as below,
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.

but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?

View Replies !   View Related
Insert Rows With SQLServerce V3.5 Is Very Slow Can Anyone Help Insert Performance Poor

Hi All
I decided to change over from Microsoft Access Database file to the New SQLServerCe Compact edition. Although the reading of data from the database is greatly improved, the inserting of the new rows is extremely slow.
I was getting between 60 to 70 rows per sec using OLEDB and an Access Database but now only getting 14 to 27 rows per sec using SQLServerCe.
I have tried the below code changes and nothing seams to increase the speed, any help as I would prefer to use SQLServerCe as the database is much smaller and I€™m use to SQL Commands.
VB2008 Pro
.NET Frameworks 2.0
SQL Compact Edition V3.5
Encryption = Engine Default
Database Size = 128Mb (But needs to be changes to 999Mbs)
Where Backup_On_Next_Run, OverWriteQuick, CompressAns are Booleans, all other column sizes are  nvarchar and size 10 to 30 expect for Full Folder Address size  260
Direct Insert Using Data Adapter.
Me.BackupDatabaseTableAdapter1.Insert(Group_Name1, Full_Folder_Address1, File1, File_Size_KB1, Schedule_To_Run1, Backup_Time1, Last_Run1, Result1, Last_Modfied1, Last_Modfied1, Backup_On_Next_Run1, Total_Backup_Times1, Server_File_Number1, Server_Number1, File_Break_Down1, No_Of_Servers1, Full_File_Address1, OverWriteQuick, CompressAns)
14 to 20 rows per second (Was 60 to 70 when using OLEDB Access)
Using Record Sets
Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)
        Dim conn As SqlCeConnection = Nothing
        Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"
            conn = New SqlCeConnection(strConn)
            Dim cmd As SqlCeCommand = conn.CreateCommand()
            cmd.CommandText = "SELECT * FROM BackupDatabase"
            Dim rs As SqlCeResultSet = cmd.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
            Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
            rec.SetString(1, Group_Name1)
            rec.SetString(2, Full_Folder_Address1)
            rec.SetString(3, File1)
            rec.SetSqlString(4, File_Size_KB1)
            rec.SetSqlString(5, Schedule_To_Run1)
            rec.SetSqlString(6, Backup_Time1)
            rec.SetSqlString(7, Last_Run1)
            rec.SetSqlString(8, Result1)
            rec.SetSqlString(9, Last_Modfied1)
            rec.SetSqlString(10, Latest_Modfied1)
            rec.SetSqlBoolean(11, Backup_On_Next_Run1)
            rec.SetSqlString(12, Total_Backup_Times1)
            rec.SetSqlString(13, Server_File_Number1)
            rec.SetSqlString(14, Server_Number1)
            rec.SetSqlString(15, File_Break_Down1)
            rec.SetSqlString(16, No_Of_Servers1)
            rec.SetSqlString(17, Full_File_Address1)
            rec.SetSqlBoolean(18, OverWriteQuick)
            rec.SetSqlBoolean(19, CompressAns)
        Catch e As Exception
        End Try
    End Sub
€™20 to 24 rows per sec
Using SQL Commands Direct
Private Sub InsertRecordsIntoSQLServerce(ByVal Group_Name1 As String, ByVal Full_Folder_Address1 As String, ByVal File1 As String, ByVal File_Size_KB1 As String, ByVal Schedule_To_Run1 As String, ByVal Backup_Time1 As String, ByVal Last_Run1 As String, ByVal Result1 As String, ByVal Last_Modfied1 As String, ByVal Latest_Modfied1 As String, ByVal Backup_On_Next_Run1 As Boolean, ByVal Total_Backup_Times1 As String, ByVal Server_File_Number1 As String, ByVal Server_Number1 As String, ByVal File_Break_Down1 As String, ByVal No_Of_Servers1 As String, ByVal Full_File_Address1 As String, ByVal OverWriteQuick As Boolean, ByVal CompressAns As Boolean)
        Dim conn As SqlCeConnection = Nothing
        Dim CommandText1 As String = "INSERT INTO BackupDatabase (Group_Name, Full_Full_Folder_Adress, File1,File_Size_KB, Schedule_To_Run, Backup_Time, Last_Run, Result, Last_Modfied, Latest_Modfied, Backup_On_Next_Run, Total_Backup_Times, Server_File_Number, Server_Number, File_Break_Down, No_Of_Servers, Full_File_Address, OverWrite, Compressed) VALUES ('" & Group_Name1 & "', '" & Full_Folder_Address1 & "', '" & File1 & "', '" & File_Size_KB1 & "', '" & Schedule_To_Run1 & "', '" & Backup_Time1 & "', '" & Last_Run1 & "', '" & Result1 & "', '" & Last_Modfied1 & "', '" & Latest_Modfied1 & "', '" & CStr(Backup_On_Next_Run1) & "', '" & Total_Backup_Times1 & "', '" & Server_File_Number1 & "', '" & Server_Number1 & "', '" & File_Break_Down1 & "', '" & No_Of_Servers1 & "', '" & Full_File_Address1 & "', '" & CStr(OverWriteQuick) & "', '" & CStr(CompressAns) & "')"
            conn = New SqlCeConnection(strConn)
            Dim cmd As SqlCeCommand = conn.CreateCommand()
            cmd.CommandText = CommandText1
            'cmd.CommandText = "INSERT INTO BackupDatabase (€¦"
        Catch e As Exception
        End Try
    End Sub
€˜ 25 to 30 but mostly holds around 27 rows per sec I
Is this the best you can get or is there a better way. Any help would be greatly appericated
Kind Regards
John Galvin

View Replies !   View Related
Wants To Insert Into Multiple Table By A Single Insert Query
I wants to insert data into multiple table within a single insert query

View Replies !   View Related
Multiple Insert Call For A Table Having Insert Trigger

I am trying to use multiple insert for a table T1 to add multiple rows.

Ti has trigger for insert to add or update multiple rows in Table T2.

When I provide multiple insert SQL then only first insert works while rest insert statements does not work

Anybody have any idea about why only one insert works for T1


View Replies !   View Related
Openquery Insert With Additional Columns For Manual Insert.

I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.
The query below works well because the table's columns are even in both databases:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')

I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.
My insert statement above no longer works because the column numbers don't match.
In a nutshell I would like a way to combine the following queries:
Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')
Insert into Webcases (insurancetype) Values ('SF') 
--insurancetype is the new column.

View Replies !   View Related
Insert To Recordset Gives Different Date Format To Table Insert ????????
Help please!


I have an asp page with some simple vbscript to add a record to a table, the record has a datefield (dob).

the insert results in a US formated date if I add a record to a dynamic recordset but a UK formated date if I insert direct to the table ?????


if request("dob") is "01/11/2007"       (1st november 2007)



set conn = server.createobject("adodb.connection")

set rs = server.createobject("adodb.recordset") "tez", mc, 2, 2 rs.addnew

rs("dob") = request("dob")



11 jan 2007 stored in table




set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc


results in

1 november 2007 is written to the table.

Both of these methods are used in the same asp page.


This is on a windows2003 server, sql2005,iisv6, asp.netv2


I have tried every setting I can find in iis,asp,sql server to no avail.

I need the recordset method to work correctly.





View Replies !   View Related
Insert Using Subquery Fails To Insert With No Error
I am working with parent child tables and want to populate the primary key on insert so that the user does not have to enter this for each record.  Here is my codeInsertCommand="INSERT INTO [Awards] ([UFID], [DateAwarded], [Amount], [AwardingAgency]) Select UFID, @DateAwarded, @Amount, @AwardingAgency from master where GatorlinkName = @LoginName"  <InsertParameters><asp:Parameter Name="LoginName" Type="String" />         <asp:Parameter Name="strusername" Type="String" />            <asp:Parameter Name="UFID" Type="String" />            <asp:Parameter Name="DateAwarded" Type="DateTime" />            <asp:Parameter Name="Amount" Type="Decimal" />            <asp:Parameter Name="AwardingAgency" Type="String" />        </InsertParameters> The UFID field is the only field that should be populated from SQL data the others are coming from a form view insert form.  When I run an insert I get no error but the insert does not happen. I know that the @LoginName works since I am using this same logic in my select statement.  Thanks in advance for your help,Ken 

View Replies !   View Related
Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.

View Replies !   View Related
Force An INSERT INTO To Insert Rows Orderly
Hi All,

From what I know, when you execute an
INSERT INTO table1 (field0)
SELECT field0 FROM table 2 ORDER BY field0
the rows are inserted in whatever order the server engine thinks is better at that moment. Is it any way I can have field0 in table1 inserted in the order I need?

My problem is that I can not touch table1, it is used by a legacy application which I am not allowed to modify. I was thinking about creating a clustered index, adding an id field, etc, but I can not know how this will affect the front end application.

table1 and table2 have only a few hundred records.

table1 =
CREATE TABLE [FinalPlasma] ([name] [varchar] (2000) NULL )

table2 =
CREATE TABLE [Test_FinalPlasma] ([nameID] [int] NOT NULL ,
[name] [varchar] (2000) NULL
The update that is not giving the "good" order =

INSERT INTO FinalPlasma SELECT name from dbo.Test_FinalPlasma

Unfortunately I can not order the [name] field after the update, because it looks something like

Donald McQ. Shaver
Mark Sheilds
R.J. Shirley
W.E. Sills
Kenneth A. Smee
A. Britton Smith
LCol Edward W. Smith
Harry V. Smith
M. E. Southern
Timothy A. Sparling
Spectrum Investment Management Limited

Thank you,

View Replies !   View Related
@@ Variables
What is @@variables means in sql server?

View Replies !   View Related
SQL HELP......IF's With Variables

View Replies !   View Related
Sql And Variables
hi, how do i set a variable equal to a field from a sql table? i can assign it to a drop down, but it doesnt seem to work for a normal variable points.datasource=cmd2.ExecuteReader(
points.DataValueField = "PT_ID"

any help much appreciated

View Replies !   View Related
DTS Variables..?
Is there a way to pass variables off to DTS by ADO.NET?
Such as a FileName to export to and/or a parameter for the export query?

View Replies !   View Related
Using LIKE With Variables
Is there a way to use the LIKE keyword with variables like below?

SET @Name = 'MyName'

WHERE my_name LIKE @Name

This won't work, but you get the idea of what I want. Any thoughts?

View Replies !   View Related
Using Variables In T-SQL
I want to change database in my SQL-script like this:
DECLARE @DB_NAME varchar(30)
SELECT * FROM @DB_NAME.dbo.TableName,
but it is syntactically incorrect.
I could use
but I write Stored Procedure, it is not allowed using
USE statement in any Stored Procedures.

View Replies !   View Related
Using Variables In DDL

I think this is an easy question.

Is it possible to use a variable in a create database statement


declare @db_name varchar(20)
select @db_name='new_db'

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db_name)

CREATE DATABASE [@db_name] ON (NAME = @db_name, FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataew_DB_Data.MDF' , SIZE = 300, FILEGROWTH = 10%) LOG ON (NAME = N'new_DB_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataImport_Utility_DB_Log.LDF' , SIZE = 30, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS

Also, if the above is possible, how can i pass a database name to the script if I am calling it from a batch file (using osql).



View Replies !   View Related
T-SQL Variables

I am testing SQL Server 7.0. In Stored Proc I try to select a database which name is stored in the variable @databasename.
I get the error that it can't open a database @databasename.
Can I declare the database object in T-SQL?


View Replies !   View Related
Using Variables In T-SQL
I have this script:
USE master

/* Get Name of Server & declare variables */
declare @sname varchar(30)
declare @db1 varchar(30)
declare @db2 varchar(30)
declare @db3 varchar(30)
declare @dbf1 varchar(30)
declare @dbf2 varchar(30)
declare @dbf3 varchar(30)

select @sname = rtrim(substring(srvname,5,30)) from sysservers

print 'The name of this server is: ' + @sname

Set @db1 = @sname + 'database1'
Set @db2 = @sname + 'database2'
Set @db3 = @sname + 'database3'
Set @dbf1 = @db1 + 'RL_log'
Set @dbf2 = @db2 + 'RL_log'
Set @dbf3 = @db3 + 'RL_log'

print @db1
print @dbf1


When I run it, I get the following errors:
The name of this server is: KANSASCITY
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@db1'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db2'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db3'.

Don't let the line numbers fool you. They refer to the number of lines since the last 'go' in the script. As you can see, the @db1 and @dbf1 variables are evaluating correctly.

I am attempting to change the setting of the Transaction Log to grow to fill up the entire disk. I do not wish to limit the space at this time. I have approximately 200 servers to manage and this script would be most useful in managing them, if it only worked.

Should I be using a different function to change the settings on the Transaction Log? Something other than ALTER DATABASE?

Thank you (in advance)

View Replies !   View Related
Hey folks,

i'm trying to make a variable inside of a loop do this.

@a = 1
@b = Variable + @a

and then inside of the loop @a incriments by 1, so i'd get

but I can't get the variable to work right...

Any help would be great!

View Replies !   View Related
Variables In SQL?
Basically I am creating a wizard(using forms) within a database to import client files and reformat them into the format that we need.

I have a form that list all 26 column headings that I need and tied to each column headings is a combo box that that lists the column headings from the imported client file.

I need to create a query that either appends it to an already formatted file or creates a new table with the column headings.

The problem is the field headings from the imported file change.
This is the sql that I used that doesn't work:

INSERT INTO Formatted_Data ( Field X )
SELECT Imported_Data.Forms![Import Data-3]![Combo33]
FROM Imported_Data;

Because Imported_Data.Forms![Import Data-3]![Combo33] is not recognized. I think if I could create a variable to replace the Forms! part then it would work but I do not know how to do this,

Thanks in advance to anyon that can help

View Replies !   View Related
Variables In MS SQL
I have a MS SQL server DB which stores tables and queries and a MS Access DB which acts as an interface and stores Forms.
I am a MS Access Developre and this is my first MS Sql Server DB. I would like to know how can i pass a variable which is an object in a Form (In MS Access DB) to a query in MS SQL?

In MS Access( i mean mdb file which all tables, queries and forms are in one mdb file)it is very easy, for example if you would like to pass idfld from Form of frminfo to a query you can say: Where idname like " & Forms!frminfo!idfld , but it does not work on MS SQL.
Would you please tell me how can i use a variable in MS SQL query?


View Replies !   View Related

I have a recordset that only has 1 record. I want to use this record to initialize the package variables. How do I do this? Or where do I do this?

View Replies !   View Related
I running a data extract to a text file from SQL2005 database.
Currently I have had to rewrite the query for every date range.
What I want to be able to do is let the user enter the date himself and then execute the DTS package, anybody know about this?

Paresh Motiwala
Boston, USA

View Replies !   View Related
DTS And Variables
I hope this is the place for questions about DTS.

I'm working on a DTS package that runs against development databases. At some point I'll move it to qual for testing, and I'll have to change names of servers and credentials for some of the connections. Can I do that with global variables? BOL aren't very helpful about this.

To be more specific. I'll have one "master" package that will be kicking off other packages. I've found in help that I can use dtsrun to pass variables - so I'm assuming that DBAs moving packages from one environment to another will adjust parameters passed by dtsrun to the "master" package. But how does the "worker" package reference global variable from the "master" package? And how do I reference global variable inside the "worker" package so that it's value is used for any of the properties I see when clicking on "disconnected properties" (like server name or user name)?

Is there some sort of tutorial out there that explains use of global variables?

Thanks in advance for any and all leads

View Replies !   View Related
Variables From VSA
Hi everyone,
Primary platform is XP Pro Sp2. Sql25k sp1.
Is it possible to see either user variables or system variables from VSA? I€™ve been looking for any option but I don€™t see nothing similar.
In fact, I am really thinking regarding old dts 2000 when you had a VbScript Task and you could see perfectly all the global variables defined in your package.
Thanks in advance and regards,

View Replies !   View Related
Hi everyone,


Is the only way to get a SSIS variable to change values via a configuration file or table?  I thought that at one point in developing some SSIS packages that  if I changed the value of a variable in a script that value was then retained. 



View Replies !   View Related
Using Variables

I have a simple task flow which takes data from an external table and puts it into a SQL server table. In the SQL server table I have a column which flags the data source. I have set up  a variable in the variable window to hold a constant which was the appropriate value for the column. I cannot see how to map the variable I have created to the column in my output table.

Not sure if variables do what I think they do, can  anyone give me a simple overview, and point me in the right direction to add my constant into the output table.


Thanks in advance



View Replies !   View Related
Variables In Variables
It it possible to define a variable that is the result of 3 other variable ?

like @[User::Total] = @[User::varA] + @[User::varB] + @[User::varC]

I know that i can set it with a scripttask but is there a way to define it when creating the variable ?


View Replies !   View Related
Using Variables
I have around 40-50 packages.
I need to use a Variable do some control.
Let says it's DateFormat

My Question is

From BIDS i can change the value before i run the package. I might change it according to user's requirement

How if i plan to install the package to SQL server and it will run/called using SQL AGENT.

View Replies !   View Related
OLE DB And Variables
Hi all

I got an SSIS package with an OLE DB connection (Access) and SQL server connection. I'm trying to copy some tables in the OLE DB connection to the SQL server without creating individual data flow tasks. I was able to create blank tables with the same table names as the Access database using a ForEach container loop.

Now I tried executing a similar method to copy the data from the Access database to their corresponding table in the SQL server database. It gave me an error saying the SQL statement is incomplete. I tried executing a very simple SQL statement connected to the OLE DB connection: select * from @curtableName where @curtableName is the current table the ForEach container loop is looking at. Again same error occurs.

My question is, is there a way for me to execute this SQL statement? Or will I have to create a data flow task for each individual table?

Thanks in advance


View Replies !   View Related
Using Variables

Is there a way to manipulate the value of a variable with out using a skripttask?
I just want to  var1 = var2.Substring(5)

View Replies !   View Related
Help With Variables

I am trying to set this up with variables instead of the file path and table name, so if they change I do not have to change them everywhere. But when I change it from the string 'c:file.csv' to "' + @fileName   + "'  it errors. Is it possible to do this or am I just missing something?


Code Snippet

DECLARE @doesExist INT

DECLARE @fileName VARCHAR(200)

DECLARE @myTableName VARCHAR(20)

SET @fileName = 'c:file.csv'                                                                      --variable

SET @myTableName = 'my_table_name_here'                                            --variable






WHERE TABLE_NAME =  "' + @myTableName  + "')                      --HERE


CREATE TABLE temp_table


One int,

Two int,

Three int,

four varchar(1) ,

five int




EXEC xp_fileexist "' + @fileName   + "' @doesExist OUTPUT                     --HERE




IF @doesExist = 1


BULK INSERT temp_table

FROM  "' + @fileName   + "'                                                                      -- HERE


Thanks again


View Replies !   View Related
Using Variables In T-SQL
SQL is still relatively new to me, and I haven't been able to figure out how to really make variables work for me (unless I'm using a cursor -- but I'd really like to avoid them).

Here is an example to demonstrate my problem:

Code Block

SELECT i.Item_ID, i.Size_Height * i.Size_Length / 144 AS Sq_Ft, i.Total_Sales/(i.Size_Height * i.Size_Length / 144)
FROM Item i
(Forgive the fact that there could be a divide by zero error for now - I'd like to keep the code simple to demonstrate my problem)

You see, what I really want to do is stuff that "ic.Size_Height * ic.Size_Length / 144" into a variable and reuse it so it might look something like...

Code Block

DECLARE @sqFT float
SELECT i.Item_ID, (SELECT @sqFT = i.Size_Height * i.Size_Length / 144) AS Square_Feet, i.Total_Sales/@sqFT AS Sales_Per_FT
FROM Item i

but SQL doesn't seem to enjoy that very much - probably because there doesn't seem to be a way to set a variable and use it at the same time.  This example is pretty trivial, but a lot of the stuff I need to do involved pretty complicated queries that build off of a bunch of results from existing queries.

I did all of what I needed with a cursor but it took well over 7 times the amount of time to generate my results as did a query like the one above.  I absolutely need to keep the performance on this pretty solid as what I need to use this for will return the results to a web application.

Does anybody have any suggestions?  Thanks a lot in advance for any help!

View Replies !   View Related
Using Variables
Hi there,
Is it possible to store a server name in a variable?

View Replies !   View Related

Copyright © 2005-08, All rights reserved