How To Copy Columns From A Table To Another?
Jan 5, 2002
At first I have to apologize that I write here. This is not right place for my problem but I could not find any better place to ask help.
I'm creating a new table and I have to copy some columns from the old one to the new. Desciption of the old table is something like that:
Table1: name char(32) not null primary key, variable1 char(32), variable2 char(32).
Now I would like to copy columns variable1 and variable2 to the new table (called Table2). I need to change the names to the id-numbers. (Don't ask me why, I just have to :) I have created Table2. Table2: id int not null primary key, newvariable1 char(32), newvariable2 char(32).
Now the problem is that how can I copy just columns variable1 and 2 without the name-column (witch is the primary key)? I just an error message:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,
TABLEID=578, COLNO=0" is not allowed. SQLSTATE=23502
It just says that (if I understood the message) I can't leave the id-column blank. How can I generate the keys to Table2? It would be nice to get keys from 1,2,3...
Please help me. :)
View 4 Replies
ADVERTISEMENT
Nov 14, 2000
I would like to dynamically copy a table in Transact SQL, like this:
SELECT * into NEW_TABLE from MY_TABLE where 1 = 2
This creates an empty NEW_TABLE as desired. However, NEW_TABLE retains the column nullability of MY_TABLE; I would like NEW_TABLE to have all its columns nullable.
I tried to write Transact SQL logic to update the isnullable column in syscolumns for NEW_TABLE, but was told that the entire SQL Server would have to be reconfigured to allow this.
Does anyone know of another way to do this?
View 1 Replies
View Related
Jul 22, 2015
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3
**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
View 4 Replies
View Related
Dec 20, 2005
I am using a DTS to copy columns from one database to another.
How do I do the same thing, but first check if the data alreday exists in the destination table and then only copy if it doesn't exist?
thanks in Advance
ActionAnt
View 1 Replies
View Related
Aug 14, 2007
i have data in a table..
i want the values in the rows to place in columns and columns into rows..
for ex:-there is one table
name id dept
a 1 x
b 2 y
c 3 z
i want the resultant table should look like this
a b c
1 2 3
x y z
View 2 Replies
View Related
Apr 10, 2007
Hi all,
I want to copy 2 columns from 1 database to another database.
I managed to do this, using a Ole DB source and a Ole DB destination dataset.
Now I want to merge 2 colums into 1:
Source Database: Column A: first name, Column B: Lastname.
Destination Database: Column 1: First and lastname customer.
Thanks for your help!
Kind regards,
Marcel Hijnen
eXDe Solutions B.V.
View 4 Replies
View Related
Aug 15, 2007
I have 1 table in my db that I have imported from a txt file using DTS.
In my database "DOJ" I have a table named "DOJGRAB" with ALL my data fresh from a import using DTS.
Also in "DOJ" are 6 other tables (NAME, PERSON, etc...)
How can I parse the columns out from my DOJGRAB table to fill existing columns in my other 6 tables? Basically a copy of specific columns from DOJGRAB to their proper places in the other tables.
Can somebody post an example of the syntax I would use to copy a column from one table to another? Thanks!
View 7 Replies
View Related
Jun 15, 2006
Hi,I have a problem, I have a table with a text type column and anvarchar(2000) type column on my MS SQL 2000 Server.I know that the longest text in the text field is 1000 chars. I want tocopy the content the content of the text field into the nvarchar field.I tried convert and cast but after the update there are only 255 charsin the nvarchar field.Best regardsMarc
View 2 Replies
View Related
Mar 16, 2007
Is there a way to copy and paste data into columns when view the table columns?
I would like to be able to copy and paste data from an access database.
TIA
View 3 Replies
View Related
Nov 4, 2015
I am trying to Import data into SQL server 2008 using management studio. The source data is an Access dbase. I am trying to do this with queries as the tables do not match but I do need to copy specific columns for the source to the destination. Any brief example selecting a column from the source table, and just entering a dummy value for other columns(other column of data for destination table does not exist in source table).
For the example
Source access dbase, just two columns but no primary key, T2dbase, Employee
New table.
First Name, Description
Tom , manager
Destination dbase SQLServer (T1dbase,Employee table), note 4 columns
Primary key NameID, FirstName nvarchar (20), LastName nvarchar (20), Description nvarchar(20)
View 5 Replies
View Related
Jan 9, 2008
Hi all,
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing:
dbo.Categories
dbo.CustomerCustomerDemo
dbo.CustomerDemographics
dbo.Customers
dbo.Employees
dbo.EmployeeTerritories
dbo.Order Details
dbo.Orders
dbo.Products
dbo.Regions
dbo.Shippers
dbo.Suppliers
dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing:
dbo.Categories
New Table...
Modify
Open Table
Script Table as |> CREATYE To |>
DROP To |>
SELECT To |>
INSERT To |> New Query Editor Window
File....
Clipboard
UPDATE To |>
DELETE to |>
From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance,
Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
View 3 Replies
View Related
Jan 26, 2006
Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.
View 2 Replies
View Related
Sep 19, 2007
Hi,
I have two tables
Table Source
{
category varchar(20),
LastUpdate datetime
}
Table Destination
{
SubjectId varchar(20),
SubjectDate datetime,
category varchar(20),
LastUpdate datetime
}
Please note that the number columns are different in each table.
I wanted to dump the data of Source table to Destination table. I meant to say that the rows of 2 columns in Source table to last 2 rows of Destination table.
And also my oreder of the columns in Destination table will vary. So i need to a way to dynamically insert the data in bulk. but i will know the column names for sure before inserting.
Is there anyway to bulk insert into these columns.
Your quick response will be appreciated
~Mohan Babu
View 2 Replies
View Related
Apr 29, 2015
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
View 14 Replies
View Related
May 26, 2015
Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily
Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.
View 4 Replies
View Related
Jul 20, 2005
Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name
View 2 Replies
View Related
Oct 1, 2001
How do I copy a column(or colums) from a table in one database to another table in a different database
View 1 Replies
View Related
Nov 23, 2007
Hi All,
I have 2 tables People & PeopleCosts.
PeopleID in People Table is the primarykey and foreign Key in PeopleCosts Table. PeopleID is an autonumber
The major fields in People Table are PeopleID | MajorVersion | SubVersion. I want to create a new copy of data for existing subversion (say from sub version 1 to 2) in the same table. when the new data is copied my PeopleID is getting incremented and how to copy the related data in the other table (PeopleCosts Table) with the new set of PeopleIDs..
Kindly help. thanks in advance.
Myl
View 3 Replies
View Related
Apr 4, 2008
I'm in the process of converting legacy DTS packages to SSIS. I need to populate a table that has more fields than the source file. In DTS I did this with an ActiveX script. How do I go about doing this within SSIS.
In the ActiveX script most of the fields were defaulted with either spaces or zeroes.
One of the Destination fields needs to be incremented by 1 for each new record inserted.
Any help would be appreciated.
Thanks,
Jeff
View 6 Replies
View Related
Jan 20, 2007
Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem
1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right?
2) these two table have One Prinary Key column with IDENTITY feature.
Any Help Greatly appriciated.Regards.
View 6 Replies
View Related
Dec 10, 2007
Hi i have set up two very simple tables, I want a user to be able to create a basic account ( data stored in User_Profile table with Id set as the Primery Key as Identity) I
want the user to be able to be able to return to their account at a later date
and then post multiple reviews of different bands they have seen at a later date.
I kept the tables in my example very simple so I could get my head
around the concept, but generally, I want to connect the Id (PK) value in
User_Profile table to the User_Id filed in the User_Review table,
so every review that user writes, will be connected directly to their Id.
Any help you could give would be fantastic a i have no idea where to start!!!
User_Profile
Id int, ( as primary Identity Key)
Name
City
Country
I have a second table called User Reviews
User_Revews
Revew_Id int , ( as primary Identity Key)
User_Id int, ( I want this to contain the Id value in
the User profile Table)
Review_Details
Thanks
Odxsigma
View 3 Replies
View Related
Nov 30, 2004
Hi, All,
I have agentID in product table.
Now I add agentID column in transaction table. Now I want to copy all agentID from product table to transaction table based on the order_id
in both table. Can you show me an example?
Thanks
Betty
View 3 Replies
View Related
Jun 21, 2007
I need to copy existing row data from one table into a new row in a different table, both in the same database. Can this be done in a stored procedure where the selected row is passed in as parameter value?
Thank you,
View 2 Replies
View Related
Apr 24, 2005
Hello everyone,
I have a local MSSQL server (I guess it's called MSDE), with some tables that I would like to use as a template for a set of new tables. I would simply like a Stored Procedure that takes these 3 tables, makes a copy of their structure (not data, since they will be empty), and name them by using a parameter given to the SP. I have made something that I thought would work, but after testing it a bit more, it seems to forget default values for the fields, which is of course not good enough :). I hope that someone can tell me how to duplicate these 3 tables, including every detail for the fields!
View 1 Replies
View Related
Apr 21, 2008
Hi,
I need to recover a table.
I have restored completed database to a temp DB.
I do know,how to use export and import.
Any one can help me?
Regards,
Vinoth
View 2 Replies
View Related
Jun 4, 2007
Hi.Like the title says - how do i do this?I was given the following example:INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE COL1 = 'A'The above statement threw the following error:An explicit value for the identity column in table 'TABLE2' can onlybe specified when a column list is used and IDENTITY_INSERT is ON.Then, after filling in all the column names in my above selectstatement I kept getting an error to the effect that the number ofsource and destination columns don't match. This is because one column"confirm_hash" does not exist in the destination table, just thesource table.could somebody show me how to get this to work?thanks!PS - MS SQL SERVER EXPRESS 2005
View 10 Replies
View Related
Oct 23, 2007
I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.
Table Fields
Tbl_Date_Dimension [Date_Dimension_Year], [Date_Dimension_Period], [Date_Dimension_Fiscal_Week]
Tbl_Report_Level Report_Level_Id
Tbl_Customer Customer_Code
[Sales Fact] [Gross Turnover] , Quantity, Consolidated_Sales_Tables_Id
The new table is called Tbl_Sales_Growth
Here's the information on the new table:
Code Block
[Date_Dimension_Year] [int] NOT NULL,
[Date_Dimension_Period] [int] NOT NULL,
[Date_Dimension_Fiscal_Week] [int] NULL,
[Report_Level_Id] [int] NOT NULL,
[Customer_code] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Consolidated_Sales_Tables_Id] [tinyint] NOT NULL,
[Quantity] [decimal](18, 0) NOT NULL,
[Gross turnover] [decimal](18, 0) NOT NULL
) ON [PRIMARY]
View 2 Replies
View Related
Jan 8, 2008
Hi,
I have a number of large tables (50,000,000 + rows and 30+ columns) that are refreshed once per month with data from another system. Data is first loaded into a staging table, that has an indentical strcuture to the final table, cleansed and then copy to the final table.
The current copy process is simple but inefficient:
Truncate Table <final-table>
Insert Into <final-table>
Select * from <staging-table>
If possible I don't want to use DTS for this and I'd like to use a batch size of around 10,000 rows to help reduce the size of the log file because I have 3 of these types of Inserts running concurrently.
What is the fatest way to copy data from one table to another, within a stored procedure using SQL 2000 on a large, clustered server that uses logging?
Thanks,
Jason
View 5 Replies
View Related
Mar 1, 2008
hai friends.............. I want to know how to copy table from one database to another in a same sql server 2005? ex : i have one table called sample1 in database db1. we want to copy it and and paste it to database db2.how can i do this explain me elaborately. regards Samuel Chandradoss .J
View 2 Replies
View Related
Apr 3, 2008
How to copy one databse table into another database as a same table with same same tablename?
View 2 Replies
View Related
May 8, 2008
Hi
I have 2 MSSQL databases. I want to copy some tables of DB1 to DB2. how can I do it?
Also, how can I copy some columns of table1 of DB1 to table 2 of DB2?
Thanks for help
View 2 Replies
View Related
Feb 11, 2006
What is the best way to copy a table, with the foriegn keys, primary keys and indexes, from one database to another using SQL Server 2005?
In sql server 2000 I used the DTS, but in SQL server 2005 it does'nt bring over the foriegn keys, primary keys and indexes.Thanks
View 2 Replies
View Related
Feb 16, 2006
Hi everone,I have one table called temp and I am copying its content over to another table called final. The final table has 2 more coloumns than the temp table, 1 of which is a primary key, and the other one is calculated. What is the easiest way I can do this?Thank you for the answer.
View 1 Replies
View Related
