Sqlserver copy the table structure and copy table data

Copy table structure
In Enterprise Manager, select all the tables you want to copy, right-click all tasks, and generate SQL scripts. Save as a *. SQL file.
Open it in the query analyzer. In the middle of the menu bar, select a database, copy the saved SQL file, tick it, and press F5. The table structure is copied successfully.

Copy table data

You can use the "generate SQL script" tool that comes with SQL Server 2000 to generate SQL scripts for creating tables, views, and stored procedures. So can we generate SQL scripts for the data in the table and automatically import the data to SQL Server after executing these scripts in the query analyzer? The answer is yes. The following stored procedure is written by a senior user. the last name of the senior user is unknown, but the SQL Server community occasionally shows the immortal work. Create procedure DBO. outputdata
@ Tablename sysname
As
Declare @ column varchar (1000)
Declare @ columndata varchar (1000)
Declare @ SQL varchar (4000)
Declare @ xtype tinyint
Declare @ name sysname
Declare @ objectid int
Declare @ objectname sysname
Declare @ ident int

Set nocount on
Set @ objectid = object_id (@ tablename)
If @ objectid is null -- determines whether the object exists
Begin
Print @ tablename + 'object does not exist'
Return
End set @ objectname = rtrim (object_name (@ objectid ))
If @ objectname is null or charindex (@ objectname, @ tablename) = 0
Begin
Print @ tablename + 'the object is not in the current database'
Return
End if objectproperty (@ objectid, 'istable') <> 1 -- determines whether the object is a table.
Begin
Print @ tablename + 'object is not table'
Return
End select @ ident = Status & 0x80 from syscolumns where id = @ objectid and status & 0x80 = 0x80 If @ ident is not null
Print 'set identity_insert '+ @ tablename + 'on' -- defines the cursor, cyclically retrieves data, and generates an insert statement
Declare syscolumns_cursor cursor
Select C. Name, C. xtype from syscolumns C
Where C. ID = @ objectid
Order by C. colid -- open the cursor
Open syscolumns_cursor
Set @ column =''
Set @ columndata =''
Fetch next from syscolumns_cursor into @ name, @ xtype
While @ fetch_status <>-1
Begin
If @ fetch_status <>-2
Begin
If @ xtype not in (189,34, 35,99, 98) -- timestamp does not need to be processed. Image, text, ntext, SQL _variant will not be processed for the moment.
Begin
Set @ column = @ column +
Case when Len (@ column) = 0 then''
Else ','
End + @ name
Set @ columndata = @ columndata +
Case when Len (@ columndata) = 0 then''
Else ','','','
End +
Case when @ xtype in (167,175) Then ''' + '+ @ name +' + ''' -- varchar, char
When @ xtype in (231,239) Then ''' + '+ @ name +' + ''' -- nvarchar, nchar
When @ xtype = 61 Then ''' + convert (char (23), '+ @ name +', 121) + ''' -- datetime
When @ xtype = 58 then ''' + convert (char (16), '+ @ name +', 120) + ''' -- smalldatetime
When @ xtype = 36 then ''' + convert (char (36), '+ @ name + ') + ''' -- uniqueidentifier
Else @ name
End
End
End
Fetch next from syscolumns_cursor into @ name, @ xtype
End
Close syscolumns_cursor
Deallocate syscolumns_cursor set @ SQL = 'set nocount on select ''insert' + @ tablename + '(' + @ column + ') values ('as ''--'', '+ @ columndata +', '') 'from' + @ tablename print '--' + @ SQL
Exec (@ SQL) If @ ident is not null
Print 'set identity_insert '+ @ tablename + 'off' when calling exec outputdata 'myuser', replace the myuser table in the current database of myuser