Insert From One Tabel Into 2 Tables
May 20, 2008
Hi, i need to create a stored procedure that will take data from one table and insert it into one tabe.
I have 3 tables
tblTest is the table that i need to take the informations from.
intD1 to intD4 is a score
I don't know if iexplain my sefl correctly tks in advance!
tblTest
1 , 2 , 'A', 'B', 0 , 1 ,2 , 4
I need this
tblResultat
1, 2, 'A','B'
tblResultatXReponse
1,1,0,0
2,1,1,1
3,1,2,2
4,1,4,3
tblTest
intTestId autonumber
intCoursId
strCodeAbsent1
strCodeAbsent2
intD1
intD2
intD3
intD4
tblResultat
intResultatsId autonumber
intCoursId
strCodeAbsent1
strCodeAbsent2
tblResultatXReponse
intResultatXReponseId autonumber
intResultatsId
intReponseDesc
intOrdre
View 2 Replies
ADVERTISEMENT
Aug 3, 2004
Hi, I'm fairly new to strored procedures. what I need to do is create a new row with an INSERT specifying value A as a param ( so far no problem ) and value B as a value from table2. Is this possible?
Thanks for your help
Raif
View 3 Replies
View Related
May 16, 2008
Hi i am trying to create an insert statement that will insert rows into a table based on the information in the table already.
the table looks like this
Groupid field1 field2
-1 100 200
-1 100 300
-1 300 500
-1 300 600
-1 400 100
the insert looks like this
INSERT Into table1(groupid,field1,field2)
select -1,@passedvalue,field2
from table1
where field1 = @passedvalue1
assume @passedvalue = 700, @passwedvalue1 = 100
Now this is fine however i cannot have a duplicate key (key is comibantion of all 3 fields) thus the first time this runs it works however if it runs again it fails - how can i change the where clause to ignore rows that already exist?
eg if @passedvalue = 300 and passedvalue1 = 500
View 1 Replies
View Related
Oct 24, 2005
How can I get the script of a table.i.e 'CREATE TABLE' script.
1. One way is to get this script from SQL Analyzer.
but I m having tables created in the database and I want the My Stored Procedure should build the script and return it to me ...
How could I achieve that ?
Thankx in advance... :)
View 2 Replies
View Related
Nov 16, 2006
I link to a sql server for the first time.
I change my .provider and .connectionstring to ADO
And know in one of my forms i get a debug Error; Invalid table Name "tablename"
can someone help me with this.
Thanks eddied
View 6 Replies
View Related
Oct 29, 2007
Hi All,
When an end user view a report using report manager or a custom build web site, would query of the report scan the tables in original data base? In this case it is a OLTP data base.
Thanks
View 4 Replies
View Related
Nov 25, 2005
Hi,
I cannot figure out how to print the design of a tabel.
What I need is to have printed on a paper the list of the columns, data type, length, allow nulls and description for a table.
thx
View 4 Replies
View Related
Nov 24, 2006
Ik wil uit een tabel het gehele getal halen, de waarde staat als05612123 maar wanneer ik ditopvraag in bv. de Query Analyzer wordt de waarde gegeven als 5612123.De 0 welke ervoor staat wordtdus niet afgebeeld. De tabel in de kolom heeft als Data Type char, deLength staat op 10 en Allow Nulls staat aangevinkt. Wie kan mij helpen?Dank.
View 3 Replies
View Related
Sep 19, 2007
Hi
Running on SQL Server2005
I have an tabel Match, TeamInGroup, Team, Group and Series.
Want following resutlt:
Tabell
Lag S V O F TOTAL P
Grupp A
BK Örnen 2 2 0 0 30-10 4
Wåxnäs BC 3 2 0 1 35-25 4
BK Kaskad 2 1 0 1 20-20 2
BK Bågen 3 1 0 2 29-31 2
BK Glam 3 1 0 2 24-36 2
IFK Norrköping BF 3 1 0 2 22-38 2
Grupp B
Uppsala BC 90 3 2 1 0 35-25 5
Ludvika BK 2 2 0 0 25-14 4
Sundbybergs IK F 3 1 1 1 34-26 3
LBK Hudik 3 1 0 2 23-36 2
Domnarvets BS 2 0 1 1 19-21 1
Örta IF 3 0 1 2 23-37 1
Grupp C
Stureby BK 3 2 0 1 39-21 4
Tureberg IF 2 2 0 0 28-12 4
BK Stallis 3 2 0 1 28-31 4
BK Amiki 3 1 0 2 28-31 2
Djurgårdens IF 2 1 0 1 15-25 2
BK Brio 3 0 0 3 21-39 0
Tabel Match:
USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Match] Script Date: 09/19/2007 17:25:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Match](
[MatchId] [int] NOT NULL,
[Matchstart] [datetime] NULL,
[LagIdHemma] [int] NOT NULL,
[LagIdBorta] [int] NOT NULL,
[Resultathemma] [int] NULL,
[ResultatBorta] [int] NULL,
CONSTRAINT [PK_Match_1] PRIMARY KEY CLUSTERED
(
[MatchId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Match] WITH CHECK ADD CONSTRAINT [FK_Match_Team] FOREIGN KEY([LagIdHemma])
REFERENCES [dbo].[Team] ([TeamId])
GO
ALTER TABLE [dbo].[Match] CHECK CONSTRAINT [FK_Match_Team]
GO
ALTER TABLE [dbo].[Match] WITH CHECK ADD CONSTRAINT [FK_Match_TeamInGroup] FOREIGN KEY([LagIdHemma])
REFERENCES [dbo].[TeamInGroup] ([TeamId])
GO
ALTER TABLE [dbo].[Match] CHECK CONSTRAINT [FK_Match_TeamInGroup]
Tabel Team:
USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Team] Script Date: 09/19/2007 17:28:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Team](
[TeamId] [int] NOT NULL,
[Name] [varchar](20) NOT NULL,
[GroupId] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED
(
[TeamId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Tabel TeamInGroup:
USE [Bowlingserier]
GO
/****** Object: Table [dbo].[TeamInGroup] Script Date: 09/19/2007 17:27:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TeamInGroup](
[TeamId] [int] NOT NULL,
[GroupID] [int] NOT NULL,
CONSTRAINT [PK_TeamInGroup_1] PRIMARY KEY CLUSTERED
(
[TeamId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TeamInGroup] WITH CHECK ADD CONSTRAINT [FK_TeamInGroup_Grupp] FOREIGN KEY([GroupID])
REFERENCES [dbo].[Grupp] ([GroupId])
GO
ALTER TABLE [dbo].[TeamInGroup] CHECK CONSTRAINT [FK_TeamInGroup_Grupp]
Tabel Group:
USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Grupp] Script Date: 09/19/2007 17:29:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Grupp](
[GroupId] [int] NOT NULL,
[SeriesId] [int] NOT NULL,
[Namn] [varchar](50) NULL,
CONSTRAINT [PK_Grupp_1] PRIMARY KEY CLUSTERED
(
[GroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Grupp] WITH CHECK ADD CONSTRAINT [FK_Grupp_Serier] FOREIGN KEY([SeriesId])
REFERENCES [dbo].[Serier] ([SeriesId])
GO
ALTER TABLE [dbo].[Grupp] CHECK CONSTRAINT [FK_Grupp_Serier]
Tabel Series
USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Serier] Script Date: 09/19/2007 17:30:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Serier](
[SeriesId] [int] NOT NULL,
CONSTRAINT [PK_Serier] PRIMARY KEY CLUSTERED
(
[SeriesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
This is what i dot so far:
SELECT tg.GroupID, t.Name, COUNT(m.ResultatHemma) AS S,
CASE
WHEN m.ResultatHemma - m.ResultatBorta > 0 THEN
COUNT(m.ResultatHemma)
ELSE 0
END AS V,
CASE
WHEN m.ResultatHemma - m.ResultatBorta = 0 THEN
COUNT(m.ResultatHemma)
ELSE 0
END AS O,
CASE
WHEN m.ResultatHemma - m.ResultatBorta < 0 THEN
COUNT(m.ResultatHemma)
ELSE 0
END AS F,
SUM(m.ResultatHemma) AS Hemma, SUM(m.ResultatBorta) AS Borta,
SUM(m.ResultatHemma - m.ResultatBorta) AS Diff,
CASE
WHEN m.ResultatHemma - m.ResultatBorta = 0 THEN 1
ELSE
CASE
WHEN m.ResultatHemma - m.ResultatBorta > 0 THEN 2
ELSE 0
END END AS P
FROM Match AS m INNER JOIN
Team AS t ON m.LagIdHemma = t.TeamId INNER JOIN
TeamInGroup AS tg ON m.LagIdHemma = tg.TeamId
WHERE (m.MatchId = m.MatchId) AND (tg.GroupID = 2 OR tg.GroupID = 1 OR tg.GroupID = 3)
GROUP BY t.Name, tg.GroupID, m.Resultathemma, m.Resultatborta
ORDER BY P DESC, Diff DESC, S DESC
I can't figure out howto group by t.Name and team in Group A,B, and C
Here are some data From Match:
MatchId Matchstart LagIdHemma LagIdBorta Resultathemma ResultatBorta
----------- ----------------------- ----------- ----------- ------------- -------------
700201001 2007-09-08 10:00:00.000 33768 159120 16 4
700201002 2007-09-08 15:00:00.000 33744 159120 16 6
700201003 2007-09-08 11:00:00.000 33894 33407 16 4
700201004 2007-09-08 14:00:00.000 33874 33407 11 9
700201005 2007-09-08 11:00:00.000 33737 32186 10 10
700201006 2007-09-08 16:20:00.000 30896 32186 15 5
700201007 2007-09-08 11:00:00.000 33286 42031 11 9
700201008 2007-09-08 15:00:00.000 33290 42031 14 5
700201009 2007-09-08 11:00:00.000 33628 33722 13 7
700201010 2007-09-08 15:00:00.000 33684 33722 15 5
700201011 2007-09-08 11:00:00.000 43635 33705 9 11
700201012 2007-09-08 15:00:00.000 31346 33705 16 4
700201013 2007-09-15 11:00:00.000 159120 33768 8 12
700201014 2007-09-15 16:00:00.000 33407 33768 11 9
700201015 2007-09-15 11:00:00.000 33874 33744 9 11
700201016 2007-09-15 13:00:00.000 33894 33744 14 6
700201017 2007-09-15 10:00:00.000 32186 33737 12 8
700201018 2007-09-15 16:00:00.000 42031 33737 13 7
700201019 2007-09-15 11:00:00.000 33290 30896 9 11
700201020 2007-09-15 15:40:00.000 33286 30896 10 10
700201021 2007-09-15 11:00:00.000 33722 33628 9 10
700201022 2007-09-15 16:00:00.000 33705 33628 12 8
700201023 2007-09-15 11:00:00.000 31346 33684 8 12
700201024 2007-09-15 15:00:00.000 43635 33684 16 4
Team:
TeamId Name GroupId
----------- -------------------- -----------
30896 Sundbybergs IK F 2
31346 Turebergs IF 3
32186 Örta IF 2
33286 Domnarvets BS 2
33290 Ludvika BK 2
33407 BK Glam 1
33628 BK Amiki 3
33684 Stureby BK 3
33705 BK Brio 3
33722 BK Stallis 3
33737 Uppsala BC90 2
33744 BK Bågen 1
33768 Wåxnäs BC 1
33874 BK Kaskad 1
33894 BK Örnen 1
42031 LBK Hudik 2
43635 Djurgårdens IF 3
159120 IFK Norrköping 1
(18 row(s) affected)
TeamInGroup:
TeamId GroupID
----------- -----------
30896 2
31346 3
32186 2
33286 2
33290 2
33407 1
33628 3
33684 3
33705 3
33722 3
33737 2
33744 1
33768 1
33874 1
33894 1
42031 2
43635 3
159120 1
(18 row(s) affected)
Group:
GroupId SeriesId Namn
----------- ----------- --------------------------------------------------
1 48967 Grupp A
2 48967 Grupp B
3 48967 Grupp C
(3 row(s) affected)
And Serier:
SeriesId
-----------
48967
(1 row(s) affected)
Best regards
Gerten
View 14 Replies
View Related
Sep 24, 2001
I'm making a system to data acquisition (production) and need more (3) tabels with a heavy load of data.
3 tabels will contain approximately 20-50 milion rows. Every day I need to add 80000 rows to these tabels. Will I get problems with system-performance with such a configuration? Ofcourse the system will contain index in the database and 2 fast 1 Ghz intel CPU. I isn't possible for me to seperate or archive theese tabel, because of researc-functionality.
Michael
View 1 Replies
View Related
Apr 9, 2007
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.
View 5 Replies
View Related
Nov 9, 2007
I have flat file (comma delimited) with 200 columns, and i want to import this to sql table using SSIS package, I create a Flat file source and sql server destination items from tool box. in destination item, i can not see the preview, for some reason, it is not reading the rows from source, Do I have to manually each and every column from source to destination under mapping tabl in destination item.
SQL 2000 DTS never required such thing.
any help?
Thanks,
View 10 Replies
View Related
Apr 22, 2004
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.
View 2 Replies
View Related
Mar 18, 2007
I currently insert into one table with: SqlCommand comm = new SqlCommand("INSERT INTO UsersTable (UserName, Password, Email) VALUES (@person, @pass, @email)", sqlConnection); comm.Parameters.AddWithValue("@person", usrnmeLbl.Text); comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text); comm.Parameters.AddWithValue("@email", hemailLbl.Text);but I realized that there's another table related to this table and I need to have something go in it so that the users data will be recorded at the same pace. So I tried: SqlCommand comm = new SqlCommand("INSERT INTO UsersTable, FatherHistTable (UserName, Password, Email), (Father) VALUES (@person, @pass, @email), (@father)", sqlConnection); comm.Parameters.AddWithValue("@person", usrnmeLbl.Text); comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text); comm.Parameters.AddWithValue("@email", hemailLbl.Text); comm.Parameters.AddWithValue("@father", fthrsNmeLbl.Text);Not working, so I am thinking I must do two inserts: SqlCommand comm = new SqlCommand("INSERT INTO
UsersTable (UserName, Password, Email) VALUES (@person, @pass,
@email)", sqlConnection);
comm.Parameters.AddWithValue("@person", usrnmeLbl.Text);
comm.Parameters.AddWithValue("@pass", hiddenpassLbl.Text);
comm.Parameters.AddWithValue("@email", hemailLbl.Text); SqlCommand comm2 = new SqlCommand("INSERT INTO
FatherHistTable (Father) VALUES (@father)", sqlConnection);
comm2.Parameters.AddWithValue("@father", fthrsNmeLbl.Text); Is that the only way to go about it then? Thanks in advance for any explanations.
View 26 Replies
View Related
Dec 19, 2006
Please i need to insert two tables at a time. One is Delivaries and the other is DelivaryDetails. This is the Structure:
DelivariesDelivaryId...........Int Primary Key IdentityDelivaryNo.........nvarcharDelivaryDate......datetime
DelivaryDetailsDelivaryId............Int Primary Key Foreign KeyProductId............Int Foreign KeyQty....................Int
I have created a StoredProcedure named InsertDelivaries
CREATE PROCEDURE InsertDelivaries@DelivaryId int output,@DelivaryNo nvarchar(20),@DelivaryDate datetime
AS
insert into Delivaries values(@DelivaryNo,@DelivaryDate)
but this does not insert the records as expected. Please help
View 5 Replies
View Related
Apr 10, 2007
hello..
I think this is common question, but i still dont get answer from all past topic and thread.
My problems is:
I have two tables,
1) company_info (com_ID(Primary key with increment int), name, add, contact_no)
2) company_detail(com_ID(Primarykey without increment), product, quantity)
I want capture the data com_ID from 1st table and at the same time I want to insert into 2nd tables. How the way??
currently I do in table adapter in data set.
I also looking how to use store procedures? my knowledge about store proceduree is zero and really need articles or tutorial from any website. anylink?
One more things, I also which part i should do the relationship, in dataset or at database diagram?
I really lack in this database knowledge
Thank you
View 3 Replies
View Related
Dec 18, 2007
hello my friends
i have a database with 5 tables : 1-Company 2-Activity 3-Product 4-Project 5-Telephone
each table have a one to many relation with Company Table ! i mean a Company can has many Activities,Products,Projects,Telephones i want to insert in tables at a same time insert into Company table ! i know that i should first insert into company Table and get CompanyID that need to insert in other tables. i hear that i can do this with define a VIEW of joining these tables and use trigger!i dont know how !?
I'll appreciate you to help me :O)
View 4 Replies
View Related
Feb 22, 2008
I am using Framework 3.5 with VS2008 writing VB code to put and get data in a SQL 2005 Server database.
How to fill out a form, and on one submit button click, send some information into one database table and different information into a different table?
Those tables of mine have a common key, but one is about where and when and what work needs to be done and the other is about authorization, departments and funds.
I’d just think that I should be able to do that in one InsertCommand="INSERT INTO …� statement and not have to code a whole new <asp:SqlDataSource ID="SqldsDOIT" runat="server" interface for the second table … which is the work-around that I can do … it just seems ignorant to me, when we are told to keep like-things in separate tables (normalization).
In other words, the following fails to run ... if I only INSERT INTO Job( or only INSERT INTO Signatory( ... it's all good.A little help please. I've scanned through the forums, but not found exactly this addressed. <asp:SqlDataSource ID="SqldsDOIT" runat="server" ConnectionString="<%$ ConnectionStrings:DOITWorkOrderConnectionString %>" SelectCommand="SELECT FROM [Job] JOIN [Signatory] ON Job.Jobnumber = Signatory.Jobnumber" InsertCommand="INSERT INTO Job(Datereq, ..., Jobcomm) VALUES(@Datereq, ..., @Jobcomm) INTO Signatory(BillProgram, ..., Sigemail) Values(@BillProgram, ..., @Sigemail)"> <InsertParameters>
View 4 Replies
View Related
Sep 5, 2001
I want to insert records from two tables into one table as the record is concatenated from both the tables and then put it into the one table.
How can we do that so that i can take the records from the one table..?
CREATE PROCEDURE SP_P31
AS
select 'CA'+'|'+COMPANY+'|'+COMPANYNAME+'|'+CUSTOMERNUMBE R+'|'+COMPANYPHONE
+'|'+COMPANYFAX+'|'+FEDTAXID+'|'+DUNSNUMBER+'|'+S1 099+'|'+DUNSSUFFIX+'|'+convert(char,ADDRESSSEQ)
+'|'+COUNTRY+'|'+ADDRESSTYPE+'|'+ADDRESS1+'|'+ADDR ESS2+'|'+ADDRESS3+'|'+ADDRESS4
+'|'+CITY+'|'+STATE+'|'+ZIP+'|'+FAXNUMBER hello1 into temp32
from v4
select 'CT'+'|'+COMPANY+'|'+CONTACTNAME+'|'+CONTACTTYPE+' |'+CONVERT(CHAR(10),CONTACTADDRESS)
+'|'+PHONE+'|'+FAX+'|'+TITLE hello INTO temp32
from v3
View 3 Replies
View Related
Mar 8, 1999
Does SQL allow you to insert data into 2 related tables simultaneously?
I have a form where the user enters in Name, Age, SocialSecurity,
Address. The Address table is a separate table than the Individual
table. How do I insert into both ??? Both tables are tied with a unique Individual_ID which is system generated.
I am using a stored procedure
Thanks alot!!
Joyce
View 1 Replies
View Related
Oct 19, 2007
I posted this question previously but am now getting another error and fixing it (months ago). I am trying insert The ID2 column (pk) from table 2 into ID2 column (fk) of table 1. What the T-SQL is trying to do is check to see if the record in table 2 already exists in then just pass the value to the insert query for table 1. If the record does not exist then insert data into table 2first then perform the table 1 insert.
This SP works fine if the user does not exist in table 2. However, if the user does exist in table2 then instead of passing the ID, a null value is passed.
Can anyone tell me why the first query does not work? If there is an easier way of doing this then I am all ears.
Thank you
DECLARE @IdentityHolder int
BEGIN TRANSACTION
IF EXISTS (SELECT ID2 FROM [tbl2] WHERE ID2 = @ID2)
BEGIN
(SELECT ID2 FROM [tbl2] WHERE ID2 = @ID2)
END
ELSE
BEGIN
INSERT INTO [tbl2] ([ID2], [FN], [LN], emailAdd])
VALUES ( @ID2, @fName, @lName, @emailAdd)
END
COMMIT
SET @IdentityHolder = (ID2)
INSERT INTO [tbl1]([ID2], [event])
VALUES (@ID2, @event)
View 14 Replies
View Related
Dec 12, 2007
Hi i have a stored prodedure which inserts into a local table then inserts into a remote table this works fine, what i need to do is.
INSERT ONTO TABLE A
THEN GET THE MAX(ID) OF THE RECORD INSERTED
THEN INSERT THAT MAX(ID) INTO TABLE B
i have created a parameter called @WebID but how do i give it the value of the MAX(ID) before i insert into tableB
Many thanks
View 2 Replies
View Related
May 18, 2008
Good evening every body.
I have 3 tables ( customer, product and order).
Customer table contain customer_id as a primary key
Product table contain product_id as a primary key
Order table contain both customer_id and product_id
I want to insert the values from customer and product to order.
Any help?
View 7 Replies
View Related
Feb 7, 2008
I want to compare two tables. if the data is existed to the other table i will insert it to other table if not exist it will load also to diffrent table.
Example: if my table was Tbl_StudentID and Tbl_FacultyID i want to insert in tbl_Grade if the data in Tbl_StudentID is exist or present in Tbl_FacultyID . And if the Tbl_StudentId is not exist in Tbl_FacultyID the data will move to Tbl_SupervisorID.
Please Help..
View 3 Replies
View Related
Jun 30, 2006
Coming from an asp world, I am totally lost on this one.
I have two tables.
tblUsers and tblUserInfo
I have a gridview that gives me the list of users and when selected, it gives me the detailsview of the the record. However, When I need to add a new user, I am trying to add that user into tblUsers (which happens with no problems), and then add a record, using the same user number (UserID) has the key or ID for the tblUserInfo.
So basicly, when I create a new user in tblUsers I want to create a record in tblUserInfo with the same userid.
In ASP, this takes me minutes... I have been trying to figure this out for weeks.
vs2005, sql express.
Thanks.
View 1 Replies
View Related
Feb 6, 2007
Hi, I have some problem with inserting data into two separate table which are connected via foreign key like in this example:Students (id_s, name, id_subject)Subjects (id_subject, name)So the point is to add a new student and subject for that chosen student. You have to make two inserts right? but how to pass the primary key to the table with foreign key? The form consists of three texbox: name, name_of_subject. Both id_s and id_subject are increment automatically. Any idea how to do it? I would be very grateful for the working code.Thanks in advance and best regards,N.
View 5 Replies
View Related
Oct 16, 2007
I have 3 tables, Type, Subtype, and Subtype2. All which are related by the ID of preceding table. Using a 3 textboxes and a button I want to insert new Types and subtypes to the tables. How do I go about identifying the new types new ID insert it in to the other tables? I am using ASP.net (vb) with sql server.
View 3 Replies
View Related
Nov 25, 2007
Hi,I need to make an insert into two tables connected to each other via foreign and primary key. The point is - I cannot use stored procedures or triggers. That tables looks like this: Candidate(ID, Name, Surname, Class) and Candidate_Exam(ID, date). ID in Candidate_Exam is a foreign key. When you insert a new record into Candidate, another one need to be inserted into Candidate_Exam with the same ID. Can anyone help? It is supposed to be an easy assigment so maybe I just have a bad approach.Regards,N.
View 3 Replies
View Related
Apr 22, 2008
Hi i want to insert a tables from one database to another based on unique key
View 2 Replies
View Related
May 12, 2008
Hi,
I'm trying to update 2 tables in SQL (say 2 Costumers table). 1 Lists all costumers per location( so 1 costumer can be placed in multiple locations), while the other is by location with contact details.
Is it possible for me to update both pages in 1 web update page?????
Thanks in advance.
View 3 Replies
View Related
May 12, 2005
hi,
i have got a problem, iam creating an asp.net application, but i pretend to insert data in two different tables, but i have no ideia how to do this.Can you help to solve this problem? please..........
View 3 Replies
View Related
May 12, 2005
hi,
iam creating an asp.net application the database is created on the SQLSERVER, but i have a problem, i pretend to insert data in to 2 tables and i have no ideia how to do this. Can you help me to solve this problem? Please.....
View 1 Replies
View Related
Jun 6, 2006
Hi I was watching this beginner webcast
Lesson 8: Obtaining Data from a SQL Server 2005 Express Edition Database
http://msdn.microsoft.com/vstudio/express/visualcsharp/learning/#beginners
I tried to do the same exact thing.
But when I tried to insert data in the newly created table I got the follwoing error window:
Invalid value for cell (row1, column 2)Then changed value in this cell was not recognized as valid..Net Framework Data Type: Byte[]Error Message; You cannot use the Result pane to set this Field data to values other than NULL.
Type a value appropriate for the data type or press ESC to cancel change.
Any Idea why thing happing.
View 3 Replies
View Related
