Hi,
A seemingly simple question: How do I create an Insert script from the data of a selected number of tables in a given database?
(The Insert script has to be part of a deployment phase)
Esben
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
Hi,
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Thank you for any suggestion.
I am new at the development stages of my DBA career and I am stumped on how to approach this problem, any help would be great.
Problem: Our web developer is designing a Software survey that ranks the difficulty of tasks within a software application like MS Word. Each of these tasks have a rank tied to them.
Here are the tables that he created.
Table 1
EmpID Text
123456 A1, A2, A3
Table 2
ValueID Text Rank
A1 Create Table 1
A2 Insert Query 5
In the text field of table 1 he inserts every task or valueid that the employee is able to do.
He needs to be able to select Empid, text(from table 2), and Rank. Based on need. An example would be 'Which employees can Insert a query'. And he would need to be able to look in the text string on Table 1 and find every A2 in listed then get empid and rank. I know that it can be done with full text searching, however I guess what I want to know is if there is another way to create tables that would jus make a join necessary where the value id can be tied to the empid for each task the employee is able to do?
I hope that this makes sense!!
Chris writes "How do I create a copy of a table (not temporary).
In Oracle I do .
Create table newtable as select * from oldtable
This command copies data and column data types.
I tried entering
SELECT * INTO db2.dbo.newtable FROM db1.dbo.oldtable
in the query manager and it appears to create the new table, but it only seems to be a temporary table because when I leave query manager and check my data base it isn't there.
I have to convert a table that has about 150 columns and about 2 million rows. I rather not have to create the data types for 150 columns."
I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 backend. I currently have a selections table in the front end file which theusers use to make selections of records. The table has two fields -- primarykey (which matches primary key of main, SQL Server table), and a booleanfield. The table is linked to the main table in a heterogeneous inner join.I'm looking to move the table to the back end, while still giving eachmachine a unique set of selections. Using one large table with machine nameas part of the primary key actually slows things down. So I'm consideringusing a series of tables, where each machine has its own table in the backend for selections. The machine name would be incorporated in the particularselections table name, and the front end link would be modified on the flywhen the database is opened to point to that machine's back end selectionstable.This would require having about 50-100 individual selections tables in theback end database. Also, if a machine doesn't have a table when the databaseis opened on that machine, then that table would be created on the fly,populated, and pointed to via the ODBC link.Anyone see any problems with this approach, specifically creating the tableon the fly and then immediately using it, as well as having that many littletables running around? Thanks for any input!Neil
View 31 Replies View RelatedI'm transferring data from DB2 to SQL Server - the data gets wiped every night and reloaded. To keep up with any changes in the tables and to reduce the amount of maintenance on keep field & table definition updated, it would be nice to be able to create the tables in SQL Server in the SSIS package, based on the DB2 definition.
I've noticed it can be done in design mode when creating the OLE DB Destination (clicking on the 'New' button), but I'm not sure how I would automate this...
any suggestions?
Thx
I am trying to go through an arraylist and create some database tables for each entry in the array. what I have is
Dim ques As String
For Each ques In questions
query = "create Table " + ques + " (plantid nvarchar(100), Answer nvarchar(100))"
cmd = New SqlCeCommand(query, con)
cmd.ExecuteNonQuery()
Next
I am wanting to use the item in the arraylist as the name of the db. I am getting an error saying
There was an error parsing the query. [ Token line number = 1,Token line offset = 14,Token in error = 1 ]
Can anyone see what I am doing wrong.
i am trying to create a trigger which when I insert a "y" on a student table in insCheck column, instructor table will create a record of the same person.
The reason is there are two tables are in my DB, student and instructor table. Student can be a instructor so whenever insCheck conlum in studnent table is checked as "y", instructor table should be populated.
Both table studentId and instructorId inserted as manually so that I am not sure how i should populate the instructor's table just fName, mI, and lName and I have go back to the instructor table and populate the rest of the table manually or is there any way to poppulate the insturctorid also while trigger is populate the name onthe instructor table.
My Two tables DDL are like down below
create table student(
studentId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
insCheck char(1) not null,
);
create table instructor(
instructorId char(4) not null primary key,
fName varchar(25) not null,
mI char(1) null,
lName varchar(25) not null,
instructorQual varchar(100) not null,
);
thanks for your help in advance!
gazawaymy
I have made a new table called 'customer' which i wish to tie into the userId of the db. I have used a db diagram to do this (there are keys on each side of the link and userId is the FK) . When i put the membership/profile view onto the form alongside the new customer table nothing displays in the customer table when i run the app., i dont even see the titles - any ideas (i'm new to this sql malarky btw - so its probably something unbelievably straightforward). Any help appreciated.
Thanks
hi all,
How can i create a new data table automatically for new users signing into the website.......what i mean here is that i have a predefined database table and i want that users signing in can have that table for them unique , so that they can fill data for themselves and that data will be visible to all just like forums
Hi All,I will like to create a table from two tables this (explained below) way using a stored procedure. Basically i want the stored procedure to return me the third table but i dont know how to do it.Table ATopicID Topic ----------------------------- 1 Sometopic 12 Some topic 23 Some topic 34 Some topic 4 Table BRateID Rate TopicID---------------------------------------1 5 12 5 13 5 24 4 25 4 36 3 5 Table CTopicID Topic Rate--------------------------------------------1 Some topic1 52 Some topic 2 5 3 Some topic 3 5Basically i have a table that stores topics associated with a particular subject. Those topics are stored in table A above. My users can just read the messages associated with the topic and rate that message. The rating is then stored in table B.Now i will like my users to search for topics with a particular word or sentence in table A and the topics that are returned have a rating they specify. My procedure takes the word or sentence to search in table A and and the rating which will be used in table B. I will like to construct another table that has this newly searched results and return it using a stored procedure or anything simple. I dont know how to do this because i have very little knowledge in stored procedures or sql..... Any help(Code) will be greatly appreciated...
View 11 Replies View RelatedHi,
I need to create a table and a few fields in the SQL database programmatically in asp.net.
I know the tabes name, the server which it resides on, username/password. Would anyone have a small sample of this code?
Thanks
rad
hello
I am working with an existing database and there is no Foreign key between 2 tables
how can i create a FK after , when the tables are allready full ?
product :
product_id
report_id
name
report :
report_id
dateR
i want to create a FK on product.report_id, and ON DELETE CASCADE
thank you
I'm currently writing a web application in Coldfusion which uses a Access 2000 db. I can create tables in SQL ok but am having problems with the Autonumber type. Any ideas?
View 8 Replies View RelatedI am very new to SQL Server 2005 so please excuse my mandune queastion.
First what I accomplished was I created several tables within SQL Server 2000 using the Database and Table Wizards. I also created all the relationships the same way. Next, I had SQL Server 2000 and had the tool reverse engineer the SQL for this new database with all the relationships.
My next step was to run this newly created SQL script on my local box with SQL Server 2005 (SMSS). What I did was create the database first and then assign the users to the new database. From here, I moved to running the sql script. While it was not 100% perfect, it did create all the tables and relationships with no problems.
Now, I am trying to accomplish the same process one a development instance of SQL Server 2005, but the script fails. The data base error says the new database created does not exist. When we go back and look at the database within SMSS, it is GONE!
I am at a lost at how to replicate the database I have on my local box. Any suggestions would be greatly appreciated.
Russ
I am having the following problem and any help would be GREATLYappreciated:In an application I am developing, at some points we create a newtable. When I create this table on another users box, I can not accessit from my box. In sql server I am dbo, but the table created by myapplication when run on a different box has an owner of : "FCxxxx". Ihave sent permissions on this thing to public, but I am still gettingan error when I try to query this thing from my application. And I cannot get query analyzer to recognize this thing. I can see it inEnterprise manager. I would think there is a way to handle this sortof thing. If anyone out there has done anything like this I would bemuch obliged for any ideas. Thanks.Sincerely,Ed HawkesJoin Bytes!
View 1 Replies View RelatedIn sql server 2003 I would create tables with generated sql scripts.
This way I could easily create duplicate dev environments.
Now in SQL 2005 Dev edition I try to run my scripts to create tables.
I create a db [MSS].
They run successfully but there is no table created.
(example script)USE [MSS]
GO
/****** Object: Table [dbo].[KataNames] Script Date: 08/17/2006 13:28:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KataNames](
[Kata] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SequenceId] [int] IDENTITY(1,1) NOT NULL,
[Description] [char](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Style] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Any clue what else I have to do to let these scripts actually create tables or why if they run ok, where is my table ?
Thanks
Kim Allbritain
I had a SQL db that i copied all the tables into excel and it generated the quereies in order to create those tables....Is there anyway i could excute all those table creations at once or would it be easier to just write an application that does that for me????
View 5 Replies View RelatedI have data model as below....I have created a unique constraint as below;
CREATE UNIQUE NONCLUSTERED INDEX [UNQ_StaffIDEventID] ON [dbo].[tblStaffBookings]
(
[StaffID] ASC,
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
I now tried to create a link between two tables tblStaffBookings and tblEventStaffDisciplinary by dragging from tblEventStaffDisciplinary.StaffID to tblStaff Bookings.StaffID and it gives me error as below;What do I need to do to link tblEventStaffDisciplinary.StaffID to tblStaffBookings.StaffID?
Hello,I need to create around 1500 similar tables.Does anyone know how to create them all at once instead of one-by-one?thanks
View 8 Replies View Relatedi created a simple table to record all uploaded files to my website. now, it works, but the problem is, it posts to the table 2 times, as in it executes "Button1_Click" event twice. The result is i get two records which are the same, and only differs in primary key (because i set it as an autonumber). how do i fix this? thanks in advance
here's the code:
HTML: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
InsertCommand="INSERT INTO [Base_Files] ([User_ID], [Date_Posted], [File_Type], [File_Size], [File_Name], [File_Description]) VALUES (@User_ID, @Date_Posted, @File_Type, @File_Size, @File_Name, @File_Description)">
<InsertParameters>
<asp:Parameter Name="User_ID" />
<asp:Parameter Name="Date_Posted" />
<asp:Parameter Name="File_Type" />
<asp:Parameter Name="File_Size" />
<asp:Parameter Name="File_Name" />
<asp:Parameter Name="File_Description" />
</InsertParameters>
</asp:SqlDataSource>
VB:Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
If FileUpLoad1.HasFile Then FileUpLoad1.SaveAs(Server.MapPath(".") & "files" & FileUpLoad1.FileName)
Label1.Text = "Received <strong>" & FileUpLoad1.FileName & "</strong> Content Type: " & FileUpLoad1.PostedFile.ContentType & ", Length: " & FileUpLoad1.PostedFile.ContentLength & "bytes, at " & Date.Now.ToString("MMM dd, yyyy, h:mmtt")
SqlDataSource1.InsertParameters("File_Name").DefaultValue = FileUpLoad1.FileName.ToString()
SqlDataSource1.InsertParameters("User_ID").DefaultValue = User.Identity.Name.ToString()
SqlDataSource1.InsertParameters("File_Type").DefaultValue = FileUpLoad1.PostedFile.ContentType
SqlDataSource1.InsertParameters("File_Size").DefaultValue = FileUpLoad1.PostedFile.ContentLength
SqlDataSource1.InsertParameters("File_Description").DefaultValue = txtDescription.Text.ToString()
SqlDataSource1.InsertParameters("Date_Posted").DefaultValue = Date.Now.ToString("MMM dd, yyyy, h:mmtt")
SqlDataSource1.Insert()
Else
Label1.Text = "No uploaded file"
End If
End Sub
Want help in creating the stored procedure of company where id is the PrimaryKey in the table companymaster which is created in sql server 2005.1 ALTER PROCEDURE companyinsert
2
3 @companyid int,
4 @companyname varchar(20),
5 @address1 varchar(30)
6
7 AS
8
9 INSERT INTO companymaster
10 ( companyname, address1)
11 VALUES (@companyname,@address1) Procedure or Function 'companyinsert' expects parameter '@companyid', which
was not supplied.
The id is to be created autogenerate in the sequence number.There should be no duplicated companyname with different ids in same table.Apart from the above error can anyone pls give me or tell me the code or modify the stored procedure according to the above..thanxs....
how can insert statement script of data present in sql server can
be generated in a .sql file
I'm trying to Insert data from a linked server connection into one of my tables in the sql database. it seems to be giving me an error saying column cant be found. It only does this when I put the Where clause in the statement. I dont have the server in front of me but this is how my statement looks.
Insert into WorkList (DSK)
Select *
From OPENQUERY (SCH, 'Select Desk_ID from public.ACCOUNT Where Desk_ID = LA1')
Hi there can anyone help me to create a SQL Insert Statement. I dont know SQL at all.
To explain, I have two web pages. search.asp and results.asp.
search.asp has the following.
Form Name: searchForm
Set to: Post
Action: results.asp
Text Field: Keyword
Drop Down: CategoryTable
Drop Down: Location
UserName Session Variable: MM_UserName
results.asp has the following SQL which pulls all the results.
SELECT SupplierName, Location, ShortDescription, TimberSpecies, CategoryTitle, Country, CustomerType
FROM Query1
WHERE TimberSpecies LIKE '%MMColParam%' AND CategoryTitle LIKE '%MMColParam2%' AND Location LIKE '%MMColParam3%' AND CustomerType = 'Trade/Retail'
ORDER BY CategoryTitle, TimberSpecies ASC
The database & form I want to insert into.
tblSearcheResults
idSearch (AutoNumber)
location (Text) "Want to insert the 'Location' result here"
category (Text) "Want to insert the 'CategoryTable' result here"
user (Text) "Want to insert the UserName Session Variable result here"
result (Text) "Want to insert the 'Keyword' result here"
Please ask if u need more info.
Mally
Hi, I have limited control on the server I use. I can not create data bases but can add tables and edit the data base that has been created for me. So what I would like to do is be able to use a tool such as aspnet_regsql through SQL Server Express to add the tables that Membership would automatically create. Any help in pointing me in the right direction would be great
Thanks
howlinhuskie
Does anyone know if it's possible to create a trigger on the sysdatabases table in the master database? I keep getting permission denied which I'm not sure is right.
View 2 Replies View RelatedHi,
Does anyone know of a way I can create a trigger on a system table (say sysdatabases in master). I know this is not supported but presumably there's a way it can be done by referencing it's equivilant in Information_schema somewhere.
I'm trying to write a script that will automatically set up a backup schedule for a database that has just been created. I was hoping the trigger would query the sysdatabases table for new database name entries, log necessary info in an audit table and then call a backup script to set up the schedule. Any ideas??
Thanks
Rob
Folks,
Is there any way I can dynamically alter the structure of a table? In other words if my Transact SQL
statement returns 5 rows I want to alter an existing table and put in 5 columns. Strange?!
Something like
alter table add
column+@i
The alter table statement is within a Cursor and ideally @i should increment each time and alter
the table to put in a new column.
If I went 3 times thru the loop, I should have
column1
column2
column3
added to the new table.
Any pointers would be greatly appreciated. Thanks much in advance.
Frenz,
I need to create tables that are session specific. i.e When I login to a database i have to create a Table that can be accessed across all the procedures and triggers. When I log out the Tables should be droped. I understand that Local Temporary (#) tables can be created in annonymus block, but I need the tables to be created during login.
Your response is highly appreciated.
-Cheeku
In SQL Server, I need to create a table from 3 different individual tables. I am new to the SQL Scene, so i want to know the best way to go about this. Each table has different fields, so that is making it difficult (at least for me). How about creating 1 table, that is composed of 3 other tables?
I know this is bad practice, but our District Manager wants to see production for his 'team' and whoever set this up back in the beginning gave each individual employee a different table, with custom fields (Why?!?!?!?!) so now I am trying to merge those 3 into 1, but want to get some insight on the best way to do this since there are different fields in each table and the merged (or master) table needs to have all of those fields.
I need to write create table statements for the er diagram that I attached. I am new to sql and I have trouble integrating foreign keys with these bigger er diagrams.
These are the tables I need to create:
Create Table Author(...)
Create Table Writes(...)
Create Table Book(...)
Create Table Copy(...)
Create Table Loan(...)
Create Table Customer(...)