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?
Hi.
Does anyone know if SQLServer 7.0 will generate dummy data for specific columns in tables?
TIA,
Jeff
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
I am sorry for asking such a broad question, but I have been working on this and from what I can gather it can be done. My problem is that much of it has gone right over my head and I am getting more confused the more I read... I'm really, really confused...
Basically, I have a dataGridView that is populated with a number of fields from Table1 (ID, NameID, Status, Phone, Notes). This works fine, BUT I would like to access Table2 and have, where ID in Table2 = NameID in Table1, it load the First Name & Last Name into the dataGridView. I am able to load the information from Table 2 like so: SELECT NameFirst + ' ' + NameLast from Table2", but I can't get both Tables to work correctly.
I would like the dataGridView to be layed out like this:
ID NameID Name (NameFirst + NameLast) Status Phone Notes
I can't for the life of me understand or get this to work (Or for that matter even understand what I am trying to do...
Also, I am using Access 2007.
I would greatly appreciate some help, and possibly some explanation in laymans terms so that I might be able to understand this. I have read a lot about this, but for whatever reason it is just soooooo over my head that I can't follow it whatsoever.
Here is the code as it stands now:
//Populate the DataGridView
string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + @"DB.accdb;Jet OLEDBatabase Password=MyPassword;";
// create and open the connection
OleDbConnection conn = new OleDbConnection(conString);
OleDbCommand command = new OleDbCommand();
command = conn.CreateCommand();
// create the DataSet
DataSet ds = new DataSet();
// run the query
command.CommandText = "SELECT ID AS [#], NameID AS [Name], Status AS [Status], Phone AS [Phone], Notes AS [Notes] FROM Table1 WHERE ID = " + textBox13.Text + ";";
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter = new OleDbDataAdapter(command);
adapter.Fill(ds);
// close the connection
conn.Close();
bindingSource1.DataSource = ds.Tables[0];
dataGridView1.DataSource = bindingSource1;
// set the size of the dataGridView Columns
this.dataGridView1.Columns[0].Width = 10;
this.dataGridView1.Columns[1].Width = 100;
this.dataGridView1.Columns[2].Width = 100;
this.dataGridView1.Columns[3].Width = 100;
this.dataGridView1.Columns[4].Width = 176;
Any help and information is greatly appreciated.
Thanks Again,
How do I correctly populate a fact table with the surrogate key from the dimension table?
View 4 Replies View RelatedHello,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 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
I am quite new to the complexities of MS SQL and have a problem, I would like to resolve. I have 2 tables with a unique identifier in both and want to populate a new table with information from both, but the second table I would like to populate just some fields that have a DOB eg
Table 1:
uniqueId
Name
Address
Table2:
uniqueId
Type
Setting
example of content for Table 2:
uniqueId Type Setting
123 DOB 03/04/74
234 TFN 12345678
567 POA Mr Smith
So the new table needs to be populated with a ll of info in table 1 and has a new field called DOB so only the clients with a DOB should populate this field, if the client in Table 1 has a TFN reference, this record should be added to the new table but no value needs to be entered eg
123 Chris Smith 1 high street 03/04/74
234 Jon brown 2 high terrace <Null>
Cheers
pommoz
Hi. I have a report which has several datasources which require a table to be populated before they read from it.
i.e. The first thing that needs to happen whenever the report is run, is a call to a stored procedure which populates the table the report datasources are based off of. The SP takes several minutes to complete and MUST complete before any of the datasources fetch their data.
How can this be achieved?
I can not find anything in the Visual Studio Report Designer which allows to me to instruct Datasource B to not execute before Datasource A has completed (or any other way to call a data population SP, before the data reader SP's execute).
Thanks.
Hi,
I have a table in Sql 2005 called
Customers
CustomerId
CustomerName
CustomerAge
CustomerRank
CustomerStCode
I have to transfer the records into 2 tables
CustomerMaster
CustomerId
CustomerStCode
CustomerDetails
CustomerId
CustomerName
CustomerAge
CustomerRank
I have to pick up a row from Customers and transfer it to CustomerMaster and CustomerDetails. CustomerId of CustomerMaster will be the CustomerId of CustomerDetails while transfer. Similarly for all other rows in Customers.
How to do this?
thanks
Hi,
I am new to SQL Server7. I need to populate some tables from an SQL Server7
database at the end of the day. How can I automate this process?
I also need to export these populated tables to a text file on daily basis.
I know I can use "DTS" to do this. But is there any way to make these
automated also? Or is there any third party tool to do all these?
Thanks in advance.
Mkhan
Hi!
I'm trying to setup a DTS that reads a flat file uses a Data Driven Query task and then selects ONLY records that does not exist in the database and then INSERT them to DB1.
This works fine but I need to add another functionality.
I need to create a record on another table(DB2) based on the freshly inserted records in DB1 using only some of the fields. How do I do it?
Is setting up a trigger possible so that everytime a record is inerted in DB1 it will automatically a populate DB2?
Flat file:
ID
Name
Phone
DB1
ID
Name
Phone
DB2
ID
PHone
Event (from 00 to 10)
NumActions (initialized to 0)
Please help.
Thanks.
$3.99/yr .COM!
http://www.greatdomains4less.com
I need to populate tables in my MS SQL 2000 DB with content from an excel file. I am not sure how this is done or how to format the excel file. If someone could help me with this it would be much appreciated!Thanks!
View 3 Replies View RelatedHey guys, I'm an old DevShed member, but my old account isn't working for some reason, so I had to recreate..
I've recently decided to learn MS SQL, and having some trouble with creating and populating tables. Using MS SQL Express 2005.
Heres the code, I keep reading through my notes on how to do it, but I cant see what I'm doing wrong. This is my first attempt at it, so there may be more wrong that I think.
Code:
drop table Property_rental;
drop table Property_type;
drop table Property_owner;
drop table Staff;
drop table Tenant;
drop table Tenant_category;
create table Tenant_category
(TCATID SMALLINT PRIMARY KEY NOT NULL,
TTYPE NVARCHAR(15))
;
create table Property_type
(PTYPEID SMALLINT PRIMARY KEY NOT NULL,
PTYPE NVARCHAR(20) NULL)
;
create table Property_owner
(POWNERID SMALLINT PRIMARY KEY NOT NULL,
FNAME NVARCHAR(20) NULL,
SNAME NVARCHAR(20) NULL,
CONTACT NVARCHAR(15) NULL,
ADDR NVARCHAR(50) NULL)
;
create table Staff
(STAFFID SMALLINT PRIMARY KEY NOT NULL,
FNAME NVARCHAR(20),
SNAME NVARCHAR(20),
CONTACT NVARCHAR(20))
;
create table Property_rental
(ID SMALLINT PRIMARY KEY NOT NULL,
PTYPEID SMALLINT NOT NULL,
STAFFID SMALLINT NOT NULL,
POWNERID SMALLINT NOT NULL,
CONSTRAINT Prop_Type_fk FOREIGN KEY(PTYPEID) REFERENCES Property_type(PTYPEID),
CONSTRAINT Prop_Staff_fk FOREIGN KEY(STAFFID) REFERENCES Staff(STAFFID),
CONSTRAINT Prop_Owner_fk FOREIGN KEY(POWNERID) REFERENCES Property_owner(POWNERID))
;
create table Tenant
(TENANTID SMALLINT NOT NULL,
TCATID SMALLINT NOT NULL,
ID SMALLINT NOT NULL,
FNAME NVARCHAR(20),
SNAME NVARCHAR(20),
CONTACT NVARCHAR(20),
COMMENTS NVARCHAR(20),
CONSTRAINT Ten_Cat_fk FOREIGN KEY(TCATID) REFERENCES Tenant_category(TCATID),
CONSTRAINT Ten_Prop_fk FOREIGN KEY(ID) REFERENCES Property_rental(ID),
CONSTRAINT Ten_pk PRIMARY KEY (TENANTID, TCATID, ID))
;
Error messages I'm getting;
Code:
Msg 547, Level 16, State 0, Line 55
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 56
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 57
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 58
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 59
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 60
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 61
The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 82
The INSERT statement conflicted with the FOREIGN KEY constraint "Prop_Staff_fk". The conflict occurred in database "master", table "dbo.Staff", column 'STAFFID'.
The statement has been terminated.
I have 14 Windows folders containing a mix of Word and PDF documents. Each folder contains up to 500,000 files and these documents are the source for a document management system.
I need to create an audit table which can take the file names and date modified for every document in each folder but I want to avoid having to do a DOS command like dir *.* > filenames.txt then importing as a text file 14 times. Is there a way of automating this in T-SQL?
Each Windows folder is named by year e.g. 2002Docs, 2003Docs, 2004Docs etc.
Documents within the folders are named like this - 20020401_doc1.doc, 20020401_doc2, 20020401_doc678.pdf etc.
I created a Fact Table with 3 Keys from dimension tables, like Customer Key, property key and territory key. Since I can ONLY have one Identity key on a table, what do I need to do to avoid populating NULLs on these columns..
View 3 Replies View RelatedHi, all here,
Thank you very much for your kind attention.
I am wondering if it is possible to use SSIS to sample data set to training set and test set directly to my data mining models without saving them somewhere as occupying too much space? Really need guidance for that.
Thank you very much in advance for any help.
With best regards,
Yours sincerely,
Hi All,
I have a startdate (01/11/2007) and a enddate (01/11/2008). I need to add dates into a table for everyday between these dates. Can anyone help?
Hi
Need to populate data from SQL Server in a pdf file which is basically a government form.
Data should be fetched from the SQL server database and needs to be displayed in a pdf file.
Advice me on how to implement this.
Suggest me if there is any other idea for implementing the same.
Thanks in advance
I have a project that entails the following:
There are two separate SQL Server databases involved that reside on two different servers. One of the depts within our building wants to have building permit data imported from Permit Database on Server "A" to their own database on Server "B".
I dont think this will be an overly complicated process. There are only a few fields they want populated (5 or 6 tops). This will have to be ran every
weeknight via some sort of scheduled task in Windows or SQL.
I was just interested in seeing if anyone has had prior experience working on data transfer like this. I would like to know what would be the best and most efficient way to approach this.
Thanks in advance.
HI,
I populed my data base. But before I populate I created some reports beased on this DB. Now after I populating I can execute query and see results in Management studio or query designer. But When I change it the report to preview mode I can not see it.
Does anyone know why this is?
Thanks
Hi all,
I have a table where I want to populate it with data (certain fields) from another table. But I only want to populate the table if there is data in a certain field in the table I wish to get the data from.
How exactly would I do this test? Would an I have to perform an IF statement to test if the field contains a value first?
Thanks
Tryst
I have a date table(A) for every day of the year from 1990-2016 including the week number.There is another table(B) that has a number, date and week number.
I would like to join these 2 tables together, in order to populate the number from table B on every day of the week (of the week number in table B).
What makes it a little more complex is that the dates in table B not always correspond with the week number in the same row. Adding to the problem are some conditions:
- Every day of the week should have a number, and it should never be 0.
- If the 1st date of all rows in table B with the corresponding week number is not the monday of the week (but for example wednesday) it should start with this number on monday.
- Thousands of rows are in table B and for some of them the date corresponds with the week and for some of them they don't
- The rows can be grouped together using the week number.
Take the following example of table B:
B:
ID Date Number WeekNo
1 21-5-2015 25 21
2 23-5-2015 30 21
In this example the dates correspond with the weeknumber, because the 21st and the 23rd of may are week 21. By joining this with the date table (A), by using for example cross apply, I would hope to get the following result;
Date Number WeekNo
18-5 25 21
19-5 25 21
20-5 25 21
21-5 25 21
22-5 25 21
23-5 30 21
24-5 30 21
The same should work if the same example had the week number 22 in every row.
Take the following example of table B:
B:
ID Date Number WeekNo
1 21-5-2015 25 22
2 23-5-2015 30 22
In this example the dates do not correspond with the weeknumber, because the 21st and the 23rd of may are week 21 and not 22. By joining this with the date table (A), I would hope to get the following result;
Date Number WeekNo
25-5 30 22
26-5 30 22
27-5 30 22
28-5 30 22
29-5 30 22
30-5 30 22
31-5 30 22
Because the last result (ordered by date) of the week number 22 is 30, the whole week shows 30 on every day.
I have just started to look at SQL and have a theory question that I could apply to a test I want to run. I have some legacy data from a previous project and the database was not designed properly (in my opinion). They have ONE field to capture City and State information. All the data is formatted City, State .
Does SQL have commands that can look at data in a field, strip out info before and info after a comma and then write that to other fields?
So, I would like to normalize this to take the data in a field called CityState and parse it, trim it and then populate two new fields 1) City and 2) State.
Thanks for your help!
Scott
Table Name: EmployeeDetails
Columns: EMpID - Date - WorkedHours
For each day I get details of number of hours worked by each employee in this table.
Now my HR wants a report with such columns
empid - Week - Month - Qtr
So, week will have Sum of hours worked by employee in that week
Month will have Sum of hours worked by employee in that Month
Qtr will have Sum of hours worked by employee in that Qtr
Hi,
I have tables like the one below for my Stage and dimension tables:
Stage Table
accountid
name
address
Dimension Table
accountkey ---- surrogate key (DW key)
accountid ---- business key (transaction's primary key)
name
address
I used slowly changing dimension to detect the changes for the records inside my Dimension table. But I had a problem when a new record exists in the stage table. The accountkey is set as the primary key and it gets its value from a different table which stores the last account key that was created. I cannot load all the changes unless i have a business key. Is there a way that i can get the "last key" from a different table in the data flow area and then supply it together with the other fields in the new output branch of the slowly changing dimension?
cherriesh
thanks!
Hello
I have a production database that i need to refresh to our test environment daily. The database size is 700 MB.
I do not need to transfer the stored procedures and triggers , users and logins.
Would a DTS package that runs every night be the best and the easiest solution to implement or should i look into log shipping and snapshot replication.
thanks
I have multiple web databases for storefront orders as linked servers on SQL Server 2008 R2. I need to organize the data for these orders into a structure that can be imported into my ERP application db frequently either on demand or periodically during the day. We are essentially trying to make the manual order entry process automated. My thought was to get the data into views that resemble the schema of the order table in my application db and then schedule a stored procedure with sql jobs or a load routine from within the ERP application that would insert data from the view into the order table.
View 2 Replies View RelatedI have a combo box where users select the customer name and can eithergo to the customer's info or open a list of the customer's orders.The RowSource for the combo box was a simple pass-through query:SELECT DISTINCT [Customer ID], [Company Name], [contact name],City,Region FROM Customers ORDER BY Customers.[Company Name];This was working fine until a couple of weeks ago. Now wheneversomeone has the form open, this statement locks the entire Customerstable.I thought a pass-through query was read-only, so how does this do atable lock?I changed the code to an unbound rowsource that asks for input of thefirst few characters first, then uses this SQL statement as therowsource:SELECT [Customer ID], [Company Name], [contact name],City, Region Fromdbo_Customers WHERE [Company Name] like '" & txtInput & "*' ORDER BY[Company Name];This helps, but if someone types only one letter, it could still bepulling a few thousand records and cause a table lock.What is the best way to populate a large combo box? I have too muchdata for the ADODB recordset to use the .AddItem methodI was trying to figure out how to use an ADODB connection, so that Ican make it read-only to eliminate the locking, but I'm striking outon my own.Any ideas would be appreciated.Roy(Using Access 2003 MDB with SQL Server 2000 back end)
View 2 Replies View RelatedWe are setting up a test lab environment with 100 machines. We want one master testing db that gets replicated to each to run scripted application tests nightly.
My goal is to minimize the amount of work to move this thing to each of the 100 test machines. I am wondering if we need to even have the sql local and invest in a monster db server with 100 copies of the db we restore and each test machine point to their own db on that server, or if I should use db mirroring or something to get the master test db to each of those machines instead.
Now that we have a good programming model in SSIS - the question is whether to write automated unit tests for your packages, and would it generally be a good idea for packages?
Also - if yes to write tests - then where to find more informations regarding How to accomplish that?
hi every one,
i need to test SSIS pacakge which will import data from different database where record count is around 5 millions.
iam planning to test it through c# code as well as manually also.
SSIS source : consist of 7 tables
SSIS destination :consist of 7 tables
Using c# code iam trying to run ssis package through batch file.
i am putting expected rowcount, column count in an excel file and comparing same with destination tables by writing query implementing ADO.Net concept.
am i going right way ,can any one suggest best and productive way to test the ssis package .
what are the other things i need to test it.
do any one can add test cases to it.
S.No
Test Case
1
Verify all the tables have been imported.
2
Verify all the rows in each table have been imported.
3
Verify all the columns specified in source query for each table have been imported
4
Verify all the data has been received without any truncation for each column.
5
Verify the schema at source and destination
6
Verify the time taken /speed for data transfer
7
Fields truncated due to difference in length of the field at destination.
Regards
Arif shareef