I'm passing data into sql proc. then insert into two diffrent tables. The two table dont have 1:1 relations. I'm trying to come up with the best way to insert the values into these two table. Here is what I have-- any suggestion?
fyi. I will love the proc to check errors during the process
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime declare @enddate as datetime declare @Line as Integer DECLARE @count INT
set @startdate = '2015-01-01' set @enddate = '2015-01-31'
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??
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()); DataSource.Insert();
I have SQL Server Management Studio Express (SSMS Express) and SQL Server 2005 Express (SS Express) installed in my Windows XP Pro PC that is on Microsoft Windows NT 4 LAN System. My Computer Administrator grants me the Administror Privilege to use my PC. I tried to use SQLQuery.sql (see the code below) to create a table "LabResults" and insert 20 data (values) into the table. I got Error Messages 102 and 156 when I did "Parse" or "Execute". This is my first time to apply the data type 'decimal' and the "VALUES" into the table. I do not know what is wrong with the 'decimal' and how to add the "VALUES": (1) Do I put the precision and scale of the decimal wrong? (2) Do I have to use "GO" after each "VALUES"? Please help and advise.
Thanks in advance,
Scott Chang
///////////--SQLQueryCroomLabData.sql--/////////////////////////// USE MyDatabase GO CREATE TABLE dbo.LabResults (SampleID int PRIMARY KEY NOT NULL, SampleName varchar(25) NOT NULL, AnalyteName varchar(25) NOT NULL, Concentration decimal(6.2) NULL) GO --Inserting data into a table INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration) VALUES (1, 'MW2', 'Acetone', 1.00) VALUES (2, 'MW2', 'Dichloroethene', 1.00) VALUES (3, 'MW2', 'Trichloroethene', 20.00) VALUES (4, 'MW2', 'Chloroform', 1.00) VALUES (5, 'MW2', 'Methylene Chloride', 1.00) VALUES (6, 'MW6S', 'Acetone', 1.00) VALUES (7, 'MW6S', 'Dichloroethene', 1.00) VALUES (8, 'MW6S', 'Trichloroethene', 1.00) VALUES (9, 'MW6S', 'Chloroform', 1.00) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00 VALUES (11, 'MW7', 'Acetone', 1.00) VALUES (12, 'MW7', 'Dichloroethene', 1.00) VALUES (13, 'MW7', 'Trichloroethene', 1.00) VALUES (14, 'MW7', 'Chloroform', 1.00) VALUES (15, 'MW7', 'Methylene Chloride', 1.00 VALUES (16, 'TripBlank', 'Acetone', 1.00) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00) VALUES (19, 'TripBlank', 'Chloroform', 0.76) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51) GO //////////Parse/////////// Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '6.2'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'VALUES'. ////////////////Execute//////////////////// Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '6.2'. Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'VALUES'.
I'm trying to insert data into a table from two tables into a single table along with a hard coded value.
insert into TABLE1 (THING,PERSONORGROUP,ACCESSRIGHTS) VALUES ((select SYSTEM_ID from TABLE2 where AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID =('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID =('USER2')),255)
I get the following-
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Is it possible to insert data into a table from a temporary table that is inner join? Can anyone share an example of a stored procedure that can do this? Thanks, xyz789
Hi, I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table. Overall it is like storing the history of the table updation. How do i write a stored procedure for this? Anybody has done this before? Pls help me.
I want to insert the data from temp table to other table. Only condition is, it needs to sorted based on tool number and tool date. For example if we have ten records for tool number 1000, it should be order by tool number and then based on tool_dt. Both tables doesn't have any primary keys. Please find below my code. I removed all the unnecessary columns for simple understanding. INSERT INTO tool_summary (tool_nbr, tool_dt) select tool_nbr, tool_dt from #tool order by tool_nbr, tool_dt...But this query is not working as expected. Data is getting shuffled.
I had a function like below :Public Sub Getdata2(ByVal query, ByVal db, ByVal name) Dim selectSQL As StringDim con As SqlConnection Dim cmd As SqlCommand Dim reader As SqlDataReader Trycon = New SqlConnection("User ID=xxx;password=xxx;persist security info=True;Initial Catalog=database1;Data Source=xxx.xxx.xxx.xxx.xxx,xxxxx;") Dim username As String username = Request.QueryString("username") selectSQL = "SET DATEFORMAT DMY;Insert into table1(hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, username, DGP, Remarks, Sub_Code, caveat, consider, age) select hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, '" & username & "', DGP, Remarks, Sub_Code, caveat, consider, age from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 where " & querycmd = New SqlCommand(selectSQL, con) con.Open() reader = cmd.ExecuteReader() lbl.Text = selectSQLCatch ex As Exception lbl.Text = ex.Message Finally If (Not con Is Nothing) Then con.Close() con.Dispose() End If End Try End Sub '------------------------------------------------------------------------------------------------------------------------------------------------------------------------- May i know that how do i retrieve data from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 due to diffrent server, diffrent UID and Password
Hello, I have a table called #table1 which is populated as in the example below. I would like to write a selectinsert statement based on #table1 that populates #table2 like in the #table2 example. Note #table2 is a fixed table that follows the structure below.
Can any of you T-SQL gurus help me with my problem? Any help will be most appreciated. Thanks
Insert into #table1 select '012345','2008-04-19 00:00:00.000' , '01', '06', '05', null, null UNION all select '012345','2008-04-20 00:00:00.000' , '01', '07', '05', '07', '03' UNION all select '012345','2008-04-21 00:00:00.000' , '01', '06', null, null, null UNION all select '012345','2008-04-22 00:00:00.000' , '01', '02', '05', '07', null UNION all select '012345','2008-04-23 00:00:00.000' , '03', '06', null, null, null UNION all select '987654','2008-04-21 00:00:00.000' , '19', '21', null, null, null UNION all select '987654','2008-04-22 00:00:00.000' , '01', '02', '05', '16', null UNION all select '987654','2008-04-23 00:00:00.000' , '03', '06', null, null, null
select * from #table1
--This is the table i would like to insert my data into CREATE TABLE #table2 --max of 5 orders in 1 day --it does not matter what date the date order was made the 1st date would appear in dateorder1 and so on... ( custID nvarchar(6), dateorder1 [datetime] NULL, order1_1 nvarchar(2), order1_2 nvarchar(2), order1_3 nvarchar(2), order1_4 nvarchar(2), order1_5 nvarchar(2), dateorder2 [datetime] NULL, order2_1 nvarchar(2), order2_2 nvarchar(2), order2_3 nvarchar(2), order2_4 nvarchar(2), order2_5 nvarchar(2), dateorder3 [datetime] NULL, order3_1 nvarchar(2), order3_2 nvarchar(2), order3_3 nvarchar(2), order3_4 nvarchar(2), order3_5 nvarchar(2), dateorder4 [datetime] NULL, order4_1 nvarchar(2), order4_2 nvarchar(2), order4_3 nvarchar(2), order4_4 nvarchar(2), order4_5 nvarchar(2), dateorder5 [datetime] NULL, order5_1 nvarchar(2), order5_2 nvarchar(2), order5_3 nvarchar(2), order5_4 nvarchar(2), order5_5 nvarchar(2) )
TABLE1 ID Roll Name Amount . . . . . . so on --------------------------------------- 1 2 Alex 500 2 5 Jones 600 3 2 Ales 400
and we have TABLE 2
ID Roll Name Amount . . . . . . . so on
In both the Tables ID Field is Identity Field and rest all the columns are identical in both the tables.I want to perform a query in such a way that SQL Should,
1) Remove all data from TABLE2 First 2) Select those records from TABLE1 where Roll = 2 3) Then, Insert the results to TABLE 2
So we should get the result similar to below:
ID Roll Name Amount . . . . . . . so on --------------------------------------- 1 2 Alex 500 3 2 Ales 400
I have two databases in SQL Server and they both have almost the same tables. I want to insert the data of a table from the first database into a table with the same name, but from the second database. How can i do this?
I have to tables like given below Landing table "A" (Data load will happen over here, No primary keys mentioned over here) table "B" .Now I want to move the data from A to B.I have made use of below query insert into B select * from A...Landing table "A" has huge no of records, MS SQL server is taking huge amount of time.any alternative way to make this insertion process faster?
If I have 2 tables (Source and Destination) which have the same fields except the fields are not in same order, what is a practical way to insert all data from Source into Destination? I know that I can do insert with select but then I need to specify every single fields since the fields positions are different between Source and Destination.
SALAM SIR, CREATE TABLE Airlines_Master ( Aircode CHAR(2), Airlines_name VARCHAR(15)) THIS IS MADE A TABLE IN SQLSERVER 2000.BUT HOW CAN INSERT DATA INTO THIS TABLE?
I wan t to insert into a tabla data from other tabla using a parameter, my question is. What is the better tool to make this? "Execute SQL Task" or extrat te data using "OLE DB Source" and then use "OLE DB Destination" to insert the data into the new table?
Hi, I'm not user to inserting data into databases, usually I just read the data. So I think my problem might be pretty common.I have a table of longitudes, latitudes, city names, and country names. I set the primary key to be the columns longitude and latitude. I have a method that generates the user's location and the mentioned data. So I want to only insert the new data into the database if it is new and unique. currently if the same user goes to my site, it inserts the data fine the first time and then throws and error the second time because it is inserting duplicate primary key information. Do I need to query the database to see if the data record already exists? or is there a way to insert the record only if it is "new"?? Thanks for the help!!
Hi I have a table called Version and its attributes are Version_ID, Project_ID , Hospital_ ID , Date_Created and comments. I want to select the data by Version_ID,Project_ID and Hospital_ID and the selected data is inserted in the same table(Version) as new row . Table: Version (Version_ID(Primary_key), Project_ID(Foreign_Key),Hospital_ID(Foreign_Key),Date_Created,Comments). Iam using Visual Web Developer Express and SQL Server 2005. Iam doing on asp.net 2.0. Could anyone please send me the code asp.net 2.0 for the above problem.
INSERT Into aspnet_Membership (ApplicationId,UserId,Password,PasswordFormat,PasswordSalt,Email,LoweredEmail,IsApproved,IsLockedOut,CreateDate,LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,FailedPasswordAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAnswerAttemptWindowStart)
Select '{FB1DA11C-F1AB-4E42-AAFE-E9EC8D6C607F}',UserId, cast(Rand() as nvarchar),0,'0',UserEmail,UserEmail,1,0,getdate() ,getdate() ,getdate() ,getdate() ,0,'01/01/1754',0,'01/01/1754' From aspnet_Users
End
the problem is the Password field i try to insert to every record other password, but it give me at first a random numbers but it appear at all of the records... what can i do that each record will get a random password?
Hello, I am using a database .mdf and I want to insert values in a table (I mean how to connect also with a database, open it and store the values). Can someone describe the whole process to do that (as I am newbie in asp.net). I want the whole code if it is possible. For example I am using the above code to create 2 textboxes where the user will write his name and password. Then I want to store (by clicking the button) the values to a table in database. Thank you <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Manufacturer.aspx.vb" Inherits="Manufacturer" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"><title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server" Text="Name"></asp:Label> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <asp:Label ID="Label2" runat="server" Text="Password"></asp:Label> <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> <br /> <br /> <asp:Button ID="Button1" runat="server" Text="Submit" /></div></form> </body> </html>
If you are doing an insert into a table with 10 columns, but you only have values for 2, is it better to insert 2 and let the table default the others or is it better to insert all 10 using default values for the 8 you don't know? Do you know of any benefits one way or the other? Thanks in advance.
How do I set up an insert trigger to copy all of the inserted data to another table? In other words, when someone adds a new paramater in the params table, I want to automatically create a matching set of data in the goals table. Thanks,Krista
OK, this one should be easy for you folks but my mind is drawing a blank. Can someone give me an example query to copy data from one table to another? Both tables already exist and both tables have the same table definition. tblA has data tblB is completely empty. I just want to copy from tblA into tblB.
I dont want to use the GUI, nor do I want to do a "Select * Into..."
I am looping through all the databases for a table and trying to insert data into two columns in that table. So evrything seems right. but when i run the program it says
Msg 515, Level 16, State 2, Line 4
Cannot insert the value NULL into column 'OrganizationID', table 'DesMoines_DEV.dbo.Organization'; column does not allow nulls. INSERT fails.
I think writing a dynamic query that produces a series of insert statements is wrong... Instead a dynamic query that execute insert statements one by one may work... But I couldn't figure how to approach..
Below is the code
SET @dbLoop = CURSOR FOR SELECT name FROM sys.Databases WHERE name NOT IN ('master','model','msdb','tempdb') AND State_desc = 'ONLINE'
Hi all...Can anyone give clue in doing below?Let say i have a table A with the following 3 fields. 1)Date2)Title 3)Status.i need to insert into table B (also have the same 3 fields) from thistable A with the condition where Title is "Alarm" and Status is "ON".This can be done by a simple "INSERT" query.However, there's a case for table A in like below:Date Title Status------ ------ ----------5/7/07 1:05:23am ALARM ON5/7/07 1:05:24am ALARM ON5/7/07 1:05:25am ALARM ACK5/7/07 1:05:25am ALARM ONBased on the table A above, i only need to insert from table A intotable B the first 2 ALARM(ON)s. The third ALARM(ON) which has the sameDate with ALARM(ACK) needs not to be inserted into table B.How can i write a simple SQL query which can insert all ALARM(ON)swhich doesnt have same date with ALARM(ACK)?Thanks. Pls help.Albert
I have one one XLS File i need to import data in 2 diffrent SQL SERVER 2005. For e.g One Master Table X and Child Table Y. X Table columns mastertabid, col1,col2,col3, etc etc..master table columns are fix. and Child table columns childtabid,mastertabid,childcol1,childcol2,childcol3,childcol4, etc. etc.. Child table columns are not fix it may be 5,10,15 or 20... Now, at the time of importing XLS file i need to insert data in both table. most importing thing is i need to add data in child table with reference of mastertabid..i have to add this column value.
I can do it with business logic, i can do it but it makes my page slow...and i dont want to do that...
I am new to this side of the sql and just testing some statements
what I want to do
insert one line into a table that already has data in it
KBCopy = Database name KBStatusID = (PK, tinyint, not null) KBStatusName = (varchar(100) not null)
the following is what I have tried.
use KBCopy
go
insert into KBStatus ("KBStatusID", "KBStatusName") Values ("4", "Archived")
the error I receive
Msg 128, Level 15, State 1, Line 2
The name "4" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
I am new to SSIS, and was only a novice to intermediate skill level with SQL 2000 DTS, so please excuse me if this is an easy question. I am trying to import data from a table in one DB into a table in another. After insertion, I need to store the newly created ID (an identity seed) in a separate table that maps to the original DB's row id. My eventual goal is to import a bunch of related tables from the old DB into the new DB, and maintain relationships, so the mapping of newly created IDs is necessary to make sure data is imported with the correct relationships. Any advice would be greatly appreciated!
