Results 1 to 6 of 6

Thread: table copy

  1. #1
    Join Date
    May 2004
    Posts
    36

    Unanswered: table copy

    what's the best way to copy columns from table1 to table2. i have more columns in table2 than table1, but i want to insert the data from table1 to table2 and add default values to the extra columns....


    thanks,
    eddie

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The fastest to implement and the slowest to execute is INSERT<field_list> SELECT <field_list> FROM...

    The slowest to implement and the fastest to execute is to BCP...OUT/BULK INSERT. You'd have to create and modify the format file to accomodate difference in fields, or you can use QUERYOUT with BCP, or create a view and use straight BCP...OUT from that view.

    Choice is yours
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    The slowest to implement and...

    Says you....what's so hard?

    bcp table out filename -Sserver -T -n
    bcp table IN filename -Sserver -T -n

    Now that was hard...

    I even think it's less typing...

    No Column list, which we all agree you SHOULD always code...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    May 2004
    Posts
    36
    how do i implement the bcp

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Brett, as the poster said, there are more fields in table2 (target) than there are in table1 (source). Without having a format file you can't use BCP/BULK INSERT in the form you presented, unless you just want to double the number of records in the same table
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    So?

    Use a format file...you can even generate it....

    bcp has got to be the most under utilized feature of sql server....

    When you bcp in just leave the columns you don't need off the cars...

    Here's a sample card of the employees table in Northwind...

    This one shows how to import when you have LESS Columns....see the 0's int the col order column..

    Code:
    7.0 
    18 
    1	SQLCHAR	0	14	""	1	EmployeeID 
    2	SQLCHAR	0	20	""	2	LastName 
    3	SQLCHAR	0	10	""	3	FirstName 
    4	SQLCHAR	0	30	""	4	Title 
    5	SQLCHAR	0	25	""	5	TitleOfCourtesy 
    6	SQLCHAR	0	26	""	6	BirthDate 
    7	SQLCHAR	0	26	""	7	HireDate 
    8	SQLCHAR	0	60	""	8	Address 
    9	SQLCHAR	0	15	""	9	City 
    10	SQLCHAR	0	15	""	10	Region 
    11	SQLCHAR	0	10	""	11	PostalCode 
    12	SQLCHAR	0	15	""	12	Country 
    13	SQLCHAR	0	24	""	13	HomePhone 
    14	SQLCHAR	0	4	""	14	Extension 
    15	SQLCHAR	0	0	""	0	Photo 
    16	SQLCHAR	0	0	""	0	Notes 
    17	SQLCHAR	0	14	""	17	ReportsTo 
    18	SQLCHAR	0	255	"\r\n"	18	PhotoPath
    It's fixed width btw..


    If you post your ddl of the 2 tables we can hook you up...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •