Dummy Table In SQL Server

Hi, Is there a dummy table in SQL Server just like DUAL in Oracle?
Thanks..



ADVERTISEMENT

Dummy Where Clause Allowing Dummy Select Of Data - Utilizing Where Value = 1

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
 

View Replies View Related

Dummy Table?

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

View Replies View Related

How Can You Insert A Dummy Row At The Top Of Every Related Recordset In A Table?

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?

View Replies View Related

SQL Dummy!

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?"

 

View Replies View Related

Dummy Question

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 Replies View Related

Dummy Question

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

View Replies View Related

Create Dummy Data

 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
endWhat 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 Date3. 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 Number4. 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 FileIDCould 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

View Replies View Related

Accomodating 'Dummy' Data?

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 Replies View Related

Question About Dummy Constraint

I'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 Replies View Related

Create 'Dummy' Rows

Guys - 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

View Replies View Related

Is MSSQL&#39;s Optimizer Dummy Or What?

I have a table with field "field" and index on it.

SELECT * FROM TABLE WHERE field=something does not use index & it makes tablescan.

when i use SELECT * FROM TABLE (INDEX=...)..., it works good and speedy

why optimizer does not use index in that simple query? And what does optimizer do with more sophisticated query!!!!!!

thanx, Beargie

View Replies View Related

Inserting Dummy Data

 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

View Replies View Related

Create Dummy Data

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

View Replies View Related

Inserting Dummy Lines And Padding

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 Replies View Related

Need To Generate Test (Dummy) Data In SQLServer 7.0

Hi.

Does anyone know if SQLServer 7.0 will generate dummy data for specific columns in tables?

TIA,

Jeff

View Replies View Related

Test - Populating Tables With Dummy Data

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?

View Replies View Related

Sort Matrix On Dummy Grouping Value/subtotal

 

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?
 
 

View Replies View Related

How Can I Import Records From A Sql Server Table In Project1 To Another Sql Server Table In Project2

Hi, i have a table with all employee bio-data in a completed project. Iam now working on another project with a table that needs the same data and here iam talking about 300 records that rarely change. Instead of re-entering this data in this new table, i want to import the data from the completed project into a table in this new project. Does any one have any idea how to achieve that or is there a better option to do the same.
One more thing iam realising here is that iam going to use this same data in very many applications and some one from one department is going to enter this data in all these different applications. so i was wondering if there could be a way of having a central database that this guy can mantain and then i be able to use that table data in different applications that iam going to develop. I dont want to kill this unlucky guy with data entry tasks every time i deploy a new application.
So basically, how can make one database application sever several different applications with its data.

View Replies View Related

Query A SQL Server Compact Table While Querying A SQL 2005 Server Table

Hi there,
 
I'm trying to run a query on a SQL Server 2005 table which has a WHERE clause that requires a query from my SQL Compact table.

 

SELECT * from RemoteDB.TESTDB.dbo.Objects

WHERE Last_Updated > '2008-05-21 10:51:00'

AND Object_PARENT IN (select Object_CODE from LocalDB.PDADB.dbo.Objects)

 
Basicallly on a linked system, this query would find all new objects in my main database where the same objects exist in my local database.  This would work just perfectly, no problems.
 
Now, the local database is actually on a PDA running SQL Server Compact Edition.  There is currently no support for creating a linked environment.  I have the option of pulling the table off the local db and pushing it to the remote db and then running the above query from within the single db and then retrieving the list of new entries and pulling them down to the local db but that is a HUGE amount of bandwidth, even if I just used the single primary key column.
 
Would anyone maybe have a little advice for me on how I could possibly achieve the above result on SQL Server Compact please?

 
Thanks in advance

View Replies View Related

Importing Access Table Into SQL Server 2005 Express Table And Adding One Field

Hi all,

 Hopefully I am posting this question in the correct forum. I am still learning about SQL 2005. Here is my issue. I have an access db that I archive weekly into and SQL server table. I have used the dst wizard to create an import job and initally that worked fine. field I have as the primary key in the access db cannot be the primary key in the sql table since I archive weekly and that primary key field will be imported several time over. I overcame this initally by not having a primary key in the sql table. This table is strictly for reference. However, now I need to setup a unique field for each of the records in the sql table.  What I have done so far is create a recordID field in the sql table that is an int and set as yes to Identify (auotnumber). That worked great and created unique id for all existing records. The problem now is on the import. When I try to import the access table i am getting an error because of the extra field in the sql table, and the error is saying cannot import null value into this field. So... my final question is how can I import the access table into the sql table with one extra field which is the autonumber unique field?  Thanks a bunch for any asistance.

Bill

View Replies View Related

INSERT New Record Works OK In Local Table, BUT Not If The Target SS DB/table Is In A Different Physical Server

 

Hi...  I was hoping if someone could share me some thoughts with the issue that I am having at the moment.
 
Problem: When I run the package in my local machine and update  local SS DB/table - new records writes OK in the table.  BUT when I changed my destination meaning write record into another physical SS DB/table there is no INSERT data occurs.  AND SO when I move/copy over that same package into another server (e.g. server that do not write record earlier) and run it locally IT WORKS fine too.
 
What I am trying to do is very simple -  Add new records in a SS table using SSIS .  I only care for new rows and  not even changed rows.
Here is my logic -
1. Create Ole DB source to RemoteSERVER -  using SELECT stmt
2.  I have LoopUp component that will look for NEW records -  Directs all rows that don't find match and redirect rows (error output).
3.  Since I don't care for any rows that is matched in my lookup - I do nothing or I trash the rows
4.  I send the error rows (NEW rows) into OleDB destination
 
RESULTS when I run the package locally and destination table is also local - WORKS FINE;
But when I run the package locally and destination table is in another Sserver (remote) - now rows is written.
 
The package is run thru BIDS manually so there is no sucurity restrictions attached to it.
 
I am not sure what I am missing.  And I do not see error in my package either.  It is not failing.
 
Thanks in advance!
 
 
 
 

View Replies View Related

I Imported A SQL Table Into SQL DataBase, But I Can Not Update This Table Even With SQL Server Management Studio

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
When I change any data on mentioned table above, Red exclamation sign appears left of the record .
How can I correct this problem?
 Thanks.

View Replies View Related

Retrieving Data From SQL Server Table To Display On Button On Datagrid Table.

I have nine type of buttons,
EnrollAmtBTM
PlacAmtBTM and so on, I also have a SQL setver view V_Payment_Amount_List from here i need to display the data on the button
this is the select value to display when i choose the agency list and the amount corresponding to that agency_ID is displayed here the agency_ID is fetched from the SQL CONDITION
 THIS IS WHERE I GET FETCH AGENCY DATA WHEN SELECTED i.e SQL CONDITIONprotected void CollectAgencyInformation()
{
WebLibraryClass ConnectionFinanceDB;ConnectionFinanceDB = new WebLibraryClass();
string SQLCONDITION = "";string RUN_SQLCONDITION = "";
SessionValues ValueSelected = null;int CollectionCount = 0;if (Session[Session_UserSPersonalData] == null)
{ValueSelected = new SessionValues();
Session.Add(Session_UserSPersonalData, ValueSelected);
}
else
{
ValueSelected = (SessionValues)(Session[Session_UserSPersonalData]);
}ProcPaymBTM.Visible = false;PaymenLstBTN.Visible = false;
Dataviewlisting.ActiveViewIndex = 0;TreeNode SelectedNode = new TreeNode();
SelectedNode = AgencyTree.SelectedNode;
SelectedAgency = SelectedNode.Value.ToString();
Agencytxt.Text = SelectedAgency;
Agencytxt2.Text = SelectedAgency;
Agencytxt3.Text = SelectedAgency;DbDataReader CollectingDataSelected = null;
try
{CollectingDataSelected = ConnectionFinanceDB.CollectedFinaceData("SELECT DISTINCT AGENCY_ID FROM dbo.AIMS_AGENCY where Program = '" + SelectedAgency + "'");
}
catch
{
}DataTable TableSet = new DataTable();
TableSet.Load(CollectingDataSelected, LoadOption.OverwriteChanges);int IndexingValues = 0;foreach (DataRow DataCollectedRow in TableSet.Rows)
{if (IndexingValues == 0)
{SQLCONDITION = "where (Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'";
}
else
{SQLCONDITION = SQLCONDITION + " OR Project_ID = '" + DataCollectedRow["AGENCY_ID"].ToString().Trim() + "'";
}
IndexingValues += 1;
}SQLCONDITION = SQLCONDITION + ")";
ConnectionFinanceDB.DisconnectToDatabase();if (Dataviewlisting.ActiveViewIndex == 0)
{
Dataviewlisting.ActiveViewIndex += 1;
}
else
{
Dataviewlisting.ActiveViewIndex = 0;
}
SelectedAgency = SQLCONDITION;
ValueSelected.CONDITION = SelectedAgency;
 
 
???? this is where i use to get count where in other buttons and are displayed.... but i changed the query to display only the Payment_Amount_Budgeted respective to the agency selected. from the viewRUN_SQLCONDITION = "SELECT Payment_Amount_Budgeted FROM dbo.V_Payment_Amount_List " + SQLCONDITION;
try
{
CollectionCount = ConnectionFinanceDB.CollectedFinaceDataCount(RUN_SQLCONDITION);
EnrollAmtBTM.Text = CollectionCount.ToString();
}
catch
{
}////this is my CollectedFinaceDataCount-- where fuction counts the records in the above select statement if i use for eg.
"SELECT Count(Placement_Retention_ID) FROM dbo.V_Retention_6_Month_Finance_Payment_List"
here is the functionpublic int CollectedFinaceDataCount(String SQLStatement)
{int DataCollection;
DataCollection = 0;
try
{
SQLCommandExe = FinanceConnection.CreateCommand();
SQLCommandExe.CommandType = CommandType.Text;
SQLCommandExe.CommandText = SQLStatement;
ConnectToDatabase();DataCollection = (int) SQLCommandExe.ExecuteScalar();
DisconnectToDatabase();
}catch (Exception ex)
{Console.WriteLine("Exception Occurred :{0},{1}",
ex.Message, ex.StackTrace.ToString());
}
 return DataCollection;
}
 
 
So here mu requirement request is to display only the value fronm the view i have against the agency selected
Please help ASAP
Thanks
Santosh

View Replies View Related

Append Query From Access Table To Linked SQL Server Table Failing

Strange one here - I am posting this in both SQL Server and Access forums

Access is telling me it can't append any of the records due to a key violation.

The query:

INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;

Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.

There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.

I can manually append the records using cut and paste with no problems.

I have tried re-linking the tables.

Any ideas?
Thanks,
Brad

View Replies View Related

ADOX To Create A DSNless Linked Table To A SQL Server Table In MS Access?

QUESTION: How do I use ADOX (VB) to create a DSNless linked table to a SQL Server Table in MS Access?

CRITERIA:
- Will need a code skeleton that satisfies all conditions above (Including the minimum table properties required).
- SQL Server Name: MySQLServer
- Database Name: MyTestDB
- Table Name: MyTestTable

View Replies View Related

Capturing Record Count For A Table In Oracle And Saving It In A Table In SQL Server

I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.

 

I understand that I can use a variable to accomplish this task. Well first issue I run into is that what  import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:

Imports System.Data.SqlClient

 

Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.

 

any idea?

thanks

View Replies View Related

How Do I Script INSERTS From An ASP Membership Table Onto Live Server Table

Hi,I would have used the aspnet membership tool to auto-create all the ASP.NET membership tables.  However, the hosting company don't allow remote connections which meant I had to create the tables by hand, scripting the tables using script to CREATE using management studio.However, I noticed one of the tables has data without any users: aspnet_SchemaVersions, which causes an error when trying to log onto my site.The fix is to make sure the table has the 4-5 rows of data in it (which is missing off the live server).  Its just a few rows of data, but I want to script the inserts for each row so I don't have to type them in using myLittleAdmin (the host's web version of management studio). Can anyone point me in the right direction?

View Replies View Related

Accidentally Deleted A Table In My Local SQL Server How Can I Get Back The Table

Hi,

I accidentally deleted a table in my local server. How can I get back the table? I did not do it as a transaction!

Thanks in Advance

View Replies View Related

MSSQL Server 7.0: How To Update A Column In One Table With Values From Another Table

This may be simple but I'm stumped.

I have 2 tables- tableA and tableB.

Each table has basically the same columns and rows with some variations.

The common keys of both tables are employee ID. There are more employees listed in tableA than there are in tableB.

The phone numbers in tableA are incorrect, but they are correct in tableB.

How can I update tableA to have tableB's phone numbers wherever the employee ID field is the same for both tables?

I've been playing around with an update statement, and select statements, but I can't figure out the syntax to update one column using values from another table without appending or overwriting all of the other columns in tableA. I need to keep ALL values in tableA the same except for the phone number column.


Your help is GREATLY appreciated.

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved