SELECT
'INSERT into temp values(data1, data2, data3)' +
'SELECT ' +
CONVERT(VARCHAR(20), temp2.data1) + ', ' + CONVERT(VARCHAR(20), temp2.data2) + ', ' + '''' + CONVERT(VARCHAR(20), temp2.data3) + ''''
FROM temp, temp2
order by temp2.data1
now my table temp and temp2 both has folowing fields and data type
data1 int
data2 int
data3 varchar(20)
befor execution i have 0 records in temp and 10 records in temp2.
this scripts supposed to add all 10 records from temp2 to temp but it does nothing. evevn it is not giving error too.
hi, i'm quite new to sql and would like to ask if there is any way to automatically insert records from a table, for example dailyTransaction, into another table, for example transactionHistory, daily maybe around midnight or something. would also have to delete the records in dailyTransaction after it has been copied over to transactionHistory table i've done a bit of searching around and i think i have to use job to do this but i have no idea how to implement it. looked through the libary in msdn but i'm still not too sure. would really appreciate any help. thanks
Hi, Does anyone have any sugestions as to the best way to achieve the following? I want to display a list of products in a GridView. Against each product I want to place a button which will add the product to a "wish list" table. I need to be able to check that the product has not already been inserted into the wish list. Either suggested methods or links to tutorials will be appreciated. Thanks in advance.
I am having a problem in adding new records in SQL 7.0 Database. I have two databases - one with table name DB1..WTEST and the other with table name DB2..TEST. I want to update records in DB2..TEST table when records are already in TEST table (by comparing primary key - with ignore duplicate option) & secondly, INSERT records in DB2..TEST table from DB1..WTEST table if the records do not exist in DB2..TEST table. The first option which is Updation, works fine but with second option I have problem. It works and records are INSERTED but I don't know the statistics of inserted records. Instead it gives a warning message - Duplicate records were Ignored -
Can somebody help me out or suggest some better solution for conditional record insertion. I used CURSOR option but it is only for Updation & Deletion.
IF EXISTS (SELECT * FROM DB1..WTEST INNER JOIN DB2..TEST ON DB1..WTEST.Name1 = DB2..TEST.Name1) BEGIN UPDATE DB1..TEST SET add1 = DB1..WTEST.Add1 FROM DB1..WTEST Print 'Updated' END
WHILE NOT EXISTS(SELECT * FROM DB1..WTEST INNER JOIN DB2..TEST ON DB1..WTEST.Name1 = DB2..TEST.Name1) BEGIN INSERT DB2..TEST SELECT DB1..WTEST.Name1, DB1..WTEST.Add1 FROM DB1..WTEST CONTINUE END
------------------ Alternate Script --------------------- IF EXISTS (SELECT * FROM DB1..WTEST, DB2..TEST WHERE DB1..WTEST.Name1 = DB2..TEST.Name1) BEGIN UPDATE DB2..TEST SET add1 = DB1..WTEST.Add1 FROM DB1..WTEST WHERE DB1..WTEST.Name1 = DB2..TEST.Name1 END
INSERT DB2..TEST SELECT DB1..WTEST.Name1, DB1..WTEST.Add1 FROM DB1..WTEST WHERE NOT EXISTS(SELECT * FROM DB2..TEST WHERE DB1..WTEST.Name1 = DB2..TEST.Name1) END
---------------------------------------------------------------------------- ------------ Ibrar Ahmed System Projects Controller Olayan Saudi Holding Company Ph:- +966-3-8871000 x 1122 Fax:- +966-3-8872000 E-Mail: i.ahmed@oshco.com
Does anyone know an effective way to capture any records that failed toinsert into a table when inserting from an xml document in SQL Svr2000?thanks.-ak
I need to create a temp table with an identity column based on the contents of a physical table (CONTACTS) that has no key or unique way to identify which record was inserted first,
I can query CONTACTS with no ORDER clause and everything is in the order it was inserted. However, when I create the temp table with a newid() as ID or IDENTITY(1,1) as ID, the duplicate rows as they existsed in the physical table are not inserted into the temp table in the same order.
Is it possible to select records from a physical table into a temp table in the same order while creating a unique field?
I've tried this many different ways using variations of ORDER BY/ no ORDER BY. I've tried creating the temp table first with the IDENTITY field, then insert into it. I've also tried SELECT INTO syntax while creating the IDENTITY field with the select. I've done this with newid() as well.
Hi, I have two tables A and B. In table A i have three columns called empid, empname and empsalary where empid is an identity column. Table A has some records filled in it. Table B has the same schema except the fact that the empid is not an identity column in table B. Table B does not contain any rows initially. All other aspects remain the same as that of table A. Now i am going to delete some records in table A based on the empid. When i delete the records in table A based on empid the deleted records should be inserted into table B with the same empid. I need to accomplish these two tasks in a single stored procedure. How to do it? I need the entire code for the stored procedure. Please help me. I am trying for the past 4 days. Thanx in Advance
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
BEGIN DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' ) END BEGIN INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22' END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
BEGIN DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' ) END BEGIN INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22' END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
I am using vs2005 and sqlserver2000 My problem is i have 5 checkboxes and some textboxes.In this user selects the checkbox and textboxes dynamically .In this user selects one or two or three or when user selects header checkbox then all checkboxes are selected.And in my database i mentioned a single column for group of checkboes.So how should i insert the data into database
dim con as new sqlconnection("userid=sa;pwd=; initial catalog=test") dim cmd as sql command
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click cmd.CommandText = "insert into check values(" what should i write here ....'" & text.text &"','" @ text2.text "'... ") con.open() cmd.executenonquery() con.close()
Hi, I am working on an application that is to read a large number of XML files, take out specific values from each file, and store these in a SQL server so that reports can be generated from these values. There are some 15-20,000 files for each month of the year. I am OK with parsing the files and getting the fields that I need but I don't want to insert one record at a time as I parse the files. I was told that I can create a .exe file that parses the xml files and stores the required values in a csv file and use these csv files to initiate a bulk insert, using Business Intelligence Studio. I have not been able to find any info or article on how to do this. Any help on how I can accomplish this, or alternate solutions is greatly appreciated.
Hi Experts , I want to insert datas randomly to all columns of a table.The columns have different datatypes like int,varchar,datetime. What to do??? It is urgent so please help.
Hi Experts , I want to insert datas randomly to all columns of a table.The columns have different datatypes like int,varchar,datetime. How to create a procedure for the same??? please help.
ALTER PROCEDURE [dbo].[usp_World_Ins_data] -- Add the parameters for the stored procedure here @UserName nvarchar(50), @Password nvarchar(15), @Email nvarchar(100), @IsChecked bit, @ReceiveNews bit
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT UserName FROM World_Registration WHERE UserName=@UserName) RAISERROR('Username exists',16,1) ELSE IF EXISTS(SELECT Email FROM World_Registration WHERE Email=@Email) RAISERROR('Email exists',16,1) ELSE INSERT INTO World_Registration(UserName,Password,Email,IsChecked,ReceiveNews) VALUES(@UserName,@Password,@Email,@IsChecked,@ReceiveNews)
END
if executed once it works properly like [usp_World_Ins_data] 'aa','aa','ravinderpal@netbiz.in','1','1'
while if i m trying to execute stored procedure like this three times i mean more than one time [usp_World_Ins_data] 'aa','aa','ravinderpal@netbiz.in','1','1' [usp_World_Ins_data] 'aa','aa','ravinderpal@netbiz.in','1','1' [usp_World_Ins_data] 'aa','aa','ravinderpal@netbiz.in','1','1'
it gives error: Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near 'usp_World_Ins_data'.
i am new to sql server i want to insert five rows continously in sql server database. my problem is like this . i am inserting user datails. i want to get the userid which is an identity column.with the userid i want to insert userid and roleid in another table.Roleid values 1 to 5. ie constant. an sql procedure is required.
Hi everyone,I tried to upload my database to my web host. Nevertheless as they donot give me enough permission to perform some DTS thus I have togenerate script to do it myself. The problem is MSSQL scriptgeneration operation can only generate the definitions of the objects,no data insertion sql is created.So is there any method to create insertion script for all the tablesin my database (they have automated increment key as well)Hope for your response!Thank you
I have a table called ACCOUNTS which has 60,000 rows already in it. It has a columns called PolicyNo and PremiumBasisCode. PolicyNo has data inside it whereas Premiumbasiscode is NULL.
Now I have to insert the data inside the PremiumBasisCode Column from Table1 into ACCOUNTS table by joining them on Policy No (Inner Join).
One thing to remember, there are already 60,000 rows for which this insert needs to take place. What will be the best approach.
I am working in ASP.Net2.0 and Sql server 2005.Using asp button(FormView) I am trying to insert the data from the form into the database and at the same time want to move on to another page indicating the succes of insertion.My code: <asp:Button ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"Text="Submit" PostBackUrl="~/ClinicDownload.aspx"></asp:Button> It is moving on to another page, but no insertion is taking place. If I don't give the PostBackUrl then I can insert. I want to do both in one click....Hope someone can help me. Regardspreeshma
Hi there, Is there a way to retrieve the set of primary keys from an inserted record, especially when the primary keys are generated upon insertion (ex. identity, uniqueid)? Basically I need to get the ID of the record I just inserted so that I can use it as a reference to that record in other places. I'm using MS SQL and raw SQL commands (ie. no database interfacing objects). I'm hoping I can tag some sort of SELECT statement on the end of the INSERT command that will give me the results that I need. Thanks!
I am using BLL for inserting my data. I have some date filed and I have declare that field in BLL as date only. But still it is giving me error string in wrong format. My database is in SQL Server2005
Hi Friends,I have 3 labels Steet,City,Pincode and 3 textboxes related to the labels and one button as nae 'Address'I gave the data for Street:abc,City:xyz,Pincode:123 and have to insert into the table.I created one table in the database with table name Adreess and column address varchar(100)but after giving the values in the textboxes and clicked on the button its throwing the exception i.e System.Data.SqlClient.SqlException: The name "abcxyz123" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.I wrote the code like following protected void Button1_Click(object sender, EventArgs e) { string street = txtStreetNo.Text; string city = txtCity.Text; string pincode = txtPincode.Text; string com = street + city+pincode; conn.Open(); SqlDataAdapter daInsert = new SqlDataAdapter("insert into Address values(" + com.ToString() + ")", conn); daInsert.SelectCommand.ExecuteNonQuery();--->here its giving the exception conn.Close(); Response.Write("the values are inserted"); }Please any one tell me am I did the code write or not if its not please give any suggetionsthanksGeeta
I would like to prepare a string variable for SQL insertion. Like, replacing all the single quotes with the HTML code equivilent and such. Has anyone written alything like this? Is there a tutorial on the web for it? A control maybe?
I'm having a small problem inserting japanese text into a ms sql database. All the fields are of type nvarchar and in my insert statement I use N'the value of the string' to make sure it insert unicode text.
Without the N I get only '??????' in my cells and with the N I see only empty squares.
Anyone have any solution how I can insert japanese text into mssql ?
I need to insert some registers in a table. These registers have three fileds: field1, field2 and field3. I don't want to insert a register if in the table already exists a row with the field2 and field3 combination of that register. And I don't want to declare these fields as key fields. How can I filter a "destination" table by two fields? I'll appreciate your answer
hello i want to ask if the insertion of a record into a partion is slower than insertion it into a non partitioned table or not? cuz sql has to decide to wich partion the record has to insert according to the partitioning key and is this decesion process is making insertion slower ?
I want XML data to be inserted int SQL table but could not figure out. #Currency is my table with assocaite columns and @XMLCurrency is a variable which holds XML string. How can I insert this XML data to my table.
Create table #Currency (CurrencyId int ,ISOCode nvarchar(10),ISONumbricCOde int,ISOName nvarchar(50), IsEnabledForMPV int default 0) Declare @XMLCurrency nvarchar(max) Set @XMLCurrency='<R><T><A>0</A><B>USD</B><C>840</C><D>US Dollar</D></T></R>'
Value 840 should insert into column ISONumbricCOde . value USD should be insert into ISOCode column. value 0 should insert into column CurrencyId. values US Dollar should insert into column ISOName .
i have 4 tables, each consist of app. 10000000 rows.They have same columns (fTime[datetime] and bid[money]).What i wanna do is to collect all of datas into one of the tables, in ascending order by fTime.
