OK, I'm trying to add some audit type information to a set of tables in my database. This involves adding 2 columns and sticking some data into those two columns. The columns themselves are added successfully, but I'm having troubles sticking any data into them - I'm getting a lot of errors. What I want to do is put a single character code in one column, and a datetime in the second. Here's the SQL I'm trying...
Conversion failed when converting datetime from character string.
I tried changing the datetime column to varchar, and CONVERT(varchar(30),@TxDateTime). That gets rid of the above error, but replaces it with the following (twice, I might add - one for each column?):
Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Any help is appreciated - let me know if you need more info.
I would like to use a dynamic pivot table in my data source view. It seems that a named query can be only one sql statement. So, I cannot use my multi-statement procedure that creates a dynamic pivot table output.
What is the best course of action here? I could hard-code my pivot table query. I could maintain a redundant table in the pivot format. Do I have any good options?
I have a requirement to load bulk of csv files to sql table. some times, some columns could not come in csv file(some times 100 columns and some times 80 cloumns). That time the package is getting failed. How to create a table dynamically based on csv file structure.
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code FROM dbo.ADSCHL AD INNER JOIN TESTCEP FD ON AD.SCHOOL_NAME = FD.School_Name
Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.
Hi,First post so apologies if this sounds a bit confusing!!I'm trying to run the following update. On a weekly basis i want toinsert all the active users ids from a users table into a timesheetstable along with the last day of the week and a submitted flag set to0. I plan then on creating a schduled job so the script runs weekly.The 3 queries i plan to use are below.Insert statement:INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS.USER_ID,TBL_TIMESHEETS.WEEK_ENDING, TBL_TIMESHEETS.IS_SUBMITTED)VALUES ('user ids', 'week end date', '0')Get User Ids:SELECT TBL_USERS.USER_ID from TBL_USERS where TBL_USERS.IS_ACTIVE = '1'Get last date of the weekSELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)I'm having trouble combing them as i'm pretty new to this. Is the bestapproach to use a cursor?If you need anymore info let me know. Thanks in advance.
I'm attempting to explore the functionality of SSIS and all the available data flow transformations, and I'm trying to learn how to use the tool correctly. How would someone update a table with data which is calculated from that same table. This is a made up example. Lets say I have a table:
I would like to populate the field color_count with the count of items listed with that color.
Data: tst_key object color color_count 1, chair blue NULL 2, table black NULL 3 lamp black NULL ---- So the last column for 1st rec value would be 1, and for 2nd and 3rd, would be two. tst_key object color color_count 1, chair blue 1 2, table black 2 3 lamp black 2
-- With T-SQL I would use the following code:
update tst_source
set color_count = res.cnt
from tst_source
Inner join (select count(tst_key) cnt,color
from tst_source
group by color) res
ON res.color = tst_source.color
But I would like to use the Data Transformations. Can someone explain in great detail which Data Transformation tasks I would need to use?
I'm trying to update a single record in a table using data from a single record in another table. For the life of me I can't figure this out. I'm trying to avoid having to write a bunch of +=" string stuff in C#. Is there any way to do this with just SQL?Thanks in advance.
I want to add to a column in one table the data in another. I have tried different update statements and none work. My update statement currently looks like this:
update t1 set t1.amtYTD = t1.amtYTD + t2.Total from #ProgramData t1 join vw_MER_Reclass t2 on (t1.ProjId = t2.ProjId and t1.task = t2.task and t1.acct = t2.acct)
I need to take the amtYTD in table1, and add to it the value of Total in table 2.
I want to update table2.message based on the criteria of table1.name. for example, all records named John will be updated with 'Msg1' in table 2.message. I am using MS SQL 2000 and below is the scenario.
table1 columns ID Name
table2 columns ID Message
Select a.Id, a.name, b.message from table1 a, table2 b where a.id =b.id
a.id a.name b.message 1 John Msg1 2 Steve Msg2 3 Scott Msg3 4 John NULL - update b.message to 'Msg1' 5 Steve NULL - update b.message to 'Msg2' 6 Scott NULL - update b.message to 'Msg3' 7 John NULL - update b.message to 'Msg1' 8 Steve NULL - update b.message to 'Msg2'
If i will update the record per name i am using the query below and i am pre-selecting all the existing names.
update table2 b set b.message=(Select top 1 b.message from table1 a, table2 b where a.id =b.id
[Code] ...
How to update this in bulk without preselecting all the names?
I have two tables ingram and ingram_update that have the identical fields. I need to update ingram with the content in ingram_update. How do I do this? Thank you for your help!
Table1: FullName, City, State, Data1 Table 2: FullName, City, State, Data1, Data2, Data3
This is what I want to do... 1. For every record that is in BOTH tables I want to UPDATE TABLE 2 with the data from Table 1. 2. Then, if there are "extra"/new records in table 1 I want to append them to Table 2.
How would I do this? Can you provide syntax? I'm new.
helo, i'm the beginner in SQL Server...i have a problem to update table A...i have 2 tables table A and table B same field and same structure i want to update data in table A,but the data i take from table B table A : id,name,job,address table B : id,name,job,address
how to insert data from table B to table A and how to update data in table A? thx.
I need to update the Denominator column in one row with the value from the Numerator column in a different row. For example the last row in the table is
c010A92NULL
I need to update the Denominator, which is currently NULL, with the value from the Numerator where the MeasureID=c001 and GroupID=A.
From: JAGADISH KUMAR GEDELA [jgedela@miraclesoft.com] Sent: 10/10/2007 4:13:43 PM To: jgedela@miraclesoft.com [jgedela@miraclesoft.com] Subject: forum Hi all,
I need to Insert the XML File data into SQL SERVER 2005 db(table). For that I created the table with XML Native column (using typed xml) *********************************create table command************ CREATE TABLE XmlCatalog ( ID INT PRIMARY KEY, Document XML(CONTENT xyz)) *********************************** In order to Create the table with typed xml ,before that we have to create the xml schema which i mentioned below ************************************create schema command******** CREATE XML SCHEMA COLLECTION xyz AS 'Place xml schema file ’ ************************************ I created the xml schema file by using the xmlspy software.
--------------------------Insert command--------- INSERT into XmlCatalog VALUES (1,'copy xml file ‘) ------------------------------- I need to retrieve the xml data from the table ------------select query---------- SELECT Document.query (‘data (/X12//UserId)') AS USERID, Document.query (‘data (/X12/X12_Q1/header/ISA//ISA_Authorization_Information_Qualifier)') AS ISA_Authorization_Information from XmlCatalog. -----------------
I Need to update/insert/delete the xml data in the table
Can you please suggest the procedure to implement the above requirement(insert/update/delete)
Is it possible to allow a user to insert and update data in a table but prevent them from performing deletes against that same table? For auditing purposes I need to prevent the end users from being able to delete data.
I have a table dbo.Sales that contains all sales records. There is a column in that table called ItemNumber that I'd like to match with ItemNumber in a flat file and update the ItemCost based on the ItemCost column in the flat file.
So while there will be many sales records for each ItemNumber, I need to loop through and update the ItemCost in that sales record based on the corresponding ItemCost in the flat file. Does this make sense? I really need this for court and I can't figure out how to do it. I took a SQL course about 7 years ago but have forgotten everything.
There will be many sales records for each ItemNumber in the database table. I need to update each one with correct cost based on the item number and cost mapping from flat file.
Every month a client sends a spreadsheet with data which we use to update matching rows in a table in the database. I want to automate this using a DTS package but am having quite a bit of trouble accomplishing what I think should be trivial task. I've been attempting to use a Transform Data Task with a modification lookup but I just keep inserting the rows from the source excel spreadsheet in to the existing destination table without ever modifying the existing data.
Any guidance would be greatly appreciated as to a best practice approach.
iden nam status ----------- -------- -------------------- 1 pp NULL 1 kk NULL 2 rr NULL 2 nn NULL 2 jj NULL 3 hh NULL
now i want to update the status cloumn in this table in such a way that the status colum = 'Status is' + iden + nam for all distinct values of iden from the table
I need to update a large table, about 55 million rows, without filling the transaction log, in the shortest time as possible. The goal is to alter the table and change the data type for Text column from VARCHAR(7900) to NVARCHAR(MAX).
Since I cannot do it with an ALTER TABLE statement (it would fill up the transaction log) I'm thinking to:
- rename column Text in Text_OLD - add Text column of type NVARCHAR(MAX) - copy values in batches from Text_OLD to Text
The table is defined like:
create table DATATEXT( rID INTEGER NOT NULL, sID INTEGER NOT NULL, pID INTEGER NOT NULL, cID INTEGER NOT NULL, err TINYINT NOT NULL,
[Code] ....
I've thought about a stored procedure doing this but how to copy values in batch from Text_OLD to Text.
The code I would start with (doing just this part) is the following, but maybe there are more efficient ways to do it, or at least there's a better way to select @startSeq in the WHILE loop (avoiding to select a bunch of 100000 sequences and later selecting the max).
declare @startSeq timestamp declare @lastSeq timestamp select @lastSeq = MAX(sequence) from [DATATEXT] where [Text] is null select @startSeq = MIN(Sequence) FROM [DATATEXT] where [Text]is null BEGIN TRANSACTION T1 WHILE @startSeq < @lastSeq
Hi,I have an Excel file with 400 rows of old values and the correspondingnew values. My table currently has 10 columns out of which 3 columnsuse the old value specified in the excel file. I need to update thoseold values in the columns with the new values from the Excel file.Please guide me as to how to proceed with this.Thanks in advance!
Hello folks!I'm have some trouble with a dynamic update system! What I want to do: 1. I want to send in the code behind from any .aspx file values and parameters to a .vb class2. Code (code behind in .aspx file):1 gridupdate.updategrid("tblFeedbackA", e.CommandArgument, "locked=@locked", "@locked", "int", "0") 'Call de .vb class2 getgridfeedback() 3. Code (code in .vb class)1 Public Shared Function updategrid(ByVal tblnaam As String, ByVal x As Integer, ByVal sql As String, ByVal parameters As String, ByVal sqltype As String, ByVal waarden As String 'translation: values) 2 Dim dynstr As String() 3 Dim dynstr2 As String() 4 Dim re As New Regex(",") 5 dynstr = re.Split(parameters) 6 Dim i As Integer 7 dynstr2 = re.Split(sqltype) 8 Dim dynstr3 As String() 9 dynstr3 = re.Split(waarden) 10 11 Dim sqlconn As New SqlConnection(ConfigurationManager.ConnectionStrings("DataBase").ConnectionString) 12 Dim sql2 As String = "UPDATE " & HttpContext.Current.Session("prefix").ToString & tblnaam & " set " & sql & " where id=" & x 13 Dim sqlcmd As New SqlCommand(sql2, sqlconn) 14 sqlconn.Open() 15 16 For i = 0 To dynstr.Length - 1 17 sqlcmd.Parameters.Add(dynstr(i), CType("sqldbtype." & dynstr2(i), System.Data.SqlDbType)) 18 sqlcmd.Parameters(dynstr(i)).Value = dynstr3(i) 19 Next 20 sqlcmd.ExecuteNonQuery() 21 sqlconn.Close() 22 Return Nothing 23 End FunctionAll I want to do is to add the sqlparameters dynamically, but I don't find a way to do this :).Can you help me out?Thanks!
I am currently working with C and SQL Server 2012. My requirement is to Bulk fetch the records and Insert/Update the same in the other table with some business logic? How do i do this?
In Dot net 2.0 we change using SQLDataSource to Conect with SQLDB. Now for My case ,the Select SQL is dynamic when differnece user and parameters to the page, So if I want to Update the data input by user,then I must give Update/insert/delelte SQL to SQLDatasource's InsertCommand /UpdateCommand/DeleteCommand . How to Generate the Insert/update/delete command for the SQLDataSource ? as in dot net 1.1 can use SQLCommandBuilder to generate it,but SQLCommandBuilder just support DataAdeptor not for SQLDataSource, Could any body know how to do it when the SelectCommand is dynamic and need to update data back to DB after edit? thanks a lot.
Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?
For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...
ALTER PROCEDURE u_sp_x @semester int AS Select Semester@semester From ThisTable
