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.
Hi, I have a data file which consists of data as below, 4 PPU_FFA7485E0D|| T_GLR_DET_11||
While iam inserting into table using bulk insert, this pipe(||) is also getting inserted into the table, here is my query iam using to insert the data using bulk insert.
BULK INSERT TABLE_NAME FROM FILE_PATH WITH (FIELDTERMINATOR = ''||'''+',KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '''')
SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)
SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)
AS Document"
EXEC (@QUERY)
This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
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'
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
How can I insert ' (Apostrophe) into sql table field ? Insert Into Table(Field) Values(?) After executing sql statement above, I want to see ' (apostrope) in field. Thanks
i have tbl_location which includes userid, building, room. i combine the building and room into one feild called mailstop
SELECT Userid, Building +'/'+Room AS mailstop FROM tbl_Location
i then want to take this recordset and insert it into a field called mailstop in my employee table. but they must based upon the userid of the location table and the userid of the employee table.
so the userid of the location table must match the userid of the employee table and insert that mailstop value into the mailstop feild in employee table. i want to get this right the first time. any help would be greatly appreciated.
hi everybody I have this case which I couldn't solve and need badly your help
table1
id1 field1 field2 1 a b 2 a c
table2
id1 field1 field2 a b a c
I need to update table2 on the (id1) if the value of field1 and field2 in table 1 is equal to field1 and field2 in table 2 and inserting null when there is no match:::
I'm trying with this code but couldn't get thru
UPDATE table2 SET id1= COALESCE(table2.id1, 0) from table1 left outer Join table2 ON table1.id1=table2.id1
I have a table with 1000000 records i try to add a field with following`spec.
[mkey] [int] IDENTITY(1,1) NOT NULL
I get the following meassage:-
yearly' table - Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.
Then I Have the following error:- Time out error
- How is the best way to copy a large table from one to anther.
I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost) How do i obtain this value and how would I supply it to the second INSERT statement?
i have a student table and i created a stored procedure to insert a new student in this table but student_id field wich i put it as primary key got error because allready record has same value .how i can know the last row's student_id value and input a new valid value in one stored procedure thanks
I have been tasksed to create a data table and stored procedure to extract a special formatted XML file that is an attachment with a standard XML envelope. The XML file is an attchment in a node within the XML wrapper. There are other MIME files (pdf's ) that are handle by a seperate procedure. But I need to just extract the XML file attached along with those and put it into the datable with some other PK?FK fields.
Is a blob the best datatype. How to I insert that XML file into it?
I have created a database in the SQL Server 2005 and the same database in sql mobile. I have a field, which has a GUID and uniqueidentifier, when i tried to insert the same data which is there in sql server 2005 database into sql mobile database it is giving me error as below
Major Error 0x80040E14, Minor Error 25501
> INSERT INTO TypeValue (TypeValue_ID,DisplayValue,TypeName,OrderIndex,IsActive)
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 am new at ASP.net and I am having problems inserting data using C# in ASP.netI have created a table named "Profile" in the MS sql server database named "MyDataBase". There is a field named "ID" that has data type 'uniqueidentifier'.I am confused how to INSERT data into this data field. I have used MS Access and MYSQL in which there is an option of auto increment so we don't a unique identifier for each record.Please tell me what can I do to If I want to have a uniqueidentifier for each new record I INSERT in the "Profile" table of MS sql server database.While trying to insert, I get following errorsCannot insert the value NULL into column 'ID'and I don't know how I can insert something in this field that is of value type unique identifier.Please help me I will be very thankfull of you.
When both the two fields are set to SQLCHAR data types the data imports successfully without the quotes as 01 and 02. These fields will always be numbers and I want them as integers so I set the data type to int in the database and SQLINT in the format file. The results was that the 01 became 12592 and the 02 became 12848. where these numbers are coming from?
I am currently working on an application that requires, insertion of the results of a SQL Query in to the field of another table, in the form of a comma separated values.
For example, to explain things in detail:
create table dbo.phone_details
(country varchar(20), state varchar(30), list_of_toll_free_numbers text)
insert into dbo,phone_details values ( 'USA', 'CA', 'select Phone from phone_table where substring(phone, 1, 3) in ('800', '866', '877', '888')' )
The final output I desire is:
country state list_of_toll_free_numbers
---------- ------- ----------------------------------------- USA CA 8009877654, 8665764398, 8776543219
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.
In my SQL Server 2005 database I have created a table with several data fields. One of the fields is designated as a primary key. And another one is just a key. I can easily set the first field as the primary key, but am not able to set the second field as being just a key field. I perform these steps to set the primary key. (1) Right click on the table name and then select 'Open Table Definition' (2) I right click on the field in my data table and select 'Select Primary Key' As I mentioned previously, to set another field up as just a key field, I am not able to do this. My choices are the following when I right click on the data field to be designated as the key field: Set Primary Key, Insert Column, Delete Column,Relationships,Indexes/Keys,Full Text Index,XML Indexes, Check constraints,Properties. None of these choices will allow me to set the field up as a key field. Someone please help me out with this problem.
Let's say, I have Table1 and Table2, both with the exactly samestructure and data in it, except that Table1.Field1 is empty in databut Table2.Field1 have data in it.How could I copy all the data from Table2.Field1 to Table1.Field1?Is there any simpler way except looping through all the records to doit?
so the e.g. FIELD1 is a data in DefinitionTable and a field name in OrigTable..
my problem is how to get the TxtColor in merging this table.. my sp in fetching the data but only the fielddescription is
create procedure Definition @SearchKey int, @FieldTypeint as begin DECLARE @FieldName varchar(10), @FieldDesc varchar(30), @TextColor int, @mysql varchar(4000) SET NOCOUNT ON
SELECT @mysql = 'select o.origkey, '
DECLARE MyCursor CURSOR READ_ONLY FOR SELECT FieldName1, FieldDesc FROM DefinitionTable WHERE FieldType = @ResultType
Open MyCursor FETCH NEXT FROM MyCursor INTO @FieldName, @FieldDesc WHILE @@FETCH_STATUS = 0 BEGIN select @mysql = @mysql + 'o.' + @FieldName + ' as ' + @FieldDesc + ', ' FETCH NEXT FROM MyCursor INTO @FieldName, @FieldDesc END
CLOSE MyCursor DEALLOCATE MyCursor
select @mysql = @mysql + ' o.results from OrigTable o where FieldType = ' + @ResultType
execute(@mysql)
end
the result is Ok as
OrigKey ABC CDE EFG Result 8 test1 test2 test3 ok
But the problem is how to include the TxtColor or concatenate TxtColor with this output below
OrigKey ABC CDE EFG Result 8 test1+##1111 test2+#222 test3+#3333 ok
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.
