Shulou(Shulou.com)06/01 Report--

How do I copy tables from one database to another in SQL?

1.

SELECT * INTO Table 1 FROM Table 2 --Copy Table 2

If you copy only the structure and not the content or just a column

Just add the WHERE condition.

2.

Suppose you have database A and database B. Copy the data of Table_A in database A to Table_B in database B.

1 If Table_A and Table_B have the same structure:

use B

insert into Table_B select * from A.dbo.Table_A

2 If Table_A and Table_B have the same structure:

insert into Table_B select Field A Field 1A Field 2... from A.dbo.Table_A

The above two examples use select into and insert into select, so what is the difference between the two syntax?

Select into and insert into select are both used to copy tables. The main difference between the two is that select into requires that the target table does not exist because it is automatically created at insert time. insert into select from Request target table exists

Create table emp as select from scott.emp

insert into emp select from scott.emp

Copy the table structure and its data:

create table table_name_new as select * from table_name_old

Copy only table structure:

create table table_name_new as select * from table_name_old where 1=2;

Or:

create table table_name_new like table_name_old

Copy table data only:

If the two tables have the same structure:

insert into table_name_new select * from table_name_old

If the two tables are structurally different:

insert into table_name_new(column1,column2...) select column1,column2... from table_name_old

pasting

Again, explain how to copy statements using SELECT INTO and INSERT INTO SELECT tables

1. INSERT INTO SELECT statement

Insert into Table2(field1, field2,...) select value1,value2,... from Table1 [where column =value]

[] is optional

The target table Table2 must exist. Since the target table Table2 already exists, we can insert constants in addition to the fields of the source table Table1. Examples are as follows:

insert into tjjrmx(yybh,xh,tjxmbh,jg,sfyx,zhxmbh,tjksbh,jcrq,jcys,ts,ckfw,disporder)

select '24',xh,tjxmbh,jg,sfyx,zhxmbh,tjksbh,jcrq,jcys,ts,ckfw,disporder from tjjrmx where yybh = 5

2. SELECT INTO FROM statement

Select vale1, value2 into Table2 from Table1

It is required that the target table Table2 does not exist because insertion automatically creates Table2 and copies the data specified in Table1 to Table2. example is as follows

------------------------------------------------------------------------------------------------------------------- Practical Example 1 How to combine fields from multiple tables in mysql and insert them into a new table (especially when a dataset of multiple tables is combined into a single table) is implemented in an sql statement. The specific situation is: there are three tables a, b and c. Now we need to look up the values of several fields in table b and table c respectively and insert them into the corresponding fields in table a. For this case, we can use the following statement to achieve:

1. INSERT INTO db1_name(field1,field2) SELECT field1,field2 FROM db2_name

Of course, the above statement is more suitable for data interpolation between two tables, if multiple tables are not suitable. For multiple tables, we can first join the fields that need to be queried, and then form a view and then select from:

2.1 INSERT INTO a(field1,field2) SELECT FROM(SELECT f1,f2 FROM b JOIN c) AS tb

Where f1 is the field of table b, f2 is the field of table c, the fields from table b and table c are combined by join query, and then inserted into table a by select nested query, which satisfies our scenario. If more than 2 tables are needed, then fields can be combined in the form of multiple joins. It should be noted that there must be a setting table alias at the end of the nested query section, as follows:

2.2 SELECT FROM(SELECT f1,f2 FROM b JOIN c) AS tb

That is, the last as tb is required (of course, the name tb can be arbitrarily taken), that is, specify an alias, otherwise the following error will be reported in mysql:

2. 3 ERROR 1248 (42000): Every derived TABLE must have its own alias

That is, each derived new table must specify an alias.

*********************************************************************************************************** Practical Example 2 insert trigger insert into select Insert multiple records simultaneously

Requirement: Write a FOR INSERT trigger that inserts data into the associated data table each time data is inserted.

Problem: When testing, insert only one record into the main table at a time, then the trigger has no problem. In actual use, because the insert into select statement is used, more than one record is inserted into the main table at a time, such as 17 records, but only one record is found in other data tables, which obviously shows an error.

Solution:

1. Search the Internet for keywords such as "insert multiple articles at a time"

2. By writing: select count(*) from inserted in the trigger, you can indeed see that multiple items are inserted at once.

3. Online saying uses "cursor" to realize

Analysis:

1, in fact, do not need the right side can also be, if only the inserted data directly inserted into other related data tables, can be completely used insert into (select from inserted) to achieve.

For example:

CREATE TRIGGER TriInsertStoreFile ON tblStore

FOR INSERT

AS

BEGIN

Insert into storeFile(storeID) (SELECT storeID FROM INSERTED)

END

2. In my actual application, there is also a storeFileItem data table associated with storeFile. To insert the primary key FileID automatically generated when inserting storeFile into storeFileItem as a foreign key association, it seems that only "cursor" is used. In fact, it is not difficult.

CREATE TRIGGER TriInsertStoreFile ON tblStore

FOR INSERT

AS

DECLARE @storeID int

DECLARE @FILEID int

SET @storeID=0

SET @FILEID=0

DECLARE stores_cursor CURSOR FOR

SELECT storeID FROM Inserted

OPEN stores_cursor

FETCH NEXT FROM stores_cursor

INTO @storeID

WHILE @@FETCH_STATUS = 0

BEGIN

Insert into storeFile(storeID) (SELECT storeID FROM INSERTED)

SET @FILEID=@@identity

Insert into storeFileItem(fileID) values(@FILEID)

FETCH NEXT FROM stores_cursor INTO @storeID

END

CLOSE stores_cursor

DEALLOCATE stores_cursor

Finally solved the problem.

Finally, it would be desirable to write insertions to other data tables in separate storage procedures

sqlserver executes dynamic sql in triggers using the inserted (deleted) table

These two days, the company's products will add a trigger for data synchronization. There is no problem with the oracle version. It is finished smoothly.

I had trouble writing sqlserver version. Because in the trigger to insert (deleted) table data, to another table update operation, and some of the fields of this table is not fixed, so need to write dynamic sql, using exec to execute. The problem is that exec cannot access the inserted(deleted) table. It's broken. What should I do?

After repeated google+baidu+ experiments, the problem was finally solved. To sum up, there are roughly two ways:

1. Put the data of inserted (deleted) table into temporary table #temp, and then exec; 2. Use cursor to process inserted(deleted) table one by one.

The specific type can be determined according to the characteristics of the business.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.