Hi all!
I want to fill a table with random values. In each line should be a different value - "independet" from the value the row above.
what i tried didnt work, it always produced the same value.
Maybe you have an idea
Thankx in advance and greetings from Vienna
Landau
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'
IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn
-- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet
USE [Patients]
GO
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[PID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
do I get
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
for the following;
Code Snippet
INSERT INTO Patients
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');
Thank you,
hazz
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
Here is my table:
My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?
Is there a way to write an SQL statement to choose random values from a particular select statement..for example:
select * from address
I was thinking, if there was a way to use an include statement for it like:
select * from address where id IN ('generates some random values')
thanks in advance
Hello guys , I am new here,
Well, I am moving my greetings script from MS Access to MS SQL, here is what I was using in MS Access.
See this screenshot: Click Here
Well, I've imported the data from access database, but I don't know how to do the AutoNumber in MS SQL,
Also I don't know how can I add that Random thing *See the arrow in the screenshot*
Thanks in advance
I have a table with 3 fields SiteID (PK), SiteCode, SiteName.
For each SiteCode I have several rows with different SiteName.
For example: 1 327 a
2 327 b
3 327 c
4 328 aa
5 328 bb......
I need to retrieve each time SiteCode with random SiteName.
For example: 327 b , 327 a , 327 c
328 aa 328 bb 328 aa.
I need it very urgent, so any ideas will be very helpful. Thanks to all,
Hello,
i want to anonymous data by multiplicating with a random generated value. After a little bit research i know that is only solvable with a script task, but i dont have seen a working example script. Does anybody knows where i can found a little scriptlet or post something? Thank you!
Jonas
I need Insert rows in the OrderDetails Table based on values in the Orders Table
In the Orders table i have a columns called OrderID and ISale.
In the OrdersDetails i have columns called OrderID and SaleType
For each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 1, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value K and another row with the value KD.
That is a row will be added and the value in the SalesType column will be K, also a second row will be added and the value in the SalesType column will be KD
Also for each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 0, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value Q and another row with the value QD
That is a row will be added and the value in the SalesType column will be Q, also a second row will be added and the value in the SalesType column will be QD.
I need a SQL Script to accomplish this. thanks
I have a table as dbo.Bewegungen and it will be updated every day with new dates
I inserted once the values of this table to another table with group by. I want to know how should i insert the new values every day to the new table? i don't want to insert the previous values again.
it has 5 columns as primary key and one of them is Date.
Hi,I like to have a SQL script which could update a table to set one attributeto a random value picked from a given list. The prototyping code is asbelow:select @value_list = ('John', 'David', 'Mathew', 'Paul')....update EMP set emp_name = @random_namewhere ...where random name is randomly got from the given list. Of course there willbe a cursor so that different row may have different random name but notnecessary unique. Also the attribute and the list could be any valid commonSQL data typesThanks!Ximing
View 1 Replies View RelatedHello,I have 3 tableTable 1 : list of "whatever" programTable 2: list of tasks for each programTable 3: list of user for each taskWhen I have a new program, I want to select existing task and copy them and assign them to my new program. But I also want to copy the list of user of each task.Is there a way to do that in sql?I do not really want to go through each single task, then copy it with the new program, then get the @@identity of the inserted task and then assign the same user to the newly inserted task.Thanks
View 3 Replies View RelatedWhat is the easiest way to insert a new row in a existing sql table through web developer.net (visual basic)?
E.g. a database called Names and the columns Firstname and Surname and you want to insert "anna","johnsson"?
thank you very much.
Hi, I want to INSERT INTO [Table] ([Field]) VALUES('I Have a ' in value')
please teach me how to
xxx
I have a table (tblCustomer) with three fields (customer_id, s_id, s_string)
I want to fill in this table with two fixed values ??and customer_id from another table.
Every customer that starts at P in tblMainCustomer.customer_nr should be entered in table tblCustomer.
Select customer_id from tblMainCustomer where customer_nr like 'P%'
Customer_id taken from tblMainCustomer and s_id, s_string these fixed values ??that are equal for each customer.
These fixed values ??are:
100-----Gold
101-----Steel
1002----Super Copper
Example: If I have a client who has has a customer_id 45 so it will be like this in tblCustomer:
customer_id----s_id----s_string
-------------------------------
45-------------100-----Gold
45-------------101-----Steel
45-------------102-----Super Copper
I am stuck, how do I do this the best way?
Hi all:
I have a trouble getting the value for a field from the insert table, well, this is the problem:
I have to make a string from the insert command over the table, e.g.: 'insert into demo(campo) values('hello')'
this string have 2 parts: a) 'insert into demo(campo) values(' ---> I already have it , but the second, the core of the problem, I can't get it
this is my code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Marco Antonio García León
-- Create date: 28.04.2008
-- Description: Trigger para INSERT
-- =============================================
ALTER TRIGGER [ti_Prueba]
ON [dbo].[PRUEBA] FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @MyCursor CURSOR --Cursor donde se aloja la estructura
DECLARE @cmd nVARCHAR(1000) --Comando
DECLARE @Cadena nVARCHAR(4000) --Cadena SQL a ejecutar
DECLARE @Campo AS nVarchar(1024) --Nombre del Campo
DECLARE @Tipo AS Integer --Tipo del campo
DECLARE @Valor AS nvarchar(1024) --Valor del campo
DECLARE @Cadena_Valor AS varchar(1000)
DECLARE NewRow CURSOR FOR
SELECT * FROM Inserted
DECLARE cursorStruc CURSOR FOR
SELECT SysColumns.Name, SysColumns.xType
FROM SysObjects JOIN
SysColumns ON(SysObjects.ID=SysColumns.ID)
WHERE SysObjects.Name LIKE 'prueba'
SET @MyCursor = cursorStruc
SET @Cadena = 'INSERT INTO Prueba('
SET @Cadena_Valor = 'VALORES: '
print 'Cargando la data al temporal';
select * into ##tempo from inserted;
-- Insert statements for trigger here
OPEN cursorStruc
-- Perform the first fetch.
FETCH NEXT FROM cursorStruc INTO @Campo, @Tipo
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--SET @cCadena = (select cursorStruc.Name from cursorStruc)
--SET @cmd = 'SET @Valor="' + @Campo + '" FROM Inserted;';
--SET @cmd = 'DECLARE @Valor VARCHAR(1000); ' +
-- 'SELECT @Valor="' + @Campo + '" FROM #tempo;';
SET @cmd = 'DECLARE @Valor VARCHAR(1000); ' +
'SELECT @Valor=' + @Campo + ' FROM ##tempo;';
SET @Cadena = (@Cadena + @Campo + ', ');
SET @Cadena_Valor = (@Cadena_Valor + @Valor + ', ');
PRINT 'Cadena @cmd: ' + @cmd;
PRINT 'Campo: ' + @Campo;
PRINT 'Valor: ' + @Valor;
--EXECUTE sp_executesql @cmd; --, N'@campo';
EXEC sp_executesql @cmd, @campo, @Valor OUTPUT;
PRINT 'Valor: ' + @Valor;
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM cursorStruc INTO @Campo, @Tipo
END
SET @Cadena = SUBSTRING(@Cadena, 1, LEN(@Cadena)-1) + ') VALUES('
PRINT '@Cadena: ' + @Cadena
PRINT '@Cadena_Valor: ' + @Cadena_Valor
select * from ##tempo;
CLOSE cursorStruc
DEALLOCATE cursorStruc
END
Thanks a lot for your help
I am trying to insert values from a temp table #TableName to a db table.
CATEGORY table- CategoryId, CategoryName, CategoryDescription
Then I did this :
Select CategoryName
into #TableName
from CATEGORY
Now I'm doing this :
Insert into #TestTable values(1,'This is:'+CategoryName)
select CategoryName from #Test
CategoryID right now is not PK.
So, the intention is to insert as many CategoryNames available into TestTable with id 1 and category name edited as shown in Inset statement.
What is wrong with this query. Its not working.
Hi,
I have just started working with CLR Userdefined functions(SQL 2005),the below code shows I am inserting a row into
table through a function.
I dont know where am going wrong;but nuthing is happening.
I checked the connection also,in Server Explorer its getting connected with the data base.
**Please help me regarding this******
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static int EmpName()
{
using (SqlConnection conn = new SqlConnection("Context Connection=true"))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO dbo.Employee VALUES('MOHAN',66,22,'GDGDG',55)";
//SqlDataReader rec = new SqlDataReader();
//rec = cmd.ExecuteReader();
int rows = cmd.ExecuteNonQuery();
//string name = rec.GetString(0);
conn.Close();
return rows;
}
Hi I am using VS 2005 with SQL 2005.
I had a Datagrid and i Need to bind data into it.
I need to show Random Unique ID's based on the Parameter i pass.
I wrote select top 5 * from Employee order by NEWID()
for this i had created a Stored Procedure as
create Proc Demo(@N int)as beginselect top @N * from Employee order by NEWID()end
Its showing a syntax error near @N. How can i pass the parameter for Select Top @N * from .......
Hello, my problem is that I have 2 textboxes and when the user writes somthing and clicks the submit button I want these values to be stored in a database table. I am using the following code but nothing seems to hapen. Do I have a problem with the Query (Insert)??? Or do I miss something else. Please respond as I can't find this.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Manufacturer2.aspx.cs" Inherits="Manufacturer2" %><!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" OnClick="Button1_Click" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="INSERT INTO Manufacturer(Name, Password) VALUES (@TextBox1, @TextBox2)">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="TextBox1" PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox2" Name="TextBox2" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
</div></form>
</body></html>
Hi,
I've got a table with trialsubscriptions. When someone orders a trialsubscription he has to fill in a form. With the values in the form I like to fill a database with some extra fields from the trialsubscription table. I get those extra fields with a datareader. But when I insert I can't use the same datareader (its an insert), I can't make two datareaders because I have to close the first one etc.
Does someone had the same problem and has some example code (or make some :-)) Some keywords are also possible!
Thanks!
Roel
hello everyone ,
i have a table named "Employee" with EmpID as PK.
i want to insert EmpID one by one in another table named "AssignedComplaints"
so if i have all the EmpID inserted in "AssignedComplaints" table then on next insert operation , the first EmpID will be inserted and then second on so on.
like i gave u a sample
i have three EmpIDs in "Employee" table named M1,M2,M3
first M1 is inserted secondly M2 and lastly M3.
now i have all EmpID in "AssignedCompalints" table.
now if i do want to insert again then the whole process will repeat , first M1 will be inserted secondly M2 and so on.
i need the query
i have created a trigger and will use this query in that trigger.
thanks
Hello. I have a somwhat simple question.
I have a table inside my database where i have some columns. Id like to extract distinct values from one column and inser them into another. My table is named article and id like to slect all the destinct values from the column naned type inside my article table. Then i need to insert my values into a new table called type but i would also like the to have 2 columns inside my type table. 1 called counter witch is an auto increment PK, and another one named type where the results from my query would be inserted.
Iv tried a veriety of querys but none of them have managed to do this.
Could anyone help me construct this query?
Sp which inserts inforamtion into a table works fine. The trigger on that table which then inserts information into another table works fine. Only problem is that the SP will not return anything to Visual Basic.. Anyone know how to fix it?
SPbob
INsert into b values(1,1,2,2)
select 0
(this is a cut up version of the sp just to show about the way it is formated)
Please help.
Hi i am trying to create an insert statement that will insert rows into a table based on the information in the table already.
the table looks like this
Groupid field1 field2
-1 100 200
-1 100 300
-1 300 500
-1 300 600
-1 400 100
the insert looks like this
INSERT Into table1(groupid,field1,field2)
select -1,@passedvalue,field2
from table1
where field1 = @passedvalue1
assume @passedvalue = 700, @passwedvalue1 = 100
Now this is fine however i cannot have a duplicate key (key is comibantion of all 3 fields) thus the first time this runs it works however if it runs again it fails - how can i change the where clause to ignore rows that already exist?
eg if @passedvalue = 300 and passedvalue1 = 500
I am using the Database is Oracle SQL Developer, here the Requirement is like Before insert the values in Database I need to remove the Database table and insert new values.
DELETE FROM XXMBB_NOSVOS_TMP_VO_NO WHERE EFFECTIVE_DATE BETWEEN
'01'
|| '-'
|| TO_CHAR(to_date(:N_Date,'DD-MM-YY'),'MON-YY')
AND to_date(:N_Date,'DD-MM-YY')
AND LEDGER_ID = LED and name_rdf='NOSVOS';
COMMIT;
DELETE FROM XXMBB_NOSVOS_BAL_TMP_VO_NO WHERE
PERIOD_NAME = TO_CHAR(add_months(to_date(:N_Date,'DD-MM-YY'),-1),'MON-YY')
[code]....
Arif writes "I want to insert values in to a table which is having identity column, how? can you help me out from this problem.
Thanks in advance
Jamal"
Hi,
I've been working as web dev for quite sometime now but there are still few things that i would like to clarify. Hope you guys can shed lights.
I currently have several tables and all this tables are having some 1 to many relationships. I know that how i insert value into my tables are very inefficient. I do it in this manner..
-> Insert a value in TableA
-> Query the latest ID that i have inserted in Table A then
-> Insert this value in TableB. And so on and so forth.
I believe there is an efficient way to to do this but i'm not sure how? Could anyone shed me a light on this matter?
Your reply would be highly appreciated. TIA.
the error message I get is
{"Object reference not set to an instance of an object."}
and it points to < Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text >
this is my code":
Protected Sub TickMastBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles TickMastBtn.Click
REM Collect variablesDim Tickr As String = CType(FindControl("TickerTextbx"), TextBox).Text
Dim Comp As String = CType(FindControl("CoTextbx"), TextBox).TextDim Exch As String = CType(FindControl("ExchTextbx"), TextBox).Text
REM Create connection and command objectsDim cn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataVTRADE.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")Dim cmd As New SqlCommand
cmd.Connection = cn
REM Build our parameterized insert statementDim sql As New StringBuilder
sql.Append("INSERT INTO TickerMaster ")sql.Append("(Ticker,Company,Exchange,) ")sql.Append("VALUES (@Tickr,@Comp,@Exch,)")
cmd.CommandText = sql.ToString
REM Add parameter values to command
REM Parameters used to protect DB from SQL injection attacksWith cmd.Parameters
.Add("Tickr", SqlDbType.Int).Value = Tickr.Add("Comp", SqlDbType.VarChar).Value = Comp
.Add("Exch", SqlDbType.VarChar).Value = Exch
End With
REM Now execute the statement
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
End Sub
hi all,
In sql server 2005 i had created 2 tables,table 1 and table 2. Here is the detail of the table.
table 1:
tid--> int,identity,primary key
tname-->varchar(200)
table 2:
sid-->int,identity,primary key
tid-->fk (this tid is set as foreign key for the tid in table1)
now when i'm inserting values into tname i have to insert the value of tid from table 1 into the tid of table 2 both at the same time. any one know how this is possible? if so please send me the code..
pls help me..
thanks
swapna
Hello,i have a table and if a record is inserted i will test a numeric valuein this table. If the this value is greather as 1 million, than anstatus column should be changed from 'A' to 'B'.Yes and sorry, this is a newbie question.On Oracle this work simple:create trigger myTrigger on tableXasbeginif :old.x 100000 then:new.y:='B'end if;end;ThanksMaik
View 2 Replies View Related