How Do I Copy Data From Similar Tables Knowing Unique ID Fields
Jul 20, 2005
I have two tables in my database called CartItems and OrderItems. I
store all of a session's shopping cart items in the CartItems table
using the sessionID as the identifier (called cartID in my DB). After
an order is placed and is approved, I would like to copy all of the
items in the CartItems table for that given cartID to the OrderItems
table given a new orderID.
I will know the cartID and orderID ahead of time and would like to
send them both into a stored procedure and have the transfer take
place.
Example:
take this data...
CartItems (table)
--------------------------------------
cartID | itemID | quantity | price
--------------------------------------
12345 2 1 12.95
12345 7 2 17.95
and make it this data...
OrderItems (table)
--------------------------------------
orderID | itemID | quantity | price
--------------------------------------
00001 2 1 12.95
00001 7 2 17.95
via some stored procedure that I send (@cartID,@orderID)
Any help would be greatly appreciated!!
View 5 Replies
ADVERTISEMENT
May 26, 2007
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies
View Related
Dec 13, 2004
Folks, i have to create four fields in every user table within my database:
CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]
There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!
declare @name VARCHAR (50)
declare cur cursor
fast_forward
for select name from sysobjects where type='u' and status not like '-%'
open cur
WHILE (1=1)
BEGIN
FETCH NEXT
FROM cur
INTO @name
IF @@fetch_status = 0
BEGIN
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD created_date [DATETIME]
GO
ALTER TABLE @name
ADD modified_by [VARCHAR] (25)
GO
ALTER TABLE @name
ADD modified_date [DATETIME]
END
ELSE
BREAK
END
DEALLOCATE cur
I also want that if one column for a table exists; the other columns should be created rather than it quits.
Howdy!
View 4 Replies
View Related
Oct 12, 2006
I feel sure that this is a very naive question, but it is freeky me out that I can solve it. With SQL server what is the easiest way to make a field other than the Primary key unique?Would appreciate help with this. ThanksPaul
View 3 Replies
View Related
Mar 31, 2006
I've called a resultset from SQL Server
using an SQL Selection. I need to iterate over that entire result set
(200+ columns/fields) and all I need are the random numbers contained
in any of the rows/columns. I don't want to have to name each
field/column and then use an if > 0 statement.Isn't there
some way to generically loop through the column's by index or something
instead of their field name so I can just use an integer loop to walk
the dataset? I know there is I've done it about 5 years ago. The
question is how do you do it in C#?SqlConnection thisConn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLQuery"].ConnectionString); SqlCommand thisCmd = new SqlCommand("Command String", thisConn); thisCmd.CommandText = "Select * from SelectionsByCountry where [" + DropDownList1.SelectedItem.ToString() + "] > '0'"; thisConn.Open(); SqlDataReader thisReader = null; thisReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection); while (thisReader.Read()) { DropDownList2.Items.Add(thisReader["System"].ToString().Trim());/*** There are 200+ columns left I want to walk over using a loop structure of some sort. How do I do that?*/ }- Rex
View 2 Replies
View Related
Oct 25, 2013
I'm designing an app for stock keeping. In my DB, I have a field called "ItemSerialNo" and I made it unique(but not the table's primary key). On my front end, I have a text box for Item Serial No and a combo box loaded with the item brands and also a save button. I know that if I try to save a serial no already existing in my DB, the app won't allow me because of the unique property of the field named "ItemSerialNo". But I want to be able to save a serial no already existing in my DB but with a different brand name.
For example, I want to be able to save information like:
1. ITEM SERIAL NO = 12345
BRAND NAME = AA
2. ITEM SERIAL NO = 12345
BRAND NAME = BB.
View 2 Replies
View Related
Sep 30, 2007
It seems that there should be a solution for my situation, but for the life of me I can't seem to figure it out.
I need to compare two "like" tables, containing similar data. Tbl 1 is "BOOKED" (which is a snapshot of inventory) and tbl 2 is "CURRENT" (the live - working inventory table). If I write my query as follows the the subsequent result is "duplicate" data.
Code Block
SELECT booked.item, booked.bin, booked.quantity, current.bin, current.quantity
FROM BOOKED
LEFT JOIN
CURRENT
ON booked.item = current.item
No matter what type of join I use, there is duplicate data displayed for each table. For example, if there are more bins in the BOOKED table that contain a certain product then the CURRENT table will repeat data and vica versa.
As follows:
Item
Bin
Quantity
Bin
Quantity
12345
A01
500
A01
7680
12345
B01
6
A01
7680
12345
C01
20
A01
7680
54321
G10
1032
E15
1163
54321
G10
1032
F20
523
54321
G10
1032
H30
750
98765
Z20
7000
Z20
8500
98765
Y15
2500
Y15
3000
98765
X10
1200
Y15
3000
What I would like to do is display Bin and Quantity only once and the repeating values as NULL or [BLANK]. Or, to display all of the bins from both tables and only the quantities from each table in relation to the bin found in that table, returning a "0" if no quantity exists.
This is what I'm after:
Item
Bin
Quantity
Bin
Quantity
12345
A01
500
A01
7680
12345
B01
6
B01
0
12345
C01
20
C01
0
54321
G10
1032
E15
1163
54321
F20
0
F20
523
54321
H30
0
H30
750
98765
Z20
7000
Z20
8500
98765
Y15
2500
Y15
3000
98765
X10
1200
X10
0
Is this possible? If so, how?
I also might add that it is ok for each table to contain multiple entries for any given item. This is basically being requested as an inventory variance report - inventory before physical count and immediatly after physical count - and will only be run once a year.
-----------------------------------------------
Just thinking out loud here:
What if I created three subqueries, the first containing only BOOKED information, the second containing only CURRENT information and the third being a UNION of both tables? Something like this:
Code Block
SELECT q3.bin, q1.item, ISNULL(q1.quantity, 0) as QTY_BEFORE, ISNULL(q2.quantity, 0) as QTY_AFTER
FROM
(select item, bin, quantity
from BOOKED)q1
Left Join
(select item, bin, quantity
from CURRENT)q2
on q1.item = q2.item
Left Join
(select bin, item
from BOOKED
UNION
CURRENT)q3
on q1.item = q3.item
Order By q1.item
I don't know if I wrote the UNION statement correctly, but I will have to try this when I get back to work...
Any suggestions?
View 7 Replies
View Related
Aug 14, 2015
I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?
View 3 Replies
View Related
Aug 24, 2006
I need to copy the data in the last name table to the field2 table do i use the update query in sql 2005?
View 5 Replies
View Related
Mar 1, 2006
how can i copy all table and data to another database
View 3 Replies
View Related
Feb 21, 2007
Hi there;
i've recently designed a new db for my portal website, and wanna move
the old data from former db to the new one. The problem is that
for the 'News' table, for instance i have two different schemas:Old >>News(id,title,summery,text,date, ... , (old columns) , ...)New >> News(id, title , summery , text , date, lastUpdated, ... , (new columns) , ...) i
want to copy the bolded columns, and want to have 'lastUpdated' &
'date' in the new table to be the same as 'date' in the former one.as i googled, i just found materials on copying dbs to production servers, or cross db scenarios. thanks in advance
View 3 Replies
View Related
Jul 4, 2007
Hello all,
I'v got a database full with an administration. This database needs to be protected and can not be accessed from the internet. Now we are making an internetpage for the employees and i want to read from the database who's working here.
For the security i want to copy some database data from one database to another... Is that possible? With a script ore something?
Thanks i advance!
Willio
View 10 Replies
View Related
Jul 28, 2007
How do i make a new database file and then copy all the tables and data out of an old SQL file into it? I just want to see if rebuilding the file helps with a corruption issue i have been having with the log file.
brandon jelinek
www.ccgspokane.com
View 10 Replies
View Related
Nov 21, 2006
Hello.
i have a database with 300 tables. All the data types of the fields of my tables are custom. Ex. IFGint:int and stuff like that.
I want to know how can i through a stored procedure, change in batch mode all the fields of my tables. I don't want to modify by hand everytable. It's a lot of work and i think that maybe there might be a way for this programatically.
thanks in advance
View 2 Replies
View Related
Jan 7, 2005
HI
I need to copy the table structures from my production database to development database but not loose the data in developement. Is there a way to achive this by creating some scripts.
thanks
View 2 Replies
View Related
May 26, 2007
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.
Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.
I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.
I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.
How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?
Thanks in advance.
View 5 Replies
View Related
May 29, 2006
i want to copy some tables from 1 database to another with its data & procedures etc to another database in Microsoft SQL 2005.
can any one help
plz reply
tnx alot
View 5 Replies
View Related
Sep 12, 2001
Help -
I need to import data into an existing table. Most import rows were unique, so I had no problem using DTS and appending. However, some import rows match existing rows except for one column/field that contains updated/new data, and I have to either replace the entire row with the imported row, or replace the individual field with the new data. How do I do that when there are many rows to import? It would take forever typing in all the data using UPDATE. Thanks in advance for your help!
rb
View 2 Replies
View Related
Jan 3, 2014
For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.
View 6 Replies
View Related
Mar 5, 2014
I am trying to write an SQL command for my crystal report. I need to compare the same column in 3different tables & get the data from each table for only the matching data.. I understand I need to create a temporary table, get the data into it & then work around.. I am quite new to SQL.
Eg: Considering one customer account
Table 1
Cust.No Name Amt_Counter AmtPaid
123.456 sam 0 0
123.456 sam 1 50
Table 2
Cust.No Name Freq_Counter Frequency
123.456 sam 1 0
123.456 sam 2 15
[code]....
View 3 Replies
View Related
Dec 10, 2013
I am using this query to search checkbox values.
SqlCommand cmd = new SqlCommand("Select * from Details where Emp_Code in (" + selectedValues + ")", con);
I want to join a table called Materials to this now. Material table also has an Emp_Code column. How can I write a select sql query to fetch Emp_Code from both Details and Materials table.
SqlCommand cmd = new SqlCommand("select D.Emp_Code, M.Emp_Code from Details D, Materials M where D.Emp_Code = M.Emp_Code in (" + selectedValues + ")", con);
View 2 Replies
View Related
Jan 3, 2014
For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.
View 5 Replies
View Related
Jul 23, 2005
I have two tables in the same SQL database. Both have a similar numericfield. One field has the same number as the other field but is prefixedwith various letters. I know how to use LIKE but won't know the partialstring I am looking for. I am trying to use LIKE '%' + Field A orsomething that will do this.Eg.Table 1 Field A is 'A12345"Table 2 Field B is '12345"I want to find every record in Table 1 Field A that contains the exactField B data.*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies
View Related
Sep 28, 2015
What I am working with unfortunately is a very poorly designed and non-normalized database. Please don't criticize the design. I didn't design it, but I have to write queries against it.I have 2 tables. 1 is called EnterVolume. The other is ExitVolume. Similar columns exist in each.
CREATE TABLE [EventPortion].[EventEnterVolume](
[SequenceNumber] [int] NULL,
[TrialID] [nvarchar](255) NULL,
[TimeOfEvent] [int] NULL,
[UniversalTime] [nvarchar](255) NULL,
[SBOINumber] [int] NULL,
[SEntityPosUpdateIndex] [int] NULL,
[VolumeIndex] [int] NULL,
[code]....
The rules of the database state that for every EnterVolume row (for a given TrialID, SBOINumber, and OwnerBOI) there must be a corresponding ExitVolume row in the ExitVolume table.What I need to do is to capture the paired TimeOfEvent entries from each table for each paired row. Nothing says that an SBOI cannot enter and exit a OwnerBOI's volume several times during the same Trial.Every time a SBOI Enters an OwnerBOI's volume during a certain trial, a row is created in the EnterVolume table. And Likewise when Exiting a OwnerBOI's volume during a certain trial, a row is created in the ExitVolume table.
So here is a query that I attempted, but gave undesirable results:
SELECT EV.TimeOfEvent AS [Enter Time], XV.TimeOfEvent AS ExitTime
FROM IntegratedTest1.EventPortion.EventEnterVolume AS EV INNER JOIN
IntegratedTest1.EventPortion.EventExitVolume AS XV ON EV.TrialID = XV.TrialID AND EV.SBOINumber = XV.SBOINumber AND EV.OwnerBOI = XV.OwnerBOI
Here is some sample data:
EnterVolume
TrialID SBOI OwnerBOI TimeOfEvent
1 A D 5
1 A D 2000
[code]....
View 8 Replies
View Related
May 20, 2015
I normalized the below tables but I am finding it difficult to copy data to the new tables. How do I copy data from existing table to the normalized tables? see the table structure below and other supporting information:
SKU_DATA(SKU,SKU_Description,Department,Buyer) Note: this table already has data in it.
CREATE TABLE SKU_DATA (
SKU
Integer NOT
NULL,
[code].....
The table structure above have two three determinants( SKU,SKU_Description and Buyer). SKU and SKU_Description are candidate keys. Primary key is SKU.
Normalization : SKU_DATA(SKU,SKU_Description, Buyer)
BUYER(Buyer,Department)
View 2 Replies
View Related
May 16, 2006
have a table with 2 fields i want them to be unique. idont want duplicate.
how will i do it
View 9 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Jul 21, 2015
I want to add a unique constraint on 3 fields, to only allow the value in the field ONE time. The value will NEVER be the same for anything else. This is the table structure
Code:
Create Table Employees
(
P_Id int NOT NULL,
InstructorName varchar(255) NOT NULL,
CourseName varchar(100) NOT NULL,
DataTableName varchar(100) NOT NULL
)
I want to create a unique constraint across the fields Instructorname, CourseName, DataTableName as their is ONLY 1 instructor per course per table so those 3 fields will ALWAYS hold unique values. I think the constraint syntax would go like so, but want to check before I go butchering some sql syntax.
constraint syntax:
Code:
Alter Table Employees
Contstraint uniqueEmployee Unique (InstructorName ,CourseName ,DataTableName )
View 1 Replies
View Related
Jul 23, 2005
Hi,I m searching for some Script / Function ... to find difference in datab/w 2 similar tables (exactly same fields structure) in sql 2000.plz update me asap !thanks in advance !
View 2 Replies
View Related
Jun 10, 2008
Please see below post
View 2 Replies
View Related
May 19, 2008
I am using SQL 2005 merge replication with a publisher managing about 45 articles(tables) with about 10 subscribers (remote servers). The problem is that we had to re-start replication from scratch and noticed that, although the publisher's tables have the default values, the subscribers did not get the default values with the initial snap shot, schema building..?!?
I now have to go over 450 tables (10 remotes SQl servers at 45 tables each) and 'reset or set' over 1,000 default values. Meanwhile, the system is down...omg...so not good.
Is there a script out there that automatically extracts the default values from a table and set it to another exact table with the same structure? any ideas?
Thanks..CF
btw, i'm no scripting wizard....help!.
View 1 Replies
View Related
Oct 14, 2004
What I need to do is re-populate a unique number into multiple fields,
Let me explain, An employee can appear in the first table only once but can be in the second table multiple times with multiple employee numbers .There is a field called TFN that is unique and we are using it to create a unique id called KRid so what I have done is created 2 tables namely TEST_TBL and TEST2_TBL . In TEST_TBL I am populating a KRid with a unique no being produced by the TFN field only once i.e 12345 being the resulting unique id number. If an employee has 2 employee numbers i.e empno 1 and empno 1000,only employee no 1 will have the unique KRid created but nothing for 1000 because the record already exists , so what has me stumped is that the TFN for employee empno 1 and the TFN for empno 1000 are the same. How do I get the KRid (12345 from empno 1) to populate empno 1000 in TEST2_TBL , The second table has all records in so I can group the second table by TFN id but how do I populate employee 1000 in the second table with the KRid 12345.
Please help!!!!! Below are how the tables are set up and an example of the result.
TABLE 1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST_TBL]
GO
CREATE TABLE [dbo].[TEST_TBL] (
[Empl_Num] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[First_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mid_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hours_Day] [numeric](18, 2) NULL ,
[Hours_Wk] [numeric](18, 2) NULL ,
[KR_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[Date_Term] [datetime] NULL ,
[Empl_Type] [int] NULL ,
[Cost_Centre] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Empl_Status] [int] NULL
) ON [PRIMARY]
GO
TABLE 2
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST2_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST2_TBL]
GO
CREATE TABLE [dbo].[TEST2_TBL] (
[EmpNumber] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[KR_ID] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[EmpStatus] [int] NULL ,
[EmpType] [int] NULL ,
[CommonName] [char] (32) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Query goes as follows for table 1:
SELECT NPE000.EmpNumber, NPET00.RecordStatus, NPE000.KR_ID, NPE000.Surname, NPE000.FirstName, NPE000.SecondName, NPE000.Class, NPE000.DateEmployed, NPE000.DateOfBirth, NPE000.HoursPerDay, NPE000.HoursPerWeek, NPE000.PassportNo, NPE000.AwardCode, NPE000.EmailPayslipTo, NPE000.Location, NPE000.Grade, NPE000.DateTerminated, NPE000.EmploymentType, NPE000.DistCode, NPE000.EmpStatus, NPET00.TaxRefNo FROM NPE000 NPE000, NPET00 NPET00 WHERE NPET00.RecordStatus = 0 and NPET00.TaxRefNo <> ' 111111111' and NPET00.TaxRefNo <> ' 000000000' AND LENGTH(NPET00.TaxRefNo) >= 9 AND LENGTH(NPE000.KR_ID) >= 0 AND NPE000.EmpNumber = NPET00.EmpNumber
Query goes as follows for table 2:
SELECT NPE000.EmpNumber, NPE000.FirstName, NPE000.Surname, NPE000.Class, NPE000.Location, NPE000.EmploymentType, NPE000.EmpStatus, NPET00.TaxRefNo, NPE000.Paypoint, NPE000.KR_ID, FROM NPE000, NPET00 WHERE Recordstatus = 0 and (EmploymentType = 1 AND EmpStatus = 1 AND NPE000.EmpNumber = NPET00.EmpNumber
From this you can see that in table 1 it will only create 1 KR_ID for only one employee number but in table 2 I am bringing through all employee records. In table 2 I can group by NPET00.TaxRefNo which will bring all NPET00.TaxRefNo's togeather. From that I would like to populate the other employee numbers with the unique KR_ID.
Example:Table 1
000001,Jackson,James,Sam,7.6,38,12345,475431212
Example:Table 2
000001,Jackson,James,Sam,7.6,38,12345,475431212
001000,Jackson,James,Sam,7.6,38,,475431212
I hope this helps
Thanks in advance
View 10 Replies
View Related
Jun 13, 2014
I have a simple query which displays items from inventory with their latest annual test date. I want to create another unique reference in my results to use as a certificate number. The number should be a combination of the item+month+year from the test date. What is the easiest way to accomplish this?
My query and my desired results are below:
select item, test_date
from inventory
where cat = 'TELE' and itemised_status > 15
item test_datecert_no
-------------------- ----------------------------------------
05MC0002 2014-06-10 00:00:0005MC0002-06-2014
06MT0001 2014-05-13 09:02:0006MT0001-05-2014
06MT0002 2014-05-13 09:03:0006MT0002-05-2014
06MT0003 2014-05-13 09:03:0006MT0003-05-2014
06MT0004 2014-05-09 14:12:0006MT0004-05-2014
View 2 Replies
View Related
