Hi!
I'm new in SQL SERVER 2000 and i'm looking for some kind of predefined dummy table. You know something like the "dual"-Table in Oracle. I want to use it for queries like that:
Select 1, 'auto' from dummy_table
Does anyone know, if something like that in SQL SERVER 2000 exists?
Nick
Years ago, I remember while doing maintenance on a stored procedure seeing a 'Select x, y, z Where 'some value' = 1.
The function of this, I believe was to make the select work but not retrieve any actual values.
I am attempting to use this in an 'Insert Into Select values From' statement. This insert uses multiple selects via unions and I need a final dummy Select statement with no Where criteria.
What I am thinking may not even apply to what I need to do here.
If you recognize something even remotely near what I am trying to get across I would appreciate your sending me the code.
Another solution for me is just inserting one row with a final RecId = 6 and ' ' or 0 values for the other fields into a table
but I was hoping this would work.
Example:
Insert Into table
Select
1 as RecId,
' ' as field1,
field2
From test1
Where field2 = 'CA'
Union
Select
2 as RecId,
' ' as field1,
field2
From test1
Where field2 = 'NJ'
Union
/*Final Select */
Select
6 as RecId,
' ' as field1,
field2
From test1
Where 'some value' = 1'
Thanks much for your assistance!!!
TADEG
Hi, Is there a dummy table in SQL Server just like DUAL in Oracle?
Thanks..
I have the following:
TicketID_1 AuditRec1
TicketID_1 AuditRec2
TicketID_1 AuditRec3
TicketID_1 AuditRec4
TicketID_1 AuditRec5
TicketID_2 AuditRec1
TicketID_2 AuditRec2
TicketID_2 AuditRec3
TicketID_2 AuditRec4
TicketID_2 AuditRec5
I need to insert a dummy row as the first row in this AuditRecord table for every occurrance of a given TicketID so that I get the following:
Dummy_Tick1 Dummy_AuditRec
TicketID_1 AuditRec1
TicketID_1 AuditRec2
TicketID_1 AuditRec3
TicketID_1 AuditRec4
TicketID_1 AuditRec5
Dummy_Tick2 Dummy_AuditRec
TicketID_2 AuditRec1
TicketID_2 AuditRec2
TicketID_2 AuditRec3
TicketID_2 AuditRec4
TicketID_2 AuditRec5
The AuditRec table is huge. How can I accomplish this using SQL?
Thank you to the two persons who took time to respond to my question.
I have to tell you - I know absolutely NOTHING about SQL.
Can you please tell me in language a two-year-old could understand, "What is SQL?"
Hello,
For sure that this is a dummy question but can you explain me how SQL Server reacts to the fact that we have the same application running in two different pc's and this two applications intent to insert,update, delete or even read a record from the same table
Does "he" blocks the record until one of them leave the table ??? And if the other tries to update at the same time ???
Thanks,
Paulo
It seems every month when I'm diluged by lots of data I fall back on olereliable, Excel. But, I get over 50,000 rows of data to scrub. A colleagueof mine suggested I use a database. Seems simple so far, but having dabbledin Access, it has always not so intuitive to understand. My question istwo-fold, is SQL a database. I believe its the language of some otherdatabase. If I'm corrent on the later, what database(s) use SQL?Tony
View 1 Replies View RelatedGuys - scenario/DDL/DML below
Create table #Periods (Period INT , Frequency INT , startdate datetime NULL , enddate datetime NULL )
insert #Periods (Period , Frequency , startdate , enddate)
select 0 , 3 , '01-Nov-2004' , '30-Nov-2004'
union all
select 1 , 3 , '01-Dec-2004' , '31-Dec-2004'
union all
select 2 , 3 , '01-Jan-2005' , '31-Jan-2005'
union all
select 2 , 6 , '01-Nov-2004' , '30-Nov-2004'
union all
select 3 , 6 , '01-Dec-2004' , '31-Dec-2004'
union all
select 4 , 6 , '01-Jan-2005' , '31-Jan-2005'
select * from #periods
Period Frequency startdate enddate
03 2004-11-01 00:00:00.0002004-11-30 00:00:00.000
13 2004-12-01 00:00:00.0002004-12-31 00:00:00.000
23 2005-01-01 00:00:00.0002005-01-31 00:00:00.000
26 2004-11-01 00:00:00.0002004-11-30 00:00:00.000
36 2004-12-01 00:00:00.0002004-12-31 00:00:00.000
46 2005-01-01 00:00:00.0002005-01-31 00:00:00.000
For any frequency (in this simple example 6) where the 'lowest' period is not 0, I need to create dummy rows
so here I need to create the following
Period Frequency startdate enddate
06 NULL NULL
16 NULL NULL
I've built a temp table to identify the min period for each Frequency but am not sure where to go from here using this to do the inserts ?
select
min(Period) as MinPeriod ,
ResetFrequency
into #Periods2
from #Periods
group by ResetFrequency
select * from #CashFlow2
MinPeriodResetFrequency
03
16
Hello,I am working with a vb6 Windows application that runs on Tablet PC's.There are about five SQL Server 2000 databases that tie into theapplication. SQL Server MSDE runs on the tablets, and currently thereis only one instance running. There are efforts underway to upgrade theapplication to a .NET SmartClient.One enhancement that needs to be made to the current application willbe to provide a 'training mode' in the application for the users. Theuser will use the same application, but will work with 'dummy' datainstead of live data when in training mode.So far as I can tell, the easiest way to accomodate this would be tohave a second instance of MSDE with 'copies' of all the databasesrunning, but with the 'dummy' data contained in the databases copies.The application would have some mechanism for switching to 'trainingmode', and maybe by some change of a connection string, the dummy datawould be presented in the application.So aside from accomodating a 'training mode' feature in theapplication, the application would remain the same, and the databaseschema for the databases would remain the same. Only the data wouldchange for 'training mode'.I'm looking for some ideas on how to approach this. This application isused by five thousand field agents, and so we can't support thisimplementation on a case by case basis. So my thought is that thesolution needs to have 'few moving parts'. If we can do this and getaway from having two instances of MSDE, that would be great. I have nottried to set up identical databases in different directories on thesame instance (I figure that would cause a problem in a system table ortwo someplace, but maybe not), but its one thing I want to investigate.If you have any ideas on this, please share!Thank you for your help!CSDunn
View 1 Replies View RelatedI've written code that dynamically builds an sql query based onvarious constraint possibilities.The problem is the code would have been very complex had I not come upwith a dummy constraint as a kind of place holder in the statement.To avoid complex logic that determines if there was another constraintbefore any other constraint and hence the need to add, say, AND ornot, I came up with a dummy constraint so that every subsequentconstraint will begin with AND. There's no need to determine whetherto add AND or not. This makes the coding much simpler because allthat needs to be done is ask if a certain condition exists then addthe constraint along with AND in front every time.So what I did was create the statement like this:SELECT elapsed_time AS ET from Table1 WHERE 1 > 0 AND 1stConstraintAND 2nd Constraint and so on.See if the 1 > 0 condition were not there it would be necessary tofirst determine the first actual contraint and not add AND in front ofit and then add the rest with ANDs in front of every one.I should add that the user does not have to select any constraint andthat's the problem. I need to stick that WHERE 1>0 in there so thatthere doesn't need to be a determination of which other, if any,constraints are selected.Even if my explanation above is not well understood, believe me thefront-end coding is much easier this way.My question is does the 1 > 0 conditional check present the databasewith any significant overhead or as far was dummy constraints go isthis as good as any other?-David
View 4 Replies View Related No one answered this question, how do people normally insert dummy data into tables? I mean, where does the data typically come from? what process is used to insert the data? Note: This is for testing purposes, eventually to be tested in a Web-based front-end which I know nothing about.
thx,
Kat
Hello,
I have 4 tables: Folders, Files, Tags and FilesTags.
I need to insert some dummy data for testing as follows:
1. Insert 100 records in Tags tables.
Give Tag 01, Tag 02, Tag 03, ... to Tag names.
For this I have the following:
declare @i integer
select @i = 1
while @i <= 100
begin
insert into Tags ([text])
select 'Tag ' + right('000' + convert(varchar(3), @i), 3)
select @i = @i + 1
end
What I am missing is the following:
2. Insert 10 records in Folders tables.
Each folder should be filled as follows:
FolderID = New Guid
Name = "Name" + N (1, 2, 3, ...)
CreatedDate = Current Date
3. For each Folder I want to insert a random number of Files.
It can be added 0 to 10 Files.
Each file should be filled as follows:
FileID = New Guid
FolderID = Folder ID
Description = "Description" + Folder Number + File Number
4. For each file added associate it with a random number of Tags.
It can be 0 to 10 Tags and it should be done as follows:
Insert record in FilesTags where:
TagID = Random TagID taken from table Tags
That has still not be used in current File
FileID = Current FileID
Could someone, please, help me doing this?
My tables are as follows:
create table dbo.Tags
(
TagID uniqueidentifier not null
constraint PK_Tag primary key clustered,
[Name] nvarchar(200) not null
)
create table dbo.Folders
(
FolderID uniqueidentifier not null
constraint PK_Folder primary key clustered,
CreatedDate datetime not null,
[Name] nvarchar(200) null
)
create table dbo.Files
(
FileID uniqueidentifier not null
constraint PK_File primary key clustered,
FolderID uniqueidentifier not null,
Description nvarchar(2000) null,
constraint FK_Files_Folders
foreign key(FolderID)
references dbo.Folders(FolderID)
on delete cascade,
)
create table dbo.FilesTags
(
FileID uniqueidentifier not null,
TagID uniqueidentifier not null,
constraint PK_FilesTags
primary key clustered (FileID, TagID),
constraint FK_FilesTags_Files
foreign key(FileID)
references dbo.Files(FileID)
on delete cascade,
constraint FK_FilesTags_Tags
foreign key(TagID)
references dbo.Tags(TagID)
)
Thank You,
Miguel
Hi AllCan you please help me with a few queries on adding a header line andpadding rows out.I apologise profusely for not providing the DDL for this, but I don't haveit. All I have is stored procedure that I'm trying to edit to make thefront-end app display the right data.The relevant part of the stored procedure that I'm working on is as follow:Declare StockHelpCursor Scroll Cursor ForSelect s.StockID,ISNULL(sd.ShortDescription, s.StockID) +space(30-len(ISNULL(sd.ShortDescription,s.StockID))) +pl.name +space(10-len(str(pl.name,10,3))) +sp.currencyid + str(sp.sellingprice,10,3) +space(10-len(str(sp.sellingprice,10,3))) +str(sq.quantityinstock)From Stock s, StockDescriptions sd, StockQuantities sq,StockPrices sp, PriceLevels plWhere (s.StockID Like @theID) And(ISNULL(sd.ShortDescription, sd.StockID) Like @theName) And(s.StockID=sd.StockID) And(s.StockID=sq.StockID) And(s.StockID=sp.StockID) And(sp.PriceLevelID=pl.PriceLevelID) And(sd.LanguageID=@theLanguageID) And(sp.CurrencyID=@theCurrencyID)Order By s.StockIDOpen StockHelpCursorPLEASE NOTE: this query works fine apart from the following problems:1) Creating a header line - I need to insert a header line to this cursorfor the field headers, as the app is stripping off this header. I wasthinking of creating a var, sticking it in-between the Declare and theSelect part and inserting these field headers, but I don't know how to dothis. Any ideas?2) Padding the results - As you can see from the script, I have tried to padout the above fields as the app's output window is basically a textbox, butthey just don't line up.NOTE: I'm using the 3 in the str(xxx,10,3) bit to make my numbers show at 3decimal places.Could you please give me some pointers on how I can pad these out correctly.ThanksRobbie
View 7 Replies View RelatedHi.
Does anyone know if SQLServer 7.0 will generate dummy data for specific columns in tables?
TIA,
Jeff
In 2000, BCP seemed the way to go. DTS packages would also work. My question is, in 2005, what is the best choice? I seem to remember that BCP ignored all referential integrity constraints, and applying them afterwords was a royal pain. I'm not a BCP expert by any means. Running this at the command line means using the DOS prompt correct?
What is 2005's answer to this?
I have matrix with 3 row groupings
Parent, Child, Baby
I have a value in the data cell and also a dummy column grouping to show some % values.
The % is basically what the row subtotal represents as a % of all the data in the dataset.
The Parent row group also has a subtotal, so the % here will show all values within the parent as a % of all data.
I have interactive sorting on Parent, Child and Baby.
but what I want to do is this:
Interactive sorting of the parent-subtotal-percent column values to cause a physical reordering of the parents.
i.e. show me the parents in order of their "% as a total of the entire dataset"
Possible?
I have the following attributes in this Table A.
1) Location_ID (int)
2) Serial_Number (nvarchar(Max))
3) KeyID (nvarchar(max)
4) Reference_Address (nvarchar(max)
5) SourceTime (datetime)
6) SourceValue (nvarchar)
I am trying to create 1000000 dummy records in this this table A.How do i go about do it? I would like my data to be something like this
LOCATION_ID
1
Serial Number
SN-01
KeyID
E1210
Reference_Address
83
SourceTime
2015-05-21 00:00:00 000
SourceValue
6200
I want to create a raw SQL resultset for outputting to Excel with some artificial headers transposed over the top of the 2nd part of the Union's column names. The first part of the Union will be the Headers. Like this, the space to the left of the topmost columns is preferably empty ....
COL 1    COL2     COL3      COL4 etc.                        BEH                            BIG       BPL etc.
*************************************Â Â INTAKTÂ Â Â Â Â Â DEFEKTÂ Â Â Â Â Â Â INTAKT DEFEKTÂ Â Â Â Â Â Â Â Â INTAKT
*************************************Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â EÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â EÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â B
I just want the text above as a 3 line header and there won't be any values obviously. Then the 2nd query will be joined to the above with a Union all. The 2nd query has all the same column names as what will be given in the first set above. What is the SQL Syntax for doing so? Do I have to use a from clause?
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.
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?
Hi
I am having a table called as status ,in that table one field is there i.e. currentstatus.
the rows which are having currentstatus as "ticket closed",i want to move those rows into other table called repository which is having same table structure as status table.
I can do programatically.
but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
Please help me.
Thanks.
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies View RelatedHi all,
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing:
dbo.Categories
dbo.CustomerCustomerDemo
dbo.CustomerDemographics
dbo.Customers
dbo.Employees
dbo.EmployeeTerritories
dbo.Order Details
dbo.Orders
dbo.Products
dbo.Regions
dbo.Shippers
dbo.Suppliers
dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing:
dbo.Categories
New Table...
Modify
Open Table
Script Table as |> CREATYE To |>
DROP To |>
SELECT To |>
INSERT To |> New Query Editor Window
File....
Clipboard
UPDATE To |>
DELETE to |>
From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance,
Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies View Related'****************************************************************************
Cmd.CommandText = "Drop Table Raj"
Cmd.ExecuteNonQuery()
Cmd.CommandText = "Select * Into Raj From XXX"
Cmd.ExecuteNonQuery()
'**************************************************************************
This generates error that Table already exist.
If Wait 1 sec then execute statement then it works fine.
Thanks in Advance
Piyush Verma
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.
View 2 Replies View RelatedHi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
Thanks
In a table-valued UDF, does the UDF use a table variable or a temp table to form the resultset returned?
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp
both works fine , then what is the difference
waiting for ur reply
Banti"
I am having a problem in creating query for this exciting scenario.
Table A
ID ItemQtyCreatedDatetime
W001 CB112014-06-03 20:30:48.000
W002 CB112014-06-04 01:30:48.000
Table B
IDItemQtyCreatedDatetime
A001 CB112014-06-03 19:05:48.000
A002 CB112014-06-03 20:05:48.000
A003 CB112014-06-03 21:05:48.000
A004 CB112014-06-04 01:05:48.000
A005 CB112014-06-04 02:05:48.000
I would like to return the nearest date of Table B in my table like for
ID W001 in table B should return ID A002 CreatedDatetime: 2014-06-03 20:05:48.000
ID W002 in table B should return ID A004 CreatedDatetime: 2014-06-04 01:05:48.000
Is there any Posibility to change a User Table to System Table.
How to create one system table.
I am in Big mess that One of the Table I am using is in System Type.
I cant Index the same. Is there any Mistake we can change a user table to system table.....