Faced an interesting situation. It is necessary to unload the identical in structure tables from different databases into their local database, and for each table being unloaded, assign 2 fields ID and DATE:

CREATE TABLE dbo.T1 ( ID_TBL AS '1 ', UNLOAD_DATE DATE CONSTRAINT default_name DEFAULT getdate() );

Can this be used in the design ?:

 SELECT * INTO local_table FROM Remote_table;

Please tell me how else can you realize the idea?

  • one
    Select into creates a new table. If the table already exists with that name, there will be an error. Use insert into in this case. Well, the number of columns should be the same. For example, insert into local_table select '1', getdate (), * from remote_table; - Sergey Moiseenko
  • And how to add additional columns containing - '1', getdate (), to the existing data points? - Andrey Ivanov
  • If initially there is no local table, use select into the first remote table, creating the necessary columns, and insert into the second one. - Sergey Moiseenko

1 answer 1

Read the INSERT documentation ( in Russian ). Approximate solution for your case:

 -- создаСм Ρ‚Π°Π±Π»ΠΈΡ†Ρƒ для ΠΈΠΌΠΏΠΎΡ€Ρ‚Π° CREATE TABLE dbo.Destination_Table ( Source_Table_ID INT NOT NULL, Unload_Date DATE NOT NULL DEFAULT getdate(), -- пСрСчислСниС столбцов ΠΈΠ· Ρ‚Π°Π±Π»ΠΈΡ† источников Column1 INT NOT NULL, Column2 DATETIME NULL, .... ColumnN INT NOT NULL ); -- ΠΈΠΌΠΏΠΎΡ€Ρ‚ ΠΏΠ΅Ρ€Π²ΠΎΠΉ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ INSERT INTO dbo.Destination_Table ( Source_Table_ID, -- столбСц Unload_Date Π½Π΅ ΡƒΠΏΠΎΠΌΠΈΠ½Π°Π΅ΠΌ, Π΅ΠΌΡƒ присваиваСтся стандартноС Π·Π½Π°Ρ‡Π΅Π½ΠΈΠ΅ Column1, Column2, ... ColumnN) SELECT -- прописываСм ΠΈΠ΄Π΅Π½Ρ‚ΠΈΡ„ΠΈΠΊΠ°Ρ‚ΠΎΡ€ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ источника 1 AS Source_Table_ID Column1, Column2, ... ColumnN FROM Source_Table1; -- ΠΈΠΌΠΏΠΎΡ€Ρ‚ Π²Ρ‚ΠΎΡ€ΠΎΠΉ Ρ‚Π°Π±Π»ΠΈΡ†Ρ‹ INSERT INTO dbo.Destination_Table ( Source_Table_ID, Column1, Column2, ... ColumnN) SELECT 2 AS Source_Table_ID Column1, Column2, ... ColumnN FROM Source_Table2;
  • Thank you very much! That helped me a lot. Read the documentation! - Andrey Ivanov