SQL Server 2012 :: Get Empty Data Set For Inserting Data?
Nov 11, 2014
I have 2 tables in my database.
one is Race table and 2nd one is Age Range.
I want to write a query where I can see all races and age range as column.
TblRace
ID, RaceName
TblAgeRange
ID,AgeRange.
There is no connection between this two table. I need to display result like below.
Race 17-20 21-30 31-40
A
B
I
W
How do i get this kind of empty data set so that I can fill it out in front end or any better solution. The age range will be displayed as many row as they have. It's not static. Above is just an example.
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END
However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
I am facing a strange problem in executing stored procedure. Basically my sproc will take a values from Java application and create a Insert statement. see stored procedure below.Just to give some more background- I am re writing the procedure which was written in oracle already.
Problem I am facing now is with the statement below . When I execute the procedure for first time it works fine however when I execute for second time onwards it is setting to empty. Not sure what is the problem with my declaration and setting up with values. For reference I have pasted my complete stored procedure code below.
Do we have any way to insert,update,delete data from one table and update the changes onto second table. Also, while updating records into second table, can the data be encrypted.
I tried using view and it can insert, update, delete without any issues. But if i tried to encrypt any fields after inserting data into view, I am unable to do it.
CREATE Tableb_vw ON TableB Instead of Insert AS Begin update TableA set Lname = ( --UserName = 'User' + substring(convert(varchar(32), UsersTrID), 1, 8) SELECT REPLACE(LEFT(Lname, 2), '''', 'Z') AS LNAME) FROM TableA end
What I would like to get:
1. Can we update base tables and encrypt second table data while inserting or updating data 2. If not supported using base tables, can we do using views to encrypt view data [Some fields]
Why shrinkfile empty file does not redistribute data evenly in the primary file group with multiple files:
Please run the script attached to see what the end result is.
This is what I set up last night on my test machine.
1) Create database [FGTest] size 200MB 2) Create table called TEST on primary 3) Insert 40MB of data into test 4) Create another file group called temp in primary size 200MB 5) Shrinkfile('FGTest',emptyfile) so that all data is transfered from FGTest into temp file group. 6) Add another 2 files called DATA2 and DATA3. Both are 200MB. 7) We now have 3 empty files that I want data distributed evenly on. FGTest, DATA2 & DATA3 8) Shrinkfile('temp',emptyfile) to move all the data from temp over the 3 file groups evenly
I would expect at this stage to have the following:
FGTest = 13MB, DATA2 = 13MB, DATA3 = 13MB
(40MB of data over 3 files should be about 13 MBish in each file)
What I actually end up with is this:
FGTest = 20MB DATA1 = 10MB DATA2 = 10MB
It looks as though SQL Server is allocating 50% of all data to the original file and then 50% evenly over the remaining files in PRIMARY.
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData ( Id BIGINT, --is actually an identity column Manifest_Id BIGINT, Doc_Num BIGINT, Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X) FROM #InvLogData GROUP BY X HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
I was running an operation to shrink file/emptyfile a data file, and then remove it.
It blocked and caused a huge mess, I suspect on the removal part. But I want to confirm that the emptyfile completed (and that the engine isn't going to try to put more data in there for when I schedule the removal part again a week or more from now).
How does the engine know not to put any more data in there, and how long does that situation last?
I am trying to transition from Access to MS SQL. I downloaded and installed MS SQL 2005 Express and the Server Management Studio Express. Keep in mind that I am a total newbie to this database since I've always used Access. My question is, how do I insert data manually into my tables? I figured out how to set up a table and query a table, but is there somewhere I can just plug in data? In Access, I just open the table and type it in. Also, how do you set an auto increment numeric field in a table?- Jason
select @counter=300000 while @counter > 0 begin insert into Revenue (Instr_type, Tel_no, Phone_Id, Rpt_date, Pay_mode) values("PP0073", @counter, "080464", "19990901", 1) select @counter=@counter-1 end
HI there, I run the above statement in Query Analyzer and the expected result should be 300,000 records inserted into Revenue table. But unfortunately the actual records inserted were less than 300,000. I also realise that it insert different amount of record each time I run it. Can anyone please tell me why?
Could anyboby please tell me any way which can quickly inserting large data to SQL Server except using cursor?
Every morning, I use DTS to transfer around 100,000 data from foxpro to SQL Server 7.0 temporary table. Then I use cursor to check if the data already exist in the SQL Server table, then I do update; if the data does not exist in the SQL Server table, then I do insert into. In SQL Server database, each table already has over million record, also I have 12 table with the same situation.
Now I find cursor very slow to do above, can anybody tell me any way which can quickly to do update and insert?
Hello all, I'm using sqlserver 2005 express edition. I'm working in an application which has the functionality of inserting datas from the excel file to sql server 2005 database. Can anyone please guide me for performing this task. Thanks in advance. Ravirajdanasekaran
Hi, Just look at my Code:protected void Button1_Click(object sender, EventArgs e) {SqlConnection Conn = new SqlConnection("MyConnectionString"); SqlCommand Cmd = new SqlCommand();string linktext,headline, quote, para1, para2, para3; linktext = this.TextBox1.Text;headline = this.TextBox1.Text; quote = this.TextBox2.Text;para1 = this.TextBox3.Text; para2 = this.TextBox4.Text;para3 = this.TextBox5.Text;Cmd.CommandText = "insert into desieyenews(linktext,newsheadline,quote,para1,para2,para3) values ("+linktext+","+headline+","+quote+","+para1+","+para2+","+para3+")"; Cmd.Connection = Conn; Conn.Open(); Cmd.ExecuteNonQuery(); Conn.Close();
}
Details: In the above code I am inserting data from 5 textbox to my databse. It works fine but when I am trying to insert text like Tom's..., problem occurs. Whenever I am trying to insert some text with a single quote, the problem arises. Although the datatype of my database is text in MS SQL Server 2005. Please give me some solution so that I can get rid of this problem.... Thanks in advance.
Hi everyone!Hope that someone can help me solving this problem.I have a form where the user can register by putting his private data. Each time that he submits his data, if he is using Internet Explorer, it will insert blank data into sql server database. But if user is using Firefox, everything is working well, and all data is inserted.What seems to be the problem?Why in IE, data is inserted as blank?!Thanks for your possible help and attention to this issue.Hope that someone can help me.Best regards,Mesk
hi I want to read data from XML file and insert that data from XML file into the Database Table From ASP.NET page.plz give me the code to do this using DataAdapter.Update(ds)
This is my code: Dim myConn As SqlConnection Dim mycmd As SqlCommand myConn = New SqlConnection("Initial Catalog=science;" & _ "Data Source=localhost;Integrated Security=SSPI;") mycmd = New SqlCommand("INSERT into STEP1(firstname) VALUES('Amin')", myConn) myConn.Open() mycmd.ExecuteNonQuery() myConn.Close()
This is the error message I get: The name 'firstname' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
I have some website work lined up and it involves some simple modifications to a MS SQL 2000 server. What I'll need to do is add some new data fields and insert some data.
I have some experience with databases - MS Access and MySQL, but I have never used or seen MS SQL 2000. My question is, is this a relatively simple thing to do for someone who hasn't used it before? I can do these things quite simply in Access or MySQL, so is MS SQL 2000 going to be any different?
Also, does anyone know of any free tutorials online that would help me out?
Hi to all.. I am new to ASP.Net, I want coding for inserting data in to SQL server 2005 that code contain the following criteria. 1.ASP.Net using C#.Net. 2.Using Stored Procdure. 3.the connetion string ,command , Data reader is in the same class. this class will be reusable for other page also without modifying any thing in it. Please Help me. Advance thanks.
Hello all, I am having problems running a stored proc from an aspx.cs file. Basically I want to extract data from webForm controls and create a new record in a DB table. I have watched the "Getting Started" videos on thi site, and the only difference between my code and the code of the demonstrator is the connection string. My conn string:- dataSrc.ConnectionString = ConfigurationManager.ConnectionStrings["ProjectTblConnString"].ConnectionString;Demonstrator conn string:- dataSrc.ConnectionString = ConfigurationManager.ConnectionStrings("ProjectTblConnString");When I debug with the string as shown by the demonstrator the error list reports that ConnectionStrings is a property and I am trying to use it as a mothod, which is fair enough, but I am just confused as how it worked for the demonstrator and not myself. When I debug with the code I used above I get no error, but nothing is inserted in the DB.Also, it looks as if the only class in my aspx.cs file that is actually being executed is PageLoad() - the remainder of the code seems to be ignored. Below is the entired aspx.cs file:- 1 using System; 2 using System.Data; 3 using System.Configuration; 4 using System.Collections; 5 using System.Web; 6 using System.Web.Security; 7 using System.Web.UI; 8 using System.Web.UI.WebControls; 9 using System.Web.UI.WebControls.WebParts; 10 using System.Web.UI.HtmlControls; 11 12 public partial class CreateProject : System.Web.UI.Page 13 { 14 protected void Page_Load(object sender, EventArgs e) 15 { 16 17 if (User.Identity.IsAuthenticated == false) 18 { 19 Server.Transfer("Default.aspx"); 20 } 21 22 if (chkbox_startDate.Checked == true) 23 { 24 txtbox_startDate.ReadOnly = true; 25 txtbox_startDate.Text = (System.DateTime.Now.ToString()); 26 } 27 28 } 29 30 protected void btn_create_Click(object sender, EventArgs e) 31 { 32 //connection string for connecting to SQL Server DB 33 SqlDataSource dataSrc = new SqlDataSource(); 34 dataSrc.ConnectionString = ConfigurationManager.ConnectionStrings["ProjectTblConnString"].ConnectionString; 35 36 37 //insert data in table using the CreateNewProject stored proc in the DB 38 dataSrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedure; 39 dataSrc.InsertCommand = "CreateNewProject"; 40 dataSrc.InsertParameters.Add("Project_Title", txtbox_title.ToString() ); 41 dataSrc.InsertParameters.Add("Description", txtbox_desc.ToString() ); 42 dataSrc.InsertParameters.Add("Start_Date", txtbox_startDate.ToString() ); 43 dataSrc.InsertParameters.Add("Primary_Dev_Lang", list_devLang.ToString() ); 44 dataSrc.InsertParameters.Add("Dev_Environment", list_devEnv.ToString() ); 45 dataSrc.InsertParameters.Add("No_Junior_Devs", txtbox_juniorDevs.ToString() ); 46 dataSrc.InsertParameters.Add("No_Senior_Devs", txtbox_seniorDevs.ToString() ); 47 48 int rowsAffected = 0; 49 try 50 { 51 rowsAffected = dataSrc.Insert(); 52 } 53 catch (Exception ex) 54 { 55 Server.Transfer(Error.aspx); 56 } 57 finally 58 { 59 dataSrc = null; 60 } 61 62 if (rowsAffected != 1) 63 { 64 label_confirm.Text = "Error, Contact system admin"; 65 } 66 else 67 { 68 label_confirm.Text = "Success"; 69 } 70 71 }//end btn_create 72 73 //if cancel is clicked set all values back to default 74 protected void btn_cancel_Click(object sender, EventArgs e) 75 { 76 txtbox_title.Text = ""; 77 txtbox_desc.Text = ""; 78 txtbox_startDate.Text = ""; 79 txtbox_seniorDevs.Text = ""; 80 txtbox_juniorDevs.Text = ""; 81 list_devEnv.SelectedIndex = 0; 82 list_devLang.SelectedIndex = 0; 83 chkbox_startDate.Checked = true; 84 label_confirm.Text = ""; 85 } 86 87 //if checked the current dateTime is inserted into txtbox 88 protected void chkbox_startDate_CheckedChanged(object sender, EventArgs e) 89 { 90 if (chkbox_startDate.Checked == true) 91 { 92 txtbox_startDate.ReadOnly = true; 93 txtbox_startDate.Text = (System.DateTime.Now.ToString()); 94 } 95 else 96 { 97 txtbox_startDate.ReadOnly = false; 98 txtbox_startDate.Text = ""; 99 } 100 }//end chkox 101 102 103 }//end class
Any help anyome can give is greatly appreciated. This is part of my Final Year College Dissertation which is due in 3wks !!!! Sláinte á chaire, Seán
Hi Friends, I am Ravi, I need a help. My case is like.. i need to upload data from csv file into sql server 2005 data table. but before that I need to check integrity of data for example: let us say csv data is like ISD CODE,STATE,NAME,QUALIFICATION 91,AP,KIRAN,MCA 01,MC,MIKE,MS
here i have to check that, wether there is an entry in ISD codes table for 91 and 01 (india and us) similarly AP , MC (Andhra Pradesh and MISSICippi) please suggest me a nice approach, no of records in ISD codes and States will 350-450 records.
Hi All, I am working on SQL Server 2000 ver 7.0. The Collation set for my Database Server is Latin. I want some way by which i can insert Japanese Characters in Database. Is it related to change the Collation or any other encoding format of database. Suppose the table 'Person' has fields id, Name, city If i enter name in a japanese characters, then while storing it does not recongnises this format.
insert into person values(8,'満員','osaka')
id name city 8 ?? osaka At the place of name '??' is displayed.
I am manually replicating parts of a SQL Server CE database (running windows mobile 5.0) to a centralized SQL Server 2000 database.
My program is throwing an exception whenever I try to insert an image data type into the 2000 server from the PDA. I am using parameterized queries.
Error is as follows: [error] System.Data.SqlClient.SqlConnection.OnError() at System.Data.SqlClient.SqlInternalConnection.OnError() at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run() at System.Data.SqlClient.ExecuteReader() at System.Data.SqlClient.ExecuteNonQuery() at PDASync.Database.ExecuteIDRemote() [/error]
The code for my ExecuteIDRemote method works fine for other queries. It also works if I remove the image column from the offending query.
