SQLTeam.com | Weblogs | Forums

Copy a table with all attributes

Copy a table with all attributes using T-SQL.

I just need a confirmation. I used SELECT INTO and it is a 1-1 match on the data for sure. And I think it captures the data type too. I am not sure about the indexes, constraints and others.

So will the SELECT INTO capture a 1-1 on the meta data of the table too?

Just data and schema but verify. What do you mean by metadata

1 Like

...like the indexes but you just confirmed only the data and schema.

Thanks

Actually, it does not pickup the DEFAULT either. Here is a basic example.

CREATE TABLE [dbo].[Table_Source](
	[MyID] [int] IDENTITY(1,1) NOT NULL,
	[Full_Name] [varchar](100) NULL,
	[Extra_Field] [varchar](100) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table_Source] ADD  DEFAULT (format(getdate(),'yyyy_MM_dd')) FOR [Extra_Field]
GO


SELECT * INTO MyTEST FROM [dbo].[Table_Source]

Actually, I found something even better. If I do a "Generate Script" task for the database, I was able to get this:

CREATE TABLE [dbo].[Table_Source](
	[MyID] [int] IDENTITY(1,1) NOT NULL,
	[Full_Name] [varchar](100) NULL,
	[Extra_Field] [varchar](100) NULL
) ON [PRIMARY]
GO
/****** Object:  Index [IX_Table_Source]    Script Date: 2019-08-17 12:26:59 PM ******/
CREATE NONCLUSTERED INDEX [IX_Table_Source] ON [dbo].[Table_Source]
(
	[MyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table_Source] ADD  DEFAULT (format(getdate(),'yyyy_MM_dd')) FOR [Extra_Field]
GO

So what is the real world application of this process? Cloning?

Yes, Cloning. Is there a way to generate the script by SQL? Or do I have to download the script manually?

with what end game, what is the end result, process you are trying to achieve. if you state the problem you are trying to resolve instead of how to do something technically specific, we might be able to provide a more resilient and sustainable solution?